// SQL Server error reference

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
Error message
Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

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 SERIALIZABLE holding range locks.

How to fix it

  1. 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;
  2. Read the deadlock graph. The built-in system_health Extended 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);
  3. Access tables and rows in the same order in every transaction, and keep transactions short.

  4. Add indexes so writes touch only the rows they change; the deadlock graph shows which index each lock was on.

  5. 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;
  6. 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

More transactions and locking errors

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.

// fewer errors, faster fixes

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.