Transaction (Process ID …) was deadlocked on lock resources with another process and has been chosen as the deadlock victim

What it means

A deadlock is a cycle of waits: session A holds a lock that B needs, and B holds one that A needs. Neither can ever finish, so SQL Server’s lock monitor, which looks for cycles every 5 seconds by default (and more often while it keeps finding them), picks one session as the victim. It ends the victim’s batch, rolls back its transaction, and sends it error 1205. The other session carries on as if nothing happened.

What matters for your code:

• The whole transaction is rolled back, everything since BEGIN TRANSACTION, not only the last statement. The batch stops too, so nothing after the failing statement runs.
• It’s safe to retry, and the message says so. Deadlocks are a normal hazard of concurrent writes; handle them with a retry and make them rare.
• Who loses: the session with the lower DEADLOCK_PRIORITY; if they’re equal, the one cheapest to roll back; if that’s a tie too, either one.

Inside TRY…CATCH, the CATCH block runs with the transaction still open but doomed (XACT_STATE() is -1): you can only roll it back. Writing anything first gets error 3930.

Common causes

1. Rows or tables changed in different orders. One request moves money from account 1 to 2 while another moves from 2 to 1, at the same moment.
2. A reader and a writer on different indexes. A SELECT holds a shared lock on a nonclustered index key and wants the matching row in the clustered index, while an UPDATE holds that row and wants the index key to change it.
3. Missing indexes. An UPDATE or DELETE whose WHERE can’t use an index scans and locks far more rows than it changes, so it collides with everyone.
4. Long transactions: slow work, network calls or user input between BEGIN and COMMIT, all while holding locks.
5. Stricter isolation than you need. REPEATABLE READ and SERIALIZABLE hold read locks to the end of the transaction. .NET’s TransactionScope uses Serializable unless you say otherwise.

How to fix it

Retry the transaction

Catch 1205 and run the whole transaction again from the start, after a short pause, a few times at most. In application code that means checking the error number (SqlException.Number == 1205 in .NET) and re-running your unit of work. In T-SQL:

SET DEADLOCK_PRIORITY LOW;   -- optional: let this job lose rather than user requests
DECLARE @tries int = 0;
WHILE 1 = 1
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 5 WHERE id = 2;
    UPDATE accounts SET balance = balance + 5 WHERE id = 1;
    COMMIT;
    BREAK;
  END TRY
  BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    SET @tries += 1;
    IF ERROR_NUMBER() = 1205 AND @tries < 3
    BEGIN
      WAITFOR DELAY '00:00:00.200';
      CONTINUE;
    END;
    THROW;
  END CATCH;
END;

Read the deadlock graph

The system_health session, on by default in SQL Server and Azure SQL Managed Instance, records every deadlock as an xml_deadlock_report event. Read the most recent from its files:

SELECT TOP (10)
       CAST(event_data AS xml).value('(event/@timestamp)[1]', 'datetime2') AS utc_time,
       CAST(event_data AS xml).query('event/data/value/deadlock') AS deadlock_graph
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report'
ORDER BY utc_time DESC;

This needs VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later (VIEW SERVER STATE before); without it you get Msg 300 … VIEW SERVER PERFORMANCE STATE permission was denied. Microsoft’s guide also queries the session’s ring_buffer target, but on our server its XML was already truncated (8 MB, truncated="1") and no longer held the deadlock; the files did. SQL Server Management Studio draws the graph if you save the XML as an .xdl file and open it.

In the graph:

• victim-list names the process that lost.
• Each process shows its spid, the lock it was waiting for (waitresource, lockMode), how long (waittime, in ms), its isolationlevel, and the batch it was running (inputbuf).
• Each entry in resource-list is a lock in the cycle: the table and index (objectname, indexname), who holds it (owner-list) and who waits (waiter-list).

Follow the owners and waiters around and you have the two statements and the order they took locks in.

On Azure SQL Database, Microsoft’s guide has you create a database event session for sqlserver.database_xml_deadlock_report instead, with a ring buffer or file target.

Change rows in a consistent order

If every transaction that touches accounts 1 and 2 locks 1 first, the second one waits for the first instead of deadlocking. Sort the keys in your code, or take the locks up front:

BEGIN TRANSACTION;
SELECT id FROM accounts WITH (UPDLOCK, ROWLOCK) WHERE id IN (1, 2) ORDER BY id;
UPDATE accounts SET balance = balance - 5 WHERE id = 2;
UPDATE accounts SET balance = balance + 5 WHERE id = 1;
COMMIT;

The same goes for tables: always parent before child, say, or orders before order lines.

Keep transactions short, and index the WHERE

