SQL Server Deadlocks: Diagnosis, Lock Granularity and Resource Ordering

The Mechanics of a Database Deadlock

A database deadlock occurs when two or more independent sessions hold locks on resources that each other requires, forming a circular wait condition. Because neither transaction can proceed, SQL Server automatically intervenes by selecting one session as the deadlock victim, terminating its statement, rolling back its transaction with Error 1205, and allowing the survivor to complete.

Session A: Holds Shared/Exclusive Lock on Table Customer -> Requests Lock on Table Invoice
Session B: Holds Shared/Exclusive Lock on Table Invoice  -> Requests Lock on Table Customer
Result: Circular Dependency (Neither can proceed -> Deadlock Engine Intervenes)

Extracting Deadlock Graphs via Extended Events

Do not guess what caused a deadlock. SQL Server automatically captures deadlock graphs in the default system_health Extended Events session. The following query extracts the raw XML deadlock graph for forensic analysis:

SELECT 
    XEvent.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS DeadlockGraphXml,
    XEvent.value('(@timestamp)[1]', 'datetime2') AS DeadlockTime
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 EventData
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventNode(XEvent)
ORDER BY DeadlockTime DESC;

Three Golden Rules for Deadlock Elimination

1. Consistent Resource Ordering: If multiple application code paths update both Customer and Invoice, enforce that all paths access and update Customer first, followed by Invoice. This eliminates the possibility of circular waits.
2. Keep Transactions Short: Never perform external REST API calls, email dispatches, or heavy client computation while holding an open database transaction.
3. Use Snapshot Isolation: Enable READ_COMMITTED_SNAPSHOT (RCSI). Under RCSI, readers do not lock out writers, and writers do not lock out readers, eliminating read-write deadlocks entirely.

Primary references

• Microsoft SQL Server Deadlocks Guide
• SQL Server READ_COMMITTED_SNAPSHOT Configuration
• Advanced SQL Window Functions
• Database Optimization Consulting