Skip to content

Est. 2011ยทMicrosoft Partner ID 7033487ยทDelivery under 3 minยทSupport 7 days a week

Your vault is empty.

Free Fix 1205

Error 1205: Transaction Deadlocked on Lock Resources – Capture Then Cure

10 min read Updated October 5, 2026 SQL Server

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.

Run this on the instance, then open the XML and search for xml_deadlock_report

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';
  1. 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.
  2. Read the inputbuf for both processes. That gives you the actual statements, which is usually enough to see the cycle.
  3. Check whether the two statements touch the same objects in opposite order. If they do, aligning the order removes the deadlock outright.
  4. 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.
  5. 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.

  1. 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.
  2. Keep transactions short, and never hold one open across an application round trip or any user interaction.
  3. 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.

  1. 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.
  2. Alternatively narrow the update so it touches fewer rows and holds its locks for less time.
  3. 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.

  1. Batch the modification so no single statement passes the escalation threshold.
  2. Read the table’s lock_escalation_desc in sys.tables and decide whether escalation should be constrained for that table.
  3. 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.

  1. 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.
  2. Size tempdb for the version store before you enable it, because that is where the row versions live.
  3. 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-list names the process identifier that was killed.
  • process-list carries every process involved, with its inputbuf, its isolation level, its login and its transaction count.
  • resource-list carries 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

  1. Confirm tempdb has room and a sensible file layout; the version store lives there and grows with the length of the longest open transaction.
  2. Take the change in a maintenance window. ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON needs to be the only connection in the database, so it waits for everyone else to leave.
  3. 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.
  4. 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

  1. Both workloads run together under the load that used to produce the deadlock without a new 1205.
  2. The system_health session, queried again after a full business day, shows no xml_deadlock_report with the old shape.
  3. For a covering index fix, the reading query’s plan no longer contains a key lookup.
  4. The application’s retry handler is not firing during normal operation.
  5. 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.

Related error codes

Was this article helpful?

Your feedback helps us improve our documentation.

Related articles

License Error Error 3624 Assertion Failures: When a Cumulative Update Is the Only Fix Free Fix Errors 823, 824 and 825: I/O Errors That Signal Real Storage Damage Free Fix Error 20011 and 20084: Replication Agent Failures and Rejected Subscriptions License Error Error 905 and 909: The Database Uses Features Your Edition Does Not Have
โ† Back to Knowledge Base