SQL Server error 1205: Deadlocked and chosen as the victim
Two sessions were each waiting on locks the other held. SQL Server rolled back this transaction so the other could finish. Retry it.
- Error number
1205- Severity
- 13
- Category
- Transactions and locking
What it means
A deadlock is a cycle of lock waits: session A holds a lock B needs, while B holds a lock A needs. SQL Server's deadlock monitor detects the cycle and ends it by choosing a victim, rolling back that session's whole transaction and raising 1205. The victim is the session with the lowest DEADLOCK_PRIORITY, and among equal priorities the one that is cheapest to roll back.
The other session continues as if nothing happened. Deadlocks are a normal outcome under concurrency; the goal is to make them rare and to retry when they happen.
Common causes
- Two code paths update the same tables or rows in opposite order.
- A read under the default read committed isolation level, waiting on a writer that is in turn waiting on the reader's shared lock (common with lookups through nonclustered indexes).
- Missing indexes that make updates and deletes scan and lock far more rows than they change.
- Long transactions, or user interaction in the middle of an open transaction.
- Higher isolation levels such as
SERIALIZABLEholding range locks.
How to fix it
-
Retry the transaction in the application when it fails with 1205. In T-SQL, a retry loop looks like this:
DECLARE @tries int = 0; WHILE @tries < 3 BEGIN BEGIN TRY BEGIN TRANSACTION; UPDATE dbo.Accounts SET Balance -= 100 WHERE AccountId = 1; UPDATE dbo.Accounts SET Balance += 100 WHERE AccountId = 2; COMMIT; BREAK; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; IF ERROR_NUMBER() <> 1205 OR @tries = 2 THROW; SET @tries += 1; WAITFOR DELAY '00:00:00.100'; END CATCH END; -
Read the deadlock graph. The built-in
system_healthExtended Events session records every deadlock.SELECT xed.value('@timestamp', 'datetime2') AS occurred_at, xed.query('.') AS deadlock_graph FROM (SELECT CAST(target_data AS xml) 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 = N'system_health' AND t.target_name = N'ring_buffer') AS data CROSS APPLY target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(xed); -
Access tables and rows in the same order in every transaction, and keep transactions short.
-
Add indexes so writes touch only the rows they change; the deadlock graph shows which index each lock was on.
-
Consider read committed snapshot isolation, so readers use row versions instead of shared locks and no longer deadlock with writers.
ALTER DATABASE SalesDb SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
-
Mark less important work as the preferred victim.
SET DEADLOCK_PRIORITY LOW;
Reproduce it safely
Run the steps in two separate connections to a scratch database, in the order shown, then roll back the surviving session and drop the table.
-- Setup (once): CREATE TABLE dbo.DL (id int PRIMARY KEY, v int); INSERT INTO dbo.DL VALUES (1, 0), (2, 0); -- Session 1: BEGIN TRAN; UPDATE dbo.DL SET v = 1 WHERE id = 1; -- Session 2: BEGIN TRAN; UPDATE dbo.DL SET v = 2 WHERE id = 2; UPDATE dbo.DL SET v = 2 WHERE id = 1; -- waits -- Session 1: UPDATE dbo.DL SET v = 1 WHERE id = 2; -- one session gets Msg 1205
Questions
Is my data lost after a deadlock?
The victim's whole transaction is rolled back, so none of its changes were saved. Nothing else is affected. Run the transaction again.
Does NOLOCK fix deadlocks?
It can stop readers from taking shared locks, but it allows dirty and inconsistent reads (missing or duplicated rows). Read committed snapshot isolation removes reader-writer blocking without those risks.
Related errors
1222Lock request time out period exceeded3960Snapshot isolation update conflict266Transaction count mismatch after EXECUTE40P01PostgreSQL: deadlock detected1213MySQL: Deadlock found when trying to get lockORA-00060Oracle: deadlock detected while waiting for resource
More transactions and locking errors
3930Transaction cannot be committed3902COMMIT has no corresponding BEGIN3903ROLLBACK has no corresponding BEGIN3609Transaction ended in the trigger3616Error raised during trigger execution6401No transaction or savepoint found3998Uncommittable transaction rolled back3621The statement has been terminated
The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.
Work on SQL Server in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.