SQL Server Error 1205 Deadlock Victim: Capture, Read and Fix Deadlocks

SQL Server error 1205 ("Transaction (Process ID N) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.") means two or more sessions were blocking each other in a cycle and the Database Engine rolled one of them back to break it. To fix it, pull the deadlock graph from the built-in system_health Extended Events session (or a database-scoped session in Azure SQL Database), read which statements, indexes and lock modes were involved, and remove the cycle with better indexes, consistent object access order, shorter transactions or row versioning. Keep retry logic for error 1205 in the application either way, because deadlocks can be reduced but not completely avoided.

Who this is for and what you will have at the end

This guide is for DBAs and developers working with SQL Server 2017 or later, Azure SQL Managed Instance or Azure SQL Database who see error 1205 in application logs or deadlock alerts in Azure Monitor.

At the end you will have:

• Deadlock graphs collected without adding a new trace on SQL Server, and a lightweight capture session on Azure SQL Database.
• A repeatable way to read the victim list, process list and resource list.
• A short list of fixes ordered from lowest to highest risk.
• Retry logic for the deadlocks you can't eliminate.

How deadlocks happen and how the victim is chosen

A deadlock is a cyclic dependency. Transaction A holds a lock on row 1 and wants row 2; transaction B holds row 2 and wants row 1. Neither can finish, so they would wait forever. That is different from ordinary blocking, where the blocked session waits until the blocker commits.

The lock monitor thread searches for these cycles every 5 seconds by default. When it finds deadlocks, the interval drops to as low as 100 milliseconds, and it returns to 5 seconds when deadlocks stop. Deadlocks can involve locks, worker threads, memory grants, parallel query exchange resources and Multiple Active Result Sets (MARS) mutexes, not only rows and pages.

When a cycle is found, the engine picks a victim, ends its current batch, rolls back its transaction and returns error 1205:

• Sessions can set SET DEADLOCK_PRIORITY to LOW, NORMAL or HIGH, or an integer from -10 to 10. The session with the lower priority becomes the victim.
• With equal priorities, the session that is cheaper to roll back (less transaction log written) becomes the victim.

The message in your application says lock, communication buffer or thread resources depending on what the cycle involved.

Prerequisites

• SQL Server Management Studio (SSMS) to display deadlock graphs visually.
• To read event files with sys.fn_xe_file_target_read_file: VIEW SERVER STATE on SQL Server 2019 and earlier; VIEW SERVER PERFORMANCE STATE on the server or VIEW DATABASE PERFORMANCE STATE on the database from SQL Server 2022.
• For Azure SQL Database event files: an Azure Storage container and a database scoped credential. The ring buffer option needs neither.
• Query Store enabled on the database if you want to look up the execution plans of the deadlocked statements.

Step 1: Capture the deadlock graph

SQL Server and Azure SQL Managed Instance

You don't need to create anything. The system_health session starts with the Database Engine and captures every detected deadlock with its graph. It writes to a ring buffer and an event file. The event file keeps 4 files of 5 MB, or 10 files of 100 MB on Standard and Enterprise editions, so on a busy server older deadlocks roll off. Microsoft recommends not altering or dropping system_health.

Read recent deadlocks from the ring buffer (query from the Microsoft deadlocks guide):

SELECT xdr.value('@timestamp', 'datetime') AS deadlock_time,
       xdr.query('.') AS event_data
FROM (SELECT CAST ([target_data] AS XML) AS target_data
      FROM sys.dm_xe_session_targets AS xt
           INNER JOIN sys.dm_xe_sessions AS xs
               ON xs.address = xt.event_session_address
      WHERE xs.name = N'system_health'
            AND xt.target_name = N'ring_buffer') AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY deadlock_time DESC;

For a longer history, read the event files. From SQL Server 2017 the function returns a timestamp_utc column you can filter on:

SELECT timestamp_utc,
       CAST(event_data AS XML) AS deadlock_event
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE object_name = N'xml_deadlock_report'
  AND timestamp_utc > DATEADD(DAY, -7, GETUTCDATE())
ORDER BY timestamp_utc DESC;

Trace flags 1204 and 1222 write deadlock details to the error log instead. Microsoft advises against them on workload-intensive systems that are already deadlocking, because they can add overhead; use the Extended Events data.

Azure SQL Database

