SQL Server error 1205 (deadlock victim), explained and fixed

Transaction (Process ID …) was deadlocked on … resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (Error 1205) means two or more transactions each locked resources the others needed, in opposite order, so none could proceed. SQL Server detects the cycle, picks the transaction that is cheapest to roll back (by its accumulated cost, adjustable with SET DEADLOCK_PRIORITY), kills it, and lets the others finish. The victim's correct immediate response is built into the message: rerun the transaction. Deadlocks are a normal phenomenon in concurrent systems; the work is making them rare and harmless, not imagining they can be abolished.

First: make the application survive them

  1. Retry the whole transaction on error 1205, with a small delay and a bounded number of attempts. This is the industry-standard handling, because the conflicting transaction has finished by retry time and the rerun usually succeeds at once. The retry must wrap the entire transaction, not just the failing statement, since the transaction was rolled back.

Then: see the actual deadlock

  1. Read the deadlock graph: SQL Server records it in the system_health extended event session by default; the graph (also capturable with a dedicated Extended Events session or trace flag 1222 in older setups) shows the two processes, the objects and indexes involved, and the lock types. It replaces all guessing about which queries contend.

Reducing how often deadlocks form

  1. Access objects in a consistent order everywhere: if every transaction touches tables (and rows, via ordered updates) in the same sequence, cycles cannot form. Two code paths updating the same two tables in opposite orders is the textbook generator.
  2. Keep transactions short and free of user interaction, web calls, and unnecessary work between statements; lock hold time is the exposure window.
  3. Cover the access paths with indexes: a query that scans a table locks far more than one that seeks; missing indexes are the hidden cause behind many “mystery” deadlocks, and the graph shows the scan-shaped lock patterns.
  4. Lower isolation where the business allows: READ COMMITTED SNAPSHOT at the database level lets readers use row versions instead of shared locks and eliminates the large class of reader-versus-writer deadlocks without changing application code semantics for most workloads.

Common questions

Is a deadlock a bug in my code? It is a property of concurrent access patterns, amplified by long transactions and inconsistent ordering. Occasional 1205s under load are expected even in well-built systems, which is why retry logic is standard; a high rate is the signal to do the reduction work.

Why was my transaction chosen as the victim? SQL Server estimates rollback cost and sacrifices the cheapest, influenced by DEADLOCK_PRIORITY if set. The long or update-heavy transaction usually survives; the small one is rerun. Being chosen is not an accusation.

Can NOLOCK make deadlocks go away? Reading with NOLOCK avoids taking shared locks, so it avoids some deadlocks, at the price of possibly reading uncommitted, duplicated, or skipped rows. It is a data-correctness trade, not a free fix, and snapshot isolation usually achieves the relief honestly.

Related errors

MySQL error 1062 (Duplicate entry) · SQLite: database is locked · SQL Server error 18456 (Login failed)