ERROR

Troubleshooting Guide: SQL Server Deadlock (Msg 1205) in 2026

Quick Fix Summary

TL;DR

Kill the deadlock victim session identified in the system_health extended event or enable trace flag 1222 for detailed logging.

Msg 1205 indicates a deadlock where two or more transactions are permanently blocking each other, each waiting for a resource held by the other. SQL Server automatically chooses a 'victim' to kill to break the cycle, rolling back its transaction.

Diagnosis & Causes

Non-optimized query execution order across transactions.

Missing or non-covering indexes leading to table/page locks.

Long-running transactions holding locks excessively.

Application logic acquiring locks in inconsistent sequences.

High concurrency under default READ COMMITTED isolation.

Recovery Steps

Step 1: Capture the Deadlock Graph

Enable system-level deadlock capture using Extended Events, the primary diagnostic tool. This provides the XML deadlock graph.

sql

-- View recent deadlocks from the system_health session
SELECT XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') AS DeadlockGraph
FROM (SELECT CAST(target_data AS XML) AS TargetData
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 TargetData.nodes('//RingBufferTarget/event') AS XEventData(XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

Step 2: Analyze the Deadlock Graph XML

Interpret the deadlock graph to identify the victim, involved processes, resources (keys, pages, objects), and the wait-for cycle.

sql

-- Extract key elements for analysis (run on the XML output)
SELECT
  deadlock.value('(@id)[1]', 'varchar(50)') AS DeadlockId,
  deadlock.value('(./victim-list/victimProcess/@id)[1]', 'varchar(50)') AS VictimProcessId,
  deadlock.value('(./process-list/process[@id=//victim-list/victimProcess/@id]/@clientapp)[1]', 'varchar(255)') AS VictimApp
FROM (SELECT CAST(@DeadlockGraphXML AS XML) AS graph) AS g
CROSS APPLY graph.nodes('//deadlock') AS t(deadlock)

Step 3: Isolate the Conflicting Queries and Resources

Map the process list and resource list from the graph to find the exact T-SQL statements and database objects (e.g., index key hash) causing the conflict.

sql

-- Query to find the locked object from a resource descriptor (use hash from graph)
SELECT OBJECT_NAME(p.object_id) AS TableName, i.name AS IndexName
FROM sys.partitions p
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE %%lockres%% = '<HashFromDeadlockGraph>'; -- Replace with actual hash

Step 4: Implement Code-Based Resolution

Modify application logic based on the root cause. Common fixes include optimizing query order, adding indexes, or using lock hints.

sql

-- Example 1: Force consistent access order using NOLOCK hint (use cautiously)
SELECT * FROM dbo.Orders WITH (NOLOCK) WHERE CustomerId = @Id;
-- Example 2: Use UPDLOCK to serialize updates in a transaction
BEGIN TRAN;
SELECT * FROM dbo.Inventory WITH (UPDLOCK) WHERE ProductId = @Pid;
UPDATE dbo.Inventory SET Stock = Stock - @Qty WHERE ProductId = @Pid;
COMMIT TRAN;

Step 5: Adjust Transaction Isolation Level

Switch to READ COMMITTED SNAPSHOT ISOLATION (RCSI) to reduce blocking via row versioning, a major architectural fix for read-vs-write deadlocks.

sql

-- Enable RCSI at the database level (requires exclusive database access)
ALTER DATABASE YourDatabaseName SET READ_COMMITTED_SNAPSHOT ON;
-- Ensure connections use the default READ COMMITTED, which will now use row versioning.

Step 6: Proactive Monitoring & Alerting

Set up automated alerts using SQL Agent or monitoring tools to capture deadlocks as they occur for trend analysis.

text

-- Create an Alert on Error 1205 in SQL Server Agent
-- 1. In SSMS, navigate to SQL Server Agent > Alerts.
-- 2. Create a New Alert.
-- 3. Set 'Error number' to 1205.
-- 4. On the 'Response' page, configure email notification or a job to log the event.

Architect's Pro Tip

"Most deadlocks are caused by non-covering indexes on foreign key columns. Adding an index on the FK often resolves them faster than complex code changes."

Frequently Asked Questions

Can I prevent SQL Server from ever choosing my transaction as the deadlock victim?

Yes, you can use the SET DEADLOCK_PRIORITY HIGH; statement within your transaction. However, use this sparingly as it can shift the problem to other critical processes.

Does using WITH (NOLOCK) prevent deadlocks?

No, it can reduce locking contention for reads but does not prevent write-write deadlocks. It also introduces risk by reading uncommitted data (dirty reads).

How is the deadlock victim chosen?

By default, SQL Server chooses the transaction that is least expensive to rollback based on the log size. You can override this with DEADLOCK_PRIORITY.

Related SQL Server Guides

Msg 1105