Azure SQL Database has no built-in system_health session. Create a database-scoped session that captures sqlserver.database_xml_deadlock_report. The ring buffer version needs only T-SQL, is limited to 4 MB, starts automatically when the database comes online, and loses its data whenever the session stops, for example on failover:

CREATE EVENT SESSION [deadlocks] ON DATABASE
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.ring_buffer
WITH
(
    STARTUP_STATE = ON,
    MAX_MEMORY = 4 MB
);
GO
 
ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = START;
GO

For long-term collection, use the package0.event_file target with a blob URL such as https://contosostorage.blob.core.windows.net/xevents/deadlocks.xel after you create a database scoped credential for the container. Local file paths aren't allowed; they fail with Msg 40538 ... A valid URL beginning with 'https://' is required as value for any filepath specified.

Read the ring buffer with the database-scoped DMVs (query from the Azure SQL Database deadlock article; it returns one row per process in each deadlock):

DECLARE @tracename AS sysname = N'deadlocks';
 
WITH ring_buffer
AS (SELECT CAST (target_data AS XML) AS rb
    FROM sys.dm_xe_database_sessions AS s
         INNER JOIN sys.dm_xe_database_session_targets AS t
             ON CAST (t.event_session_address AS BINARY (8)) = CAST (s.address AS BINARY (8))
    WHERE s.name = @tracename
          AND t.target_name = N'ring_buffer'),
 dx
