Mssql deadlock원인파악

1. SQL Server Error Log 또는 Deadlock Graph 확인

SQL Server는 Deadlock 발생 시 해당 정보를 SQL Server Error Log나 Extended Events, Trace, System Health Session등에 기록합니다.

✅ 방법 A: 시스템 헬스 세션(System Health Session)에서 확인

SELECT
    XEvent.value('(event/data[@name="resource"]/value)[1]', 'VARCHAR(MAX)') AS Resource,
    XEvent.value('(event/data[@name="objectname"]/value)[1]', 'VARCHAR(MAX)') AS ObjectName,
    XEvent.value('(event/data[@name="database_name"]/value)[1]', 'VARCHAR(MAX)') AS DatabaseName,
    XEvent.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)') AS SQLText,
    XEvent.value('(event/data[@name="transactionname"]/value)[1]', 'VARCHAR(MAX)') AS TransactionName,
    DeadlockGraph
FROM (
    SELECT
        XEvent.query('.') AS XEvent,
        DeadlockGraph.query('.') AS DeadlockGraph
    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)
    CROSS APPLY XEventData.XEvent.nodes('.') AS Deadlock(DeadlockGraph)
) AS DeadlockEvents;

이 쿼리는 Deadlock이 발생한 객체, SQL 문장, 데이터베이스 등을 보여줍니다.

---

2. SQL Server Profiler 또는 Extended Events로 추적

✅ Extended Events Deadlock 캡처 예시

CREATE EVENT SESSION DeadlockSession ON SERVER
ADD EVENT sqlserver.lock_deadlock(
    ACTION(sqlserver.sql_text, sqlserver.session_id))
ADD TARGET package0.ring_buffer;
GO

ALTER EVENT SESSION DeadlockSession ON SERVER STATE = START;

이 후 sys.dm_xe_session_targets를 조회해 Deadlock 정보를 XML로 분석할 수 있습니다.

---

3. 데드락 그래프(XML) 분석

XML Deadlock Report를 보면 다음을 알 수 있습니다:

• Victim (희생자): deadlock victim으로 선정된 세션
• Owner / Waiter: 어떤 세션이 어떤 리소스를 가지고 있고 기다리는지
• Resource Type: 예: KEY, RID, PAGE, OBJECT 등
• Lock Mode: S, X, U, IX 등

👉 resource-list와 process-list 항목이 중요하며, 두 세션이 어떤 순서로 락을 얻으려고 하다 충돌했는지 추적할 수 있습니다.

---

💡 Deadlock 원인 예시

원인	설명
락 순서 불일치	세션 A는 테이블 A → B 순으로, 세션 B는 테이블 B → A 순으로 접근
과도한 트랜잭션 범위	트랜잭션 범위가 크거나 오래 걸려 락 보유 시간이 길어짐
인덱스 미사용	테이블 스캔으로 인해 더 많은 행에 락이 걸림
잘못된 격리 수준	예: Repeatable Read나 Serializable로 인한 과도한 락

---

🛠 해결 방법

방법	설명
트랜잭션 범위 축소	BEGIN TRAN과 COMMIT 사이의 코드 최소화
락 순서 통일	여러 테이블을 접근할 때는 항상 동일한 순서로 락 요청
적절한 인덱스 사용	필요한 인덱스를 통해 테이블 스캔 최소화
데드락 리트라이 로직	애플리케이션에서 데드락 에러(1205) 발생 시 재시도 로직 구현
READ COMMITTED SNAPSHOT 사용	버전 기반 읽기 적용으로 락 충돌 방지 가능