When This Error Shows Up
You're running a query, and boom — Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. This is SQL Server error 1205. I've seen this pop up during peak hours when two apps update the same table at the same time. For example, an inventory app updates stock while an order app reserves stock—both hit the same row, one waits for the other, and neither backs off. SQL Server picks one to kill, usually yours.
Why It Happens (Plain English)
Think of two people in a hallway holding big boxes. They face each other, and neither can move because each is blocked by the other's box. That's a deadlock. SQL Server has two transactions: Transaction A holds a lock on Row 1 and waits for Row 2. Transaction B holds Row 2 and waits for Row 1. They're stuck. SQL Server's deadlock monitor runs every 5 seconds and picks one transaction to kill (the "victim") so the other can finish. You get error 1205.
The real trigger is almost always two queries accessing the same resources in different orders. Or missing indexes that cause table scans instead of row-level locks. If your app doesn't handle this with retry logic, users see the error.
How to Fix It (Step by Step)
Step 1: Find the Deadlocked Queries
Enable the deadlock graph trace. Run this in SQL Server Management Studio (SQL Server 2012 and up):
-- Enable trace flag 1222 for deadlock info
DBCC TRACEON(1222, -1);
-- Then check the SQL Server error log (Application log in Windows)
-- Look for messages with "deadlock-list"
This writes details to the log. Open the SQL Server error log, find the deadlock event, and copy the XML graph. It shows which queries were involved and what locks they held.
Step 2: Check the Order of Access
The most common fix: make sure all transactions access tables in the same order. For example, if Transaction A updates Table1 then Table2, Transaction B should also update Table1 then Table2 — never Table2 first. If you can't control the order, move to the next step.
Step 3: Add Missing Indexes
Look at the deadlock graph. If one query uses a table scan (no index), it locks many rows — raising the chance of deadlock. Add an index on the columns used in WHERE and JOIN clauses. Run this to find missing indexes:
SELECT * FROM sys.dm_db_missing_index_details;
But be careful — indexes slow down writes. Test during low traffic first. I've seen a single index on an order table cut deadlocks by 90%.
Step 4: Reduce Lock Time with Shorter Transactions
Keep transactions short. Don't do user input or file reads inside a transaction. Aim for less than 1 second. If you have a long-running transaction, split it into smaller ones. For example, update 100 rows at a time instead of 10,000.
Step 5: Use Read Committed Snapshot Isolation (RCSI)
This is my favorite for high-contention databases. RCSI uses row versioning so readers don't block writers, and writers don't block readers. It doesn't stop all deadlocks, but it helps a ton. Enable it on your database:
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;
Test your app after — some features like REPEATABLE READ isolation won't work the same. RCSI is supported in SQL Server 2005 and newer.
Step 6: Set Deadlock Priority
If you can't avoid the deadlock, tell SQL Server to kill the other guy instead of your query. Set the deadlock priority before your transaction:
SET DEADLOCK_PRIORITY HIGH;
BEGIN TRANSACTION;
-- Your query here
COMMIT TRANSACTION;
Values are LOW (killed first), NORMAL (default), or HIGH (killed last). Be careful — if every app sets HIGH, it's pointless.
If It Still Fails
Check these three things:
- Are you using snapshot isolation? RCSI doesn't work if the session uses SERIALIZABLE isolation. Keep it at READ COMMITTED.
- Is the deadlock graph showing the same query? Sometimes the fix hides one deadlock but another appears. Keep the trace flag on for a week.
- Did you restart SQL Server? Trace flag 1222 doesn't persist after a restart. Add it as a startup parameter if you want it always on.
If nothing works, add retry logic in your app — catch error 1205, wait a random 1-5 seconds, and try again. That's the nuclear option, but it's reliable. I've used it in production for years. Good luck!