오늘은 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/
'SQL SERVER' 카테고리의 다른 글
SQL SERVER clustered index , nonclustered index (0) | 2022.04.27 |
---|---|
SQL SERVER 비용이 높은 쿼리 보기 (fT. dm_exec_query_stats) (0) | 2022.02.20 |
SQL SERVER 프로파일 쿼리로 실행하기 (1) | 2022.02.16 |
인덱스 페이지 확인하는 방법 (FT. DBCC IND, DBCC PAGE) (0) | 2022.02.13 |
SQL SERVER 인덱스 탐색방법 (INDEX SEEK, INDEX SCAN) (0) | 2022.02.12 |
댓글