Do the slow work before BEGIN TRANSACTION, and commit as soon as the related changes are done. Check that UPDATE and DELETE statements seek on an index (look at the estimated plan) so they lock only the rows they change.

Choose who loses

SET DEADLOCK_PRIORITY LOW (or NORMAL, HIGH, or a number from -10 to 10) for the session makes it the preferred victim. Use it for background jobs that can retry later, so user-facing requests win.

Use row versioning for reads

With the database option READ_COMMITTED_SNAPSHOT on, readers at the default READ COMMITTED level read the last committed version of a row instead of taking shared locks, which removes the reader-against-writer deadlocks (cause 2). It doesn’t stop two writers deadlocking, like the reproduction below. Turning it on needs a moment with no other sessions in the database:

ALTER DATABASE [<database>] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

WITH ROLLBACK IMMEDIATE rolls back everyone else’s open transactions, so plan it, and test the application: code that relied on readers waiting for writers can behave differently. In Azure SQL Database, new databases have it on already, along with Microsoft’s “optimized locking”, which releases row locks as soon as each row is updated and makes deadlocks less likely.

Reproduce it

On SQL Server 2022 (RTM-CU27, 16.0.4295.3), a table seo_sqlerr_conn.accounts with rows 1 and 2, and two sqlcmd sessions started a second apart:

-- Session A (spid 58)
BEGIN TRAN;
UPDATE seo_sqlerr_conn.accounts SET balance = balance - 10 WHERE id = 1;
WAITFOR DELAY '00:00:03';
UPDATE seo_sqlerr_conn.accounts SET balance = balance + 10 WHERE id = 2;   -- waits for B

-- Session B (spid 62)
BEGIN TRAN;
UPDATE seo_sqlerr_conn.accounts SET balance = balance - 5 WHERE id = 2;
WAITFOR DELAY '00:00:03';
UPDATE seo_sqlerr_conn.accounts SET balance = balance + 5 WHERE id = 1;    -- closes the cycle

Session A got, about four seconds after the cycle closed:

Msg 1205, Level 13, State 51, Server 7732422b7f56, Line 6
Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

@@TRANCOUNT in A’s next batch was 0: its first update was gone. Session B finished both updates. Both had the same priority and had written the same amount to the log (logused="292" for each in the graph), so neither was clearly cheaper to roll back. The graph from system_health, trimmed:

<deadlock>
 <victim-list>
  <victimProcess id="processf00689088"/>
 </victim-list>
 <process-list>
  <process id="processf00689088" … waitresource="KEY: 5:72057594049331200 (61a06abd401c)" waittime="3863" … lockMode="X" … spid="58" … isolationlevel="read committed (2)" …>
   <inputbuf>… UPDATE seo_sqlerr_conn.accounts SET balance = balance + 10 WHERE id = 2; …</inputbuf>
  </process>
  <process id="processf132fd468" … waitresource="KEY: 5:72057594049331200 (8194443284a0)" waittime="2922" … spid="62" …>
 </process-list>
 <resource-list>
  <keylock … objectname="inlet.seo_sqlerr_conn.accounts" indexname="PK__accounts__3213E83FB76A9F06" … mode="X">
   <owner-list><owner id="processf132fd468" mode="X"/></owner-list>
   <waiter-list><waiter id="processf00689088" mode="X" requestType="wait"/></waiter-list>
  </keylock>
  <keylock … objectname="inlet.seo_sqlerr_conn.accounts" indexname="PK__accounts__3213E83FB76A9F06" … mode="X">
   <owner-list><owner id="processf00689088" mode="X"/></owner-list>
   <waiter-list><waiter id="processf132fd468" mode="X" requestType="wait"/></waiter-list>
  </keylock>
 </resource-list>
</deadlock>

Run again with session B as the retry loop above (DEADLOCK_PRIORITY LOW, and ROLLBACK in place of COMMIT so the test changed nothing), B became the victim, caught it and succeeded on the second try:

B caught 1205, XACT_STATE() = -1, @@TRANCOUNT = 1
B: done after 1 retries

In the CATCH block the transaction was still open but doomed, as described above. SQL Server 2019 (15.0.4490.9) and 2025 (17.0.5005.3) gave the same error, level and state.

In Inlet

A deadlock is over within seconds, so there’s nothing to watch live. What leads up to one is: in Inlet’s Activity view, Sessions shows who blocks whom and can end a session (KILL), and Locks lists each session’s locks per table and index, waits first, naming the session each one waits for. That needs VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE from SQL Server 2022). Explain (⌘E) draws the estimated plan of an UPDATE as a tree, so you can see whether it scans instead of seeking. When a statement fails with 1205, the error links to this page.