[MSSQL] sp_lock

업무를 진행하다 보면 테이블의 특정 칼럼에 대해 LOCK 이 걸려 해당 칼럼에 액세스 하지 못하는 경우가 있다.

 

심한경우 락이 승격되어 테이블 자체에도 액세스 하지 못하는 경우가 생길 수 가 있다.

 

이럴 경우에 어떤 방식으로 락을 발생시킨 트랜잭션을 잡아내어 대응할 수 있는지 확인해보자.

 

 

아래의 데이터를 이용하여 실습을 진행해보자.

SELECT * FROM dbo.TBLINSASHTEST WITH(NOLOCK)

 

위의 테이블에서 트랜잭션 제어어를 사용하여 홍길동의 이름을 변경하는 쿼리를 날려보자.

BEGIN TRAN

UPDATE dbo.TBLINSASHTEST SET name = N'도로시' WHERE num = 1001

 

위와 같이 TRANSACTION 을 열고 UPDATE를 해주게 되면

 

TBLINSATEST 테이블 자체에 X-LOCK 이 걸리게 된다.

 

자연스럽게 TRANSACTION이 닫히는 작업까지 수반되면

 

X-LOCK 이 풀리게 되는데 현재는 열어놓기만 한 상태이므로

 

LOCK 이 유지된다. 이렇게 되면 다른 트랜잭션이

 

해당 테이블에 접근하려고 하면 잠금대기 상태가 발생하게 된다.

 

 

S-LOCK / X-LOCK 이 궁금하다면 - [MSSQL] LOCK

 

 

DBMS 내에서 새로운 페이지를 켜고

 

아래와 같이 WITH(NOLOCK) 옵션 없이 SELECT 를 해주게 되면

 

읽어오지 못하고 대기 상태가 발생한다.

 

SELECT * FROM dbo.TBLINSASHTEST

 

 

 

 

이럴 경우 어떤 트랜잭션에서 락이 걸렸는지 확인이 필요하다.

 

아래와 같이 쿼리를 날려주게 되면 현재 db 내에서 어떤 락이 걸렸는지 총괄적으로 확인이 가능하다.

exec sp_lock

 

우리는 현재 X-LOCK 이 걸린 트랜잭션을 살펴봐야 하므로

 

아래와 같이 쿼리를 수정해준다.(sp_lock  안의 쿼리 내용을 발췌한 것이다.)

select  
	convert (smallint, req_spid) As spid  
,	rsc_dbid As dbid  
,	rsc_objid As ObjId  
,	rsc_indid As IndId  
,	substring (v.name, 1, 4) As Type  
,	substring (rsc_text, 1, 32) as Resource  
,	substring (u.name, 1, 8) As Mode  
,	substring (x.name, 1, 5) As Status  
from  master.dbo.syslockinfo sc
inner join master.dbo.spt_values v on sc.rsc_type = v.number
inner join master.dbo.spt_values x on sc.req_status = x.number
inner join master.dbo.spt_values u on sc.req_mode + 1 = u.number
where v.type = 'LR'  
and x.type = 'LS'  
and u.type = 'L' 
and substring (u.name, 1, 8) = 'X'

 

위의 해당 spid 를 기준으로 어떤 트랜잭션에서 lock이 발생되었는지 조회해보면 아래와 같다.

 

dbcc inputbuffer(108)

 

만약 의미 없이 lock 을 잡고 있는 경우라면 kill 명령어로 해당 트랜잭션을 중단하여

 

LOCK을 풀어줄 수 있다.

KILL 108