SQL Server Lock 조회 방법

SQL Server 에서 Batch Program 작업이나 Bulk 작업등의 프로세스를 실행할 경우

간혹가다 Lock이 걸리게 된다. 오라클이나 Mysql 에서는 ROW LOCK이 자주 발생하게 되는데

이러한 Lock은 Application단의 logic Transaction관리에서 발생하게 된다.

 

SQL Server 에서의 Lock의 종류와 유형에 대해서 알아보고, 어떠한 Statement가 Lock을 발생

시키는지 알아보도록 하겠다.

 

(1) SQL Server Lock의 종류와 설명 

Lock 조회방법 : sp_lock

 

결과 : 아래 그림과 같이 표현된다.

 

위 조회결과에서 주의깊게 봐야할 부분은 Type 부분이다. DB,TAB 과 같이 출력된 부분이 바로 Lock의 종류이다.

Lock Type	Description
RID	테이블의 ROW를 잠근다(흔히들 ROW LOCK이라 불린다.)
KEY	INDEX ROW LOCK임.(IDENTITY 속성이 트랜잭션 범위에 있다면 LOCK이 걸린다.)
PAG	데이터 또는 인덱스 페이지 이다.
TAB	모든 데이터와 인덱스가 포함된 전체 테이블 이다.
DB	데이터 베이스 이다.

 

(2) 어떠한 SQL이 Lock을 발생시키고, 어떠한 프로세스가 대기 상태인지 조회 가능할까?

실제 Lock이 걸렸다면, Lock의 종류만 아는것 보다는, 어떤 프로세스가 Lock이 걸려 있는지 조회 할 수 있으면

더욱 유용하게 사용할 수 있을 것이다. 만약 상사가 Lock이 어디걸렸어? 라고 물어보았을때 Best Answer은

"어떤 영역에 TABLE Lock 이 걸려있어서, 어떤 프로세스가 거기를 select 하려다 보니 안됩니다."

라고 대답하는게 최고의 대답이 아닐까 생각한다.

 

** Lock을 발생시킨 프로세스를 찾아라 !!! **

 

Lock 걸린 프로세스 조회 : sp_who or sp_who2(추천)

 

sp_who2를 실행하였을때 아래와 같은 결과가 나온다.

여기서 주의깊게 봐야할 컬럼은 BlkBy 이다. 30 번 라인을 보면 SUSPENDED 라고 해서 한국어로 하면 지연됨 이란 글자와 함께

BlkBy 컬럼에 59란 숫자가 있다. 이부분이 매우 중요하다. 이 부분을 해석을 하면 이런 의미이다.

해석 : 53번 프로세스ID를 가진 실행중인 프로세스가 SELECT 문을 실행하려고 하는데 59번의 실행중인 SQL 문 때문에 지연

         되고있는 상태이다.

 

해석을 봐서 알겠지만, 53번은 59번 프로세스가 실행이 완료될때까지 WAITING 상태인 것이다. 이걸 전문용어로 BLOCKING 이라고

한다.(뭐 ~ 전문용어도 알아두면 편하긴 하다)

 

** 현재 프로세스가 실행하고 있는 Statemenet는 무엇인가??? **

 

실행하고 있는 프로세스 상세조회 : DBCC INPUTBUFFER( @PID )  EX ) DBCC INPUTBUFFER(56)

 

 

결과 : DBCC의 결과는 아래와 같다.

EventType 쪽을 복사해서 메모장에 붙여넣으면 아래와 같이 출력된다.

(@P0 int,@P1 nvarchar(4000),@P2 nvarchar(4000))EXEC dbo.USP_SelectDuplicationAAA 

 

위 처럼 USP_SelectDuplicationAAA 라는 사용자 정의 프로시져를 호출한 것이 보여질 것이다.

만약 IBATIS와 같은 모듈에서 SQL문을 바로 적었더라면, 실행중인 SQL 명이 출력되게 될 것이다.

 

 

오늘은 프로시져의 Lock의 종류와 Lock걸린놈을 체크하는 방법에 대해 알아보았다.

어렵지 않다!! 어려워 느낄뿐이다.

 

그럼 담에 또 쓰도록 하겠다.

 

-- 2013.10.23 랑이씀 --