Mssql 데드락 발생시 락발생시킨 쿼리 찾는 방법

MSSQL에서 데드락(deadlock) 이 발생했을 때, 어떤 쿼리나 프로세스가 데드락을 유발했는지 찾는 방법은 다음과 같습니다.

---

🔍 1. SQL Server 데드락 확인 방법 요약

✅ 방법 1: SQL Server 에이전트 로그 또는 시스템 헬스 세션(XE)

SQL Server 2008 이상에서는 시스템 헬스 세션 (system_health extended event) 이 기본적으로 데드락 정보를 기록합니다.

💡 데드락 XML 조회 (가장 흔한 방법)

-- 최근 데드락 이벤트 추출
SELECT 
    XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM 
    (SELECT XEvent.query('.') AS XEvent
     FROM 
        (SELECT CAST(target_data AS XML) AS TargetData
         FROM sys.dm_xe_session_targets st
         JOIN sys.dm_xe_sessions s 
         ON s.address = st.event_session_address
         WHERE s.name = 'system_health'
           AND st.target_name = 'ring_buffer') AS Data
     CROSS APPLY TargetData.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
    ) AS Deadlocks;

이 쿼리 결과로 나오는 DeadlockGraph 는 XML 형식의 데드락 그래프로, SSMS에서 클릭하면 시각화된 형태로 볼 수 있습니다.

✅ 방법 2: 데드락 이벤트에 대해 Extended Events 세션 직접 설정

더 구체적으로 추적하고 싶다면, 사용자 지정 Extended Events 세션을 만들어 모니터링 할 수 있습니다.

---

🔎 2. 데드락 쿼리 XML 분석 방법

DeadlockGraph의 XML을 열면 다음과 같은 정보가 들어 있습니다:

• process 태그: 해당 세션의 실행 쿼리 (inputbuf)
• owner/waiter: 어떤 리소스를 가지고 있고, 어떤 리소스를 기다리는지
• resource: 락이 걸린 테이블/인덱스/키 등
• victim: 데드락 희생자 (자동으로 롤백됨)

예시:

<process id="process123" ...>
    <inputbuf>
        UPDATE Orders SET status = 'Completed' WHERE OrderID = 123
    </inputbuf>
</process>

---

✅ 방법 3: SQL Server Error Log 확인

데드락 발생 시 SQL Server는 오류 로그에 데드락 요약을 남깁니다.

EXEC xp_readerrorlog 0, 1, N'deadlock';

다만 이 로그는 간략하며, 위의 Extended Event에 비해 정보가 부족합니다.

---

✅ 방법 4: Trace Flag 1222 / 1204

SQL Server에 Trace Flag를 켜면 데드락 정보를 로그로 남깁니다.

-- 서버 시작 시 적용되도록 하려면 구성 옵션 사용
DBCC TRACEON (1222, -1);

• 1222: XML 형식 로그
• 1204: 트리형 로그

---

✅ 방법 5: SSMS Activity Monitor 또는 DMVs

sys.dm_tran_locks, sys.dm_exec_requests, sys.dm_os_waiting_tasks 등을 조합하여 현재 락 보유/대기 상태를 추적할 수 있습니다.

SELECT 
    r.session_id,
    r.status,
    r.blocking_session_id,
    r.wait_type,
    t.text AS query_text
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;

---

✅ 정리

방법	설명	권장 여부
system_health XE	기본 설정되어 있음, XML 그래프 확인 가능	⭐ 매우 권장
Extended Event 직접 생성	더 상세 추적 가능	고급
Error Log	간략한 확인용	보조
Trace Flag (1222/1204)	로그에 자세한 내용 기록	보조
DMV 조합	실시간 분석 가능	병행 활용

---