What This Error Means
You see “Transaction (Process ID XX) was deadlocked on lock resources ... choose as the deadlock victim.” That’s SQL Server killing one of two transactions stuck waiting on each other. Happens mostly under heavy concurrency — think batch updates on two tables in opposite order, or a report query blocking an insert.
Quick Fix: Retry the Transaction (30 seconds)
This isn’t a real fix, but it’s the fastest way to get unstuck. Deadlocks are transient — often the next try works. If your app has retry logic, it’s already doing this. If not, just re-run the query or restart the process.
Real-world scenario: You’re running a stored procedure that updates OrderHeader then OrderDetails, and another process does the reverse. First retry usually clears it.
Moderate Fix: Check and Fix Indexes (5 minutes)
Most deadlocks happen because SQL Server needs to scan too many rows. Missing indexes force table scans, which take more locks and increase deadlock chances. Here’s how to find the culprit:
- Run this query to see recent deadlocks (SQL Server 2012+):
SELECT
XEventData.XEvent.value('(event/data/value)[1]', 'varchar(max)') as deadlock_graph
FROM
(SELECT CAST(target_data AS XML) as target_data
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE s.name = 'system_health') AS Data
CROSS APPLY target_data.nodes('//event[@name="xml_deadlock_report"]') AS XEventData(XEvent);The deadlock graph XML shows which queries and indexes are involved. Look for missing indexes in the plan — SQL Server Management Studio will suggest them with green text. Add those indexes.
Common pattern: A SELECT with a WHERE clause on a non-indexed column gets a shared lock, an UPDATE on the same row wants an exclusive lock. Index the WHERE column so the SELECT does a key lookup instead of a full scan. This reduces lock duration.
Advanced Fix: Tune Queries and Change Lock Order (15+ minutes)
If indexes don’t stop deadlocks, the problem is in the transaction design. You need to make all transactions access objects in the same order. Here’s the hard part — finding that order.
- Enable deadlock trace flags to log every deadlock to the SQL Server error log:
DBCC TRACEON (1204, 1222, -1);These write detailed info. Run for an hour during peak load, then review the log.
- Look at the lock modes in the deadlock graph. If you see “KEY” locks and “RID” locks, it’s usually index-related. If you see “PAG” (page) locks, consider enabling row versioning:
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;This makes SELECT statements not block writes. But it increases tempdb usage — test first.
- Rewrite transactions to touch tables in the same order. Example: If two procedures update TableA then TableB, make both do TableA first, then TableB. Use a table-level lock hint as a last resort:
UPDATE YourTable WITH (TABLOCKX) ...That serializes access — kills performance but stops deadlocks. Only do this if you’re desperate.
When to Give Up and Restart
Deadlocks under 5% of transactions are normal. If you’re seeing 10%+ deadlock rate, check your app code. Sometimes the quickest fix is to add a 1-second retry delay in the application layer. SQL Server’s automatic retry (via the deadlock victim) works, but the app must resubmit the transaction.
One last thing: don’t rely on the deadlock priority setting (
SET DEADLOCK_PRIORITY LOW) unless you’re okay with that transaction always getting killed. It just chooses a victim faster — doesn’t fix the problem.
What Works and What Doesn’t
- Indexes: Fix 80% of deadlocks. Worth the time.
- Read Committed Snapshot: Fixes read-write deadlocks, not write-write ones.
- Lock hints (NOLOCK): Avoid. Causes dirty reads and corrupts reports.
- Increasing lock timeout: Masks the issue. Deadlocks still happen, just slower to detect.
Start with the retry, then indexes, then query tuning. That order works for most shops.