sys.dm_tran_locks 를 이용한 잠금 정보 확인

sys.dm_tran_locks 를 이용한 잠금 정보 확인

 

• Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

 

Sys.dm_tran_locks는 현재 활성 상태인 잠금 관리자 리소스에 대한 정보를 반환 한다. 각 행은 이미 허용된 잠금 또는 허용 대기 중인 잠금에 대해 현재 활성 상태인 잠금 관리자 요청을 나타낸다.

• Sys.dm_tran_locks : http://msdn.microsoft.com/ko-kr/library/ms190345.aspx

 

select * from sys.dm_tran_locks;

 

 

• Resource_type : 리소스 유형을 나타낸다. 리소스는 DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT 또는 ALLOCATION_UNIT 중 하나이다.
• Request_mode : 요청 모드이다. 허용 된 요청의 경우 허용 모드이고 대기 중인 요청의 경우에는 요청 중인 모드가 된다.
• Request_status : 요청의 현재 상태이다. 가능한 값은 GRANTED, CONVERT, WAIT, LOW_PRIORITY_CONVERT, LOW_PRIORITY_WAIT 또는 ABORT_BLOCKERS이다.
• resource_associated_entity_id : 리소스가 연결된 데이터베이스 내의 엔터티ID이다. 리소스 유형에 따라 개체 ID, Hobt ID 또는 할당 단위 ID가 될 수 있다.

 

 

Sys.dm_tran_locks, sys.sysprocesses, sys.dm_exec_sql_text 정보를 활용하여 잠금 정보를 좀더 상세히 알아본다. 다음 스크립트를 실행하면 잠금에 대한 정보, 차단된SID, 실행중인 쿼리 등을 보여준다.

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];

 

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/1968/understanding-sql-server-locking/

 

 

2014-10-17 / 강성욱 / http://sqlmvp.kr

 

데이터베이스, 잠금, DB 락, DB 블록킹, sys.dm_tran)locks, sys.dm_exec_sql_text, sys.sysprocesses, SQL Server, MSSQL, DBA, DMV, db lock