[스크랩] MSSQL의 잠금(Locking)에 대한 일반적인 확인 절차

http://www.sqler.com/bColumn/364203

SQL Server 의 잠금(Locking) 발생 시 이를 확인하기 위한 일반적인 절차에 대해서 아래와 같이 정리하였습니다. 잠금의 종류와 상황에 따라 달라질 수 있겠지요.  어려운 건 모르는 관계로....;;

 

각 단계별 보충해야 할 내용이 있다면 코멘트 부탁 드립니다~ 정리해 놓고 나중에 활용하면 좋을 것 같아요.

세션1,2 를 통해 문제를 재현하고 새로운 세션으로 문제 발생 원인을 확인하는 방법입니다.

[세션1 - SPID 52] 

문제 상황을 재현하기 위해 UPDATE 트랜잭션을 발생하여 COMMIT 하지 않습니다.

USE AdventureWorks

GO
 

BEGIN TRAN

UPDATE HumanResources.Employee SET Title = 'Design Manager' WHERE Title = 'Design Engineer'

--ROLLBACK TRAN

 

[세션2 - SPID 53] 

HumanResources.Employee 테이블을 쿼리해 보지만 Block 발생으로 쿼리는 기약 없이 실행 중 상태가 됩니다.

SELECT * FROM  HumanResources.Employee WHERE Title = 'Design Manager' 

 

 

[새로운 세션 - 원인 분석]  

1. block 이 발생 하였는지 확인합니다. 

SELECT * FROM SYS.sysprocesses WHERE blocked > 0

spid 52 에 의해 spid 53 프로세스가 wait (LCK_M_S) 상태임을 확인할 수 있습니다.

2. dbcc opentran 명령을 수 차례 반복하여 트랜잭션의 변화 여부를 관찰합니다.

dbcc opentran

Start time, LSN 번호가 변화하지 않는 것을 보니 COMMIT TRAN 이 진행되지 않은 상태로 판단됩니다. 

Transaction information for database 'AdventureWorks'.

Oldest active transaction:

    SPID (server process ID): 52

    UID (user ID) : -1

    Name          : user_transaction

    LSN           : (911:2958:1)

    Start time    : Apr 19 2011 11:27:53:813AM

    SID           : 0x010500000000000515000000375f4581fa66fc544ba3221af4010000

3. LOCK 에 대한 보다 상세 정보를 확인합니다.

sp_lock 52

KEY 에 대해서 Exclusive Lock 이 발생하였음을 확인합니다. 많은 Lock 이 잡혀 있지 않으므로 rollback 해도 시간적 부담은 없을 것 같습니다. 

4. 어떤 쿼리가 수행 중인지 확인합니다. sql_handle 값은 sys.sysprocesses 정보를 통해 확인할 수 있습니다.

select * from sys.dm_exec_sql_text (0x01000500F2AF330770B352840000000000000000)

SELECT * FROM  HumanResources.Employee WHERE Title = 'Design Manager' 

dbcc 명령으로도 확인할 수 있습니다.

dbcc inputbuffer (52)

  BEGIN TRAN   UPDATE HumanResources.Employee SET Title = 'Design Manager' WHERE Title = 'Design Engineer' 

일반적인 확인 절차를 마쳤습니다. 이제 이 단계에서 KILL 을 할지? commit 을 할지 고민해야 할 시점이군요....

[참고자료]

SQL Server 블로킹 발생 시 쿼리 정보 수집 스크립트

http://laigo.kr/639

Lock Modes

http://msdn.microsoft.com/en-us/library/ms175519.aspx

SQL Server, Locks Object

http://msdn.microsoft.com/en-us/library/ms190216.aspx

 

작성자 : Lai Go / 작성일자 : 2011.04.19