A deadlock is SQL Server refusing to let two sessions wait on each other forever. Session A holds a lock Session B needs, Session B holds a lock Session A needs, neither can proceed, so the deadlock monitor picks one as the victim, kills it with error 1205, and lets the other continue. That’s the engine working correctly. The actual problem is almost always in application logic or index design, not in SQL Server itself, and the deadlock graph tells you exactly where.
Get Deadlock Summary captures deadlock events from the system_health Extended Events session, no custom trace required, they’re captured by default on every SQL Server instance since 2008. This post is about reading what it gives you and turning that into an actual fix.
Step 1: Confirm It’s Actually a Deadlock
Applications sometimes misreport a timeout or a long block as a “deadlock.” Confirm from the real error first:
Transaction (Process ID 63) was deadlocked on lock resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.
Error 1205, specifically. A generic timeout (Msg -2, Timeout expired) or a long block that never actually deadlocked is a different problem, see Troubleshoot SQL Server Blocking for that case instead. They look similar to an end user (the query didn’t finish) but need different fixes.
Step 2: Pull the Deadlock Graph
Run Get-DeadlockSummary to get recent deadlock events with their XML graphs. Every deadlock graph has the same two-part shape:
<process-list>, one entry per session involved, showing what each was executing, what lock mode it wanted, and which one was chosen asvictim(marked in the graph)<resource-list>, the actual objects and lock resources each process held and was waiting for, this is where you find the table/index names
The victim process isn’t necessarily the “cause.” SQL Server picks based on transaction cost (cheapest to roll back, by default) or DEADLOCK_PRIORITY if you’ve set it explicitly. Both sessions are equally part of the cycle; reading only the victim’s query and ignoring the survivor’s is the most common way to misdiagnose a deadlock.
Step 3: Identify the Access Pattern
Read both processes’ queries and lock resources together and ask: what order is each one acquiring locks in? The classic deadlock shape is two sessions touching the same two resources in opposite order:
- Session A: updates Table 1, then Table 2
- Session B: updates Table 2, then Table 1
If A gets Table 1’s lock first and B gets Table 2’s lock first, both are now waiting on the other, and neither can win. This is by far the most common real-world cause, and it’s an application logic problem: whatever two operations these are, they need to touch resources in the same order every time.
The second most common shape is a single statement deadlocking against itself under concurrency, usually because of missing indexes forcing a full scan that takes locks across a much wider range than the query actually needs, or a lock escalation from row/page locks to a table lock while another session already holds a conflicting lock elsewhere on the same table.
Common Causes and Fixes
- Inconsistent access order across transactions. The application-logic problem above. The only real fix: enforce a consistent order everywhere the same set of tables are touched together. This is a code review finding, not a database-level fix.
- Missing indexes causing wide scans. A query that should touch 5 rows but scans 50,000 because there’s no supporting index takes far more locks than necessary, increasing the odds of colliding with another session. Check Get Missing Indexes against the tables in the deadlock graph.
- Lock escalation. SQL Server escalates row/page locks to a table lock once a single statement holds roughly 5,000 locks on one object. Two sessions that would have coexisted fine at row-lock granularity can deadlock once one of them escalates.
ALTER TABLE ... SET (LOCK_ESCALATION = AUTO)(partition-aware) or breaking a large batch operation into smaller chunks both reduce this. - Long-running transactions holding locks longer than necessary. A transaction that does an update, then calls out to slow application code, then commits, holds its locks the entire time. Keep transactions as short as possible; do the slow work before or after the transaction, not inside it.
READ COMMITTEDwithoutREAD_COMMITTED_SNAPSHOT. Under the default isolation level, readers take shared locks that can participate in deadlock cycles with writers. EnablingREAD_COMMITTED_SNAPSHOT(row versioning instead of shared locks for reads) eliminates an entire category of reader/writer deadlocks, at the cost of tempdb version-store overhead. Check current usage with Get Temp DB Hotspots before enabling it on a tempdb-constrained instance.
What Doesn’t Actually Fix a Deadlock
- Retry logic alone. Catching error 1205 and retrying the transaction makes the application resilient to the symptom, and it’s a reasonable defensive practice, but it doesn’t reduce how often the deadlock happens. Pair it with an actual root-cause fix, don’t rely on it as the fix.
NOLOCKon the involved queries. This can reduce deadlocks involving that specific reader by skipping shared locks entirely, but it does so by allowing dirty reads, uncommitted data can be returned. That’s a data-correctness trade, not a deadlock fix, and it’s the wrong tool outside a small set of genuinely tolerant reporting queries.- Raising
DEADLOCK_PRIORITYon one session. This changes which session becomes the victim, not whether a deadlock happens. Useful when one transaction is genuinely more important to protect, but it’s a mitigation for the symptom’s impact, not a fix for the cause.
Best Practices
- Capture and review deadlock graphs as they happen rather than only after a user complains;
system_healthretains a rolling window, not forever. - When two tables are updated together anywhere in the application, audit every code path that touches both and confirm the access order matches.
- Treat a recurring deadlock pattern (same tables, same shape, repeatedly) as a design problem worth fixing properly, not a “just add retry logic” situation.
- Re-run Get Deadlock Summary after a fix to confirm the specific pattern actually stopped, not just that deadlocks feel less frequent.
Related Scripts
- Get Deadlock Summary, the script this whole workflow is built around
- Get Blocking Sessions, for blocking that never escalates to an actual deadlock
- Get Missing Indexes, to check whether a wide scan is contributing to lock scope
- Get Lock Escalation, Contention Analysis, and Blocking Chains with Plan, for the lock-escalation angle specifically
- Performance and Troubleshooting (area)
Leave a Reply