SQL Server Deadlock Investigation: From system_health Graph to Root Cause
Analyzes error 1205 using the deadlock graph, distinguishes victim from the lock cycle and prevents recurrence through access-order and index fixes.
Operational Safety & Risk Notice
This runbook contains operational intervention steps; several are write-impacting (KILL, terminate, DROP, RESIZE). Verify in a staging environment first, and apply sequentially in production with verification.
1. Symptom & Signals
- Uygulama `Transaction (Process ID ...) was deadlocked on lock resources ... (Error 1205)` hatası alır; M29 artar.
- **Deadlock, uzun bloklamadan farklıdır:** SQL Server döngüyü tespit edip bir işlemi victim seçer ve geri alır; anlık `sys.dm_exec_requests` sorgusu olayı kaçırabilir.
2. Business & Technical Impact
Victim transaction geri alınır; uygulamada yeniden denenmeyen işlem başarısız olur. Sık tekrarlanırsa sipariş/ödeme gibi akışlarda veri işleme aksar.
3. Diagnosis & Root-Cause Analysis
;WITH targets AS (
SELECT CAST(st.target_data AS xml) AS target_xml
FROM sys.dm_xe_session_targets AS st
JOIN sys.dm_xe_sessions AS ses ON ses.address = st.event_session_address
WHERE ses.name = N'system_health' AND st.target_name = N'ring_buffer'
)
SELECT evt.node.value('(@timestamp)[1]', 'datetime2') AS event_utc,
evt.node.query('(data[@name="xml_report"]/value/deadlock)[1]') AS deadlock_xml
FROM targets
CROSS APPLY target_xml.nodes('/RingBufferTarget/event[@name="xml_deadlock_report"]') AS evt(node)
ORDER BY event_utc DESC;Ring buffer boşsa `system_health` event-file hedefini SSMS ile açın; buffer taşmış olabilir. Gerekli yetki SQL Server 2022'de `VIEW SERVER PERFORMANCE STATE`, eski sürümlerde genellikle `VIEW SERVER STATE`.
4. Remediation & Action Plan
5. **Yapmayın:** Genel `NOLOCK` eklemek kirli/eksik okuma riskini artırır; kalıcı çözüm değildir.
5. Verification & Health Checks
- M29 baseline'a indi; aynı deadlock imzası artık system_health'te tekrarlanmıyor.
- Uygulama tekrar denemelerinden sonra işlem tam ve tekil gerçekleşiyor; testte yarış koşulu tekrar üretilemiyor.
6. Rollback & Contingency Plan
- Uygulama erişim sırası/retry değişikliklerini deployment geri alımıyla döndürün.
- Yeni indeks geri alınabilir ancak kaldırmadan önce eski sorgu planlarını karşılaştırın; RCSI değişikliğinin geri alınması uygulama davranışını tekrar değiştirebilir.