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.
Microsoft’s reference covers MSSQLSERVER_1205 and the Deadlocks guide in full.
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
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.
Common Questions
Is a deadlock the same as blocking?
Can I stop my transaction being chosen as the victim?
SET DEADLOCK_PRIORITY HIGH makes another session more likely to be chosen, but it does not stop deadlocks happening. It moves the pain rather than fixing it, and is worth using only for a genuinely more important process.Where do I find the deadlock graph?
system_health extended events session captures deadlock graphs by default and is running on every modern instance. You do not need to set up a trace to catch the next one, the last several are usually already there.Can I catch 1205 in TRY/CATCH and retry?
CATCH block sees it and can re-run the whole transaction, which the victim has already had rolled back. Bound the retries and add a short delay, and treat the retry as a way of surviving the symptom, not as the fix: the access-order problem above is still there.Related Scripts
- Get Lock Contention and Blocking Plans, what is fighting what, with the plans behind it
- Get Blocking Chains, the live view when blocking has not yet become a deadlock
- Get Index Usage Stats, because a missing index is a common reason two transactions collide at all
- Isolation Levels and RCSI, the setting that removes a whole class of these
- 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
- Performance and Troubleshooting (area)
Leave a Reply