Detection mechanism
SQL Server runs a background lock monitor thread that walks a wait-for graph — a directed graph where nodes are transactions and edges represent “waiting on” relationships. Maintained incrementally in memory, not rebuilt each scan. O(locks) complexity.
| Behaviour | Detail |
|---|---|
| Default poll interval | Every 5 seconds |
| Minimum poll interval | 100ms (dynamically reduces when waits are detected) |
There’s an inherent lag between when a deadlock forms and when it’s detected — minimum 5 seconds.
Victim selection
SQL Server picks the transaction with the least work done (cheapest to roll back). Override with SET DEADLOCK_PRIORITY — lower priority = more likely to be chosen. Victim receives error 1205 and is rolled back; the other transaction proceeds.
Monitoring
Start with the system_health Extended Events session — it captures deadlock XML graphs by default, always running, no setup needed.
| Method | Notes |
|---|---|
XE system_health session | Default, always on — start here |
| Custom deadlock graph XE | Standard approach for detailed capture |
| Trace Flag 1222 | Verbose info → SQL error log |
vs PostgreSQL
PostgreSQL’s wait-for graph detection is triggered more eagerly when a lock wait begins (rather than polled on an interval), so deadlocks surface faster. Relevant context from Beep’s gocpi SQLSTATE 40P01 investigations.