AS (SELECT dxdr.evtdata.query('.') AS deadlock_xml_deadlock_report
    FROM ring_buffer
CROSS APPLY rb.nodes('/RingBufferTarget/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata))
SELECT d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
       d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
       d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
       d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
       LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'), CHAR(10), ' '), CHAR(13), ' '))) AS query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;

To be told when deadlocks happen, create an Azure Monitor alert on the database with Deadlocks as the signal. The alert fires after the deadlock has already been resolved, so treat it as a trend signal.

Step 2: Open the graph in SSMS

In the results grid, select the XML cell to open it, save it with an .xdl extension, close it and open the .xdl file again in SSMS. You get the graphical deadlock graph: ovals are processes, rectangles are resources, and the victim has an X across it. Hover over a process to see its input buffer. SSMS also draws the graph directly when you view the event_file target data of the system_health session.

Step 3: Read the graph

Every deadlock graph has three parts. Work through them in order.

Victim list

victim-list contains the id of the process that was rolled back, for example process27b9b0b9848. Use it to find that process in the next two sections.

Process list

Each process element is a session in the cycle. The attributes that matter most:

Two limits to keep in mind. The input buffer holds only the first 4,000 characters of the statement. And only the statement that was waiting when the cycle closed appears; earlier statements in the same transaction that took the locks the other session wants aren't shown. You often need the stored procedure or application code to see the full transaction.

In Azure SQL Database, the database name in the graph appears as a GUID, which is the physical_database_name from sys.databases.

Resource list

Each resource shows the lock owners and waiters. In this example from the Microsoft deadlocks guide, two sessions deadlock on two indexes of the same table:

<keylock hobtid="72057594214350848" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="cidx" mode="X">
  <owner-list><owner id="process27b9ee33c28" mode="X" /></owner-list>
  <waiter-list><waiter id="process27b9b0b9848" mode="S" requestType="wait" /></waiter-list>
</keylock>
<keylock hobtid="72057594214416384" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="idx1" mode="S">
  <owner-list><owner id="process27b9b0b9848" mode="S" /></owner-list>
  <waiter-list><waiter id="process27b9ee33c28" mode="X" requestType="wait" /></waiter-list>
</keylock>

Read it as a sentence: the UPDATE session holds an exclusive lock on the clustered index key and wants the nonclustered index key; the SELECT session holds a shared lock on the nonclustered key and wants the clustered key. That is the classic key lookup deadlock, and a covering nonclustered index usually removes it.

If a resource shows a hobtid or associatedObjectId without a readable name, map it to the table and index in the database named by dbid:

SELECT OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
       OBJECT_NAME(p.object_id) AS table_name,
       i.name AS index_name,
       p.index_id
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i
    ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE p.hobt_id = 72057594214350848;

Find the execution plans

If Query Store is enabled, look up the plans of the deadlocked statements by the query plan hash shown for each process in the graph's XML:

DECLARE @query_plan_hash AS BINARY (8) = 0x02b0f58d7730f798;
 
SELECT qs.query_id,
       qp.plan_id,
       qt.query_sql_text,
       TRY_CAST (qp.query_plan AS XML) AS query_plan
FROM sys.query_store_query AS qs
     INNER JOIN sys.query_store_query_text AS qt
         ON qs.query_text_id = qt.query_text_id
     INNER JOIN sys.query_store_plan AS qp
         ON qs.query_id = qp.query_id
WHERE qp.query_plan_hash = @query_plan_hash;

In the plan, look for table or index scans on the modified table, indexed views that reference several tables, modifications to columns used in foreign keys, and table hints such as HOLDLOCK, SERIALIZABLE, READCOMMITTEDLOCK, REPEATABLEREAD, UPDLOCK, TABLOCK, PAGLOCK or XLOCK. All of them increase the number or duration of locks.

Step 4: Fix the cause

Start with the changes least likely to alter results, and test each one with the workload that deadlocked.

Row versioning is the change with the broadest effect. Check the current settings first:

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();

New databases in Azure SQL Database have read committed snapshot and snapshot isolation enabled by default. Microsoft recommends row versioning-based read committed for all applications unless an application depends on readers being blocked by writers. If RCSI was turned off on an Azure SQL database, find out why before turning it back on. On SQL Server:

ALTER DATABASE [Sales] SET READ_COMMITTED_SNAPSHOT ON;

With RCSI on, deadlocks between two writers remain possible. Snapshot isolation can turn some writer deadlocks into update conflicts, which also require a retry.

Optimized locking reduces deadlocks further by releasing row and page locks as soon as each row is updated, and by not taking update (U) locks when RCSI is enabled. It is always enabled in Azure SQL Database.

SET DEADLOCK_PRIORITY LOW on a non-critical job doesn't prevent the deadlock, but it makes sure the job, not the user transaction, is the one rolled back.

Step 5: Add retry logic for error 1205

Any session can be a deadlock victim, so applications must handle 1205. Microsoft's guidance is to pause briefly before resubmitting and to randomize the pause, for example between one and three seconds, so the retried transaction doesn't collide again. In T-SQL:

DECLARE @retry INT = 3, @delay CHAR(8);
 
WHILE @retry > 0
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
            UPDATE dbo.Inventory SET Quantity = Quantity - 1 WHERE ProductID = 42;
            INSERT dbo.OrderLine (OrderID, ProductID, Quantity) VALUES (1001, 42, 1);
        COMMIT TRANSACTION;
        SET @retry = 0;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        IF ERROR_NUMBER() = 1205 AND @retry > 1
        BEGIN
            SET @retry -= 1;
            SET @delay = '00:00:0' + CAST(1 + ABS(CHECKSUM(NEWID())) % 3 AS CHAR(1));
            WAITFOR DELAY @delay;
        END
        ELSE
            THROW;
    END CATCH
END

In application code, put the same logic in your data access layer: catch SQL error number 1205, retry the whole transaction (not just the failed statement), cap the number of attempts and log each retry.

Verification

1. Count deadlocks per day before and after the change by grouping the system_health event file results by date, or by charting the Deadlocks metric for an Azure SQL database.
2. Re-run the workload that deadlocked and confirm no new xml_deadlock_report or database_xml_deadlock_report events appear for the same objects.
3. Check that the index or code change didn't regress the statement: compare duration and logical reads in Query Store before and after.
4. Confirm retries are logged in the application, so a rising retry rate is visible even when users don't see errors.

Troubleshooting

For long-running transactions that also make the log grow, see fixing SQL Server error 9002 when the transaction log is full.

Checklist

• Read existing deadlocks from system_health before adding any trace.
• On Azure SQL Database, create a database_xml_deadlock_report session and a Deadlocks alert.
• Save each graph as .xdl and identify victim, statements, indexes and lock modes.
• Look up the plans in Query Store by query plan hash.
• Apply the lowest-risk fix first: indexes, then access order and transaction length, then isolation level.
• Keep retry logic with a randomized delay for error 1205.
• Measure the deadlock rate before and after.

References

• Deadlocks guide
• Analyze and prevent deadlocks in Azure SQL Database
• Use the system_health session
• sys.fn_xe_file_target_read_file
• sys.partitions
• Optimized locking
• sp_query_store_force_plan