Fix it now
Two sessions each held a lock the other needed, so SQL Server killed the cheaper one and rolled its work back. The graph that says which two is already captured: the system_health session records every deadlock by default.
SELECT CONVERT(xml, target_data) AS target_data
FROM sys.dm_xe_session_targets AS t
JOIN sys.dm_xe_sessions AS s ON s.address = t.event_session_address
WHERE s.name = 'system_health' AND t.target_name = 'ring_buffer';
- In the graph, read three things: which process is in the victim-list, what each process in the process-list held, and what each was waiting for in the resource-list.
- Read the
inputbuffor both processes. That gives you the actual statements, which is usually enough to see the cycle. - Check whether the two statements touch the same objects in opposite order. If they do, aligning the order removes the deadlock outright.
- Look for a key lock on a non-clustered index on one side and the clustered index on the other. That is a lookup deadlock and a covering index usually ends it.
- Add retry handling for 1205 in the application as a safety net, after the access pattern is fixed rather than instead of fixing it.
Do not turn on trace flags 1204 or 1222 to collect this. Microsoft advises against them on busy systems that are already deadlocking, because they can introduce performance problems, and the extended event has the same information.
If the graph names two statements and you can align them, that is the whole job. If not, the next section explains the shapes a deadlock takes and which fix belongs to each.
Why it happens
1205 is a report, not a diagnosis. The published text is Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction. A deadlock needs a cycle: session A holds resource 1 and wants resource 2, session B holds resource 2 and wants resource 1, and neither will release before it acquires. A background monitor finds the cycle, chooses a victim, terminates that transaction and rolls back everything it had done. The other session carries on as though nothing happened.
The victim is chosen by cost. Microsoft documents the order: if the sessions have different DEADLOCK_PRIORITY values the lower one loses; if the priorities match, the transaction that is least expensive to roll back loses; if the cost is equal too, the choice is random. A task that is already rolling back cannot be chosen. That is why the same overnight job is the victim four nights running and the winner on the fifth – nothing changed except how much work each side had done at the moment the cycle closed.
Do not confuse 1205 with the codes that arrive with it. 1222 is Lock request time out period exceeded: one session waited longer than its configured limit for a lock somebody else held, there was no cycle, and the engine rolled nothing back. 3960 is a snapshot write-write conflict. 3961 is a snapshot transaction failing because a concurrent DDL statement changed an object it had already read, since metadata is not versioned. 1206 is the distributed transaction coordinator cancelling a distributed transaction, which is a different subsystem entirely.
Two modules touch the same objects in opposite order
You have this one if The graph shows each process holding what the other wants, on the same two objects, in mirrored order.
- Pick a canonical order for the objects involved and change every module to follow it. Microsoft names this as the primary way to reduce deadlocks.
- Keep transactions short, and never hold one open across an application round trip or any user interaction.
- Where a batch updates many rows, order the updates by the same key on both sides so they queue behind each other rather than cross.
A reader and a writer collide through a key lookup
You have this one if The graph names a non-clustered index on one side and the clustered index of the same table on the other.
- Make the reading query covering: add the columns it needs to the non-clustered index with
INCLUDE, so it never returns to the base table. - Alternatively narrow the update so it touches fewer rows and holds its locks for less time.
- Re-run both workloads together afterwards and confirm the pattern has gone from the graph, not just from today.
This is the commonest shape in ordinary transactional work, and a covering index usually removes it completely rather than making it rarer.
Lock escalation turns a row conflict into a table conflict
You have this one if The graph shows object locks rather than key or page locks, and the statements involved modify large numbers of rows.
- Batch the modification so no single statement passes the escalation threshold.
- Read the table’s
lock_escalation_descinsys.tablesand decide whether escalation should be constrained for that table. - Add indexes so the statement seeks rather than scans, which reduces the number of locks taken in the first place.
Readers and writers block each other unnecessarily
You have this one if Deadlocks between straightforward selects and routine updates on a database using lock-based read committed.
- Enable read committed snapshot isolation. Microsoft recommends the row-versioning read committed level for all applications unless the application relies on the blocking behaviour of the lock-based one.
- Size tempdb for the version store before you enable it, because that is where the row versions live.
- Test first. Row-versioned reads change what concurrent transactions see, and code written around readers blocking can behave differently.
Full reference
Reading the graph without guessing
| What the graph shows | What it means |
|---|---|
| Key locks on the same index, opposite order | Two code paths touch the same rows in different sequence |
| A key lock on a non-clustered index and one on the clustered index | A lookup deadlock between a reader and a writer |
| Object locks rather than key or page locks | Lock escalation turned row locks into a table lock |
| Both sides on the same object with an exchange event | An intra-query parallelism deadlock, not an application one |
| One side holding an update lock, the other a shared lock | A read-then-write pattern without the right locking hint |
| A victim-list entry with an empty process-list entry | The graph rolled out of the ring buffer part-way; capture to a file target instead |
The three nodes of a deadlock graph
victim-listnames the process identifier that was killed.process-listcarries every process involved, with itsinputbuf, its isolation level, its login and its transaction count.resource-listcarries the resources, with the object and index names, the lock modes held and the modes requested.
Almost everything you need is in inputbuf and resource-list together: the statements, and the objects they fought over. If inputbuf is truncated – a long batch, or a parameterised call – match the procedure name and read the module.
Capturing more than the default
The system_health session is enabled by default and captures xml_deadlock_report, so no configuration is required to get a graph. It is a ring buffer, so it rolls. Where deadlocks are intermittent and you need history, create your own event session for xml_deadlock_report with a file target and let it run. Microsoft explicitly steers away from trace flags 1204 and 1222 on workload-intensive systems that are experiencing deadlocks, because using them can introduce performance issues; the extended event is the recommended route on any supported version.
DEADLOCK_PRIORITY, and what it does not do
| Setting | Effect |
|---|---|
SET DEADLOCK_PRIORITY LOW |
Makes this session the preferred victim |
SET DEADLOCK_PRIORITY HIGH |
Makes other sessions the preferred victim |
SET DEADLOCK_PRIORITY -10 to 10 |
The same scale with finer steps; the lower number loses |
Priority decides who dies, not whether anyone does. Setting it HIGH on a critical batch protects that batch and shifts every rollback onto whatever it collides with, which may be your user-facing work. Use it to protect one process you have already decided must not be killed, never as a general fix.
Enabling read committed snapshot safely
- Confirm tempdb has room and a sensible file layout; the version store lives there and grows with the length of the longest open transaction.
- Take the change in a maintenance window.
ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ONneeds to be the only connection in the database, so it waits for everyone else to leave. - Test the application against it first. Readers stop blocking, which is the point, but code that relied on blocking to serialise itself will behave differently.
- Watch the version store and the longest running transaction for the first few days after the change.
When retries are the right answer
You cannot eliminate every deadlock, and an application that touches a busy database should handle 1205 and retry with a short randomised backoff. That is a safety net for the residue. If the same graph shape repeats hourly, the retry is hiding an access pattern that needs fixing, and the cost shows up as latency rather than as errors.
Every code this article covers
| Code | What it points at | Source |
|---|---|---|
1205 |
This transaction was chosen as the victim of a deadlock cycle and rolled back; rerun it | Microsoft Learn |
1222 |
Lock request time out period exceeded. A wait that expired, with no cycle and nothing rolled back by the engine | Microsoft Learn |
3960 |
A snapshot isolation transaction was aborted because another transaction updated or deleted the same row | Microsoft Learn |
3961 |
A snapshot isolation transaction failed because a concurrent DDL statement modified an object it accessed; metadata is not versioned | Microsoft Learn |
1206 |
The Microsoft Distributed Transaction Coordinator cancelled the distributed transaction | Microsoft Learn |
Confirm the fix worked
- Both workloads run together under the load that used to produce the deadlock without a new 1205.
- The system_health session, queried again after a full business day, shows no
xml_deadlock_reportwith the old shape. - For a covering index fix, the reading query’s plan no longer contains a key lookup.
- The application’s retry handler is not firing during normal operation.
- If you enabled read committed snapshot, tempdb free space and the version store size are stable across a peak day.
Questions people ask about this
Should the application just retry?
It should handle 1205 and retry with a short random backoff, because you cannot remove every deadlock. But retry is a safety net. If the same graph repeats hourly the access pattern needs fixing, and the retries are buying that with latency.
Where do I find the graph if system_health has rolled over?
Create your own extended events session for xml_deadlock_report with a file target. Microsoft advises against trace flags 1204 and 1222 on busy systems that are already deadlocking, so do not reach for those first.
Does more memory or a bigger edition help?
No. A deadlock is a logical cycle in lock acquisition. It happens identically on the largest Enterprise instance you can buy, and there is nothing to purchase that removes it.
Why is the same job always the victim?
Victim selection goes by DEADLOCK_PRIORITY first and rollback cost second, so a job doing less work is the cheaper victim. SET DEADLOCK_PRIORITY HIGH shifts the choice to whatever it collides with; it does not stop the deadlock.
Is 1222 a deadlock?
No. It is a lock wait that ran past its timeout. Nothing was rolled back by the engine and there was no cycle – one session simply held something longer than the other was willing to wait.
