SQL SERVER 데드락(lock) 조회하기

오늘은 SQL SERVER의 데드락을 확인하는 방법에 대해 포스팅하겠습니다. 

 

많이 아시는 방법으로는 sp_lock을 사용하여 조회하는 방법도 있지만, 보기 편하게 조작하기 위해 아래쿼리를 활용하면 업무에 많은 도움이 될 것 같습니다. 

 

잠금 상태 만들기 

우선 잠금이 될 테이블의 데이터를 update하는 쿼리를 실행하도록 하겠습니다. 

use Northwind
go 

-- Session 1
BEGIN TRAN  
    UPDATE Orders SET ShipName = 'test'  where OrderID = 10248 
	waitfor delay '01:00'

update 문이 실행중인 가운데, 조회하는 테이블을 동시에 실행하겠습니다. 

SELECT ShipName  
FROM Orders
where OrderID = '10248'

첫번째 쿼리에 waitfor delay 라는 명령어로 강제로 잠금이 발생하도록 만든 상태이므로 이제 데드락상태가 잘 조회되는 지 확인해 보겠습니다. 

 

데드락 쿼리 조회

아래 쿼리를 통해 object 이름, 잠금형태, 잠금쿼리, 잠긴쿼리, 로그인 정보등을 확인할 수 있습니다. 

 

SELECT DTL.resource_type,  
   CASE   
       WHEN DTL.resource_type IN ('DATABASE', 'FILE', 'METADATA') THEN DTL.resource_type  
       WHEN DTL.resource_type = 'OBJECT' THEN OBJECT_NAME(DTL.resource_associated_entity_id, SP.[dbid])  
       WHEN DTL.resource_type IN ('KEY', 'PAGE', 'RID') THEN   
           (  
           SELECT OBJECT_NAME([object_id])  
           FROM sys.partitions  
           WHERE sys.partitions.hobt_id =   
             DTL.resource_associated_entity_id  
           )  
       ELSE 'Unidentified'  
   END AS requested_object_name, DTL.request_mode, DTL.request_status,  
   DEST.TEXT, SP.spid, SP.blocked, SP.status, SP.loginame 
FROM sys.dm_tran_locks DTL  
   INNER JOIN sys.sysprocesses SP  
       ON DTL.request_session_id = SP.spid   
   --INNER JOIN sys.[dm_exec_requests] AS SDER ON SP.[spid] = [SDER].[session_id] 
   CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS DEST  
WHERE SP.dbid = DB_ID()  
   AND DTL.[resource_type] <> 'DATABASE' 
ORDER BY DTL.[request_session_id];

출처 : https://www.mssqltips.com/sqlservertip/1968/understanding-sql-server-locking/