SQL Server Deadlock: 'Transaction was deadlocked on lock resources' — Complete Fix Guide

Understanding the SQL Server Deadlock Error

When SQL Server detects a circular lock dependency, it immediately terminates one transaction and returns:

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

This is distinct from a lock timeout (Msg 1222 — Lock request time out period exceeded) or a simple blocking scenario where one SPID waits indefinitely. A deadlock has a defined resolution: the Lock Monitor background thread (running every 5 seconds by default) detects the cycle, selects a victim based on deadlock priority and transaction cost, and rolls it back.

Deadlocks frequently accompany related SQL Server pain points: slow queries that escalate page locks into table locks, too many connections exhausting the thread pool and extending lock hold time, and out-of-memory conditions that prevent query plan caching and force repeated lock acquisitions.

Step 1: Capture the Deadlock Graph

SQL Server automatically records deadlock events in the system_health Extended Events session (available since SQL Server 2008 R2). No configuration is needed.

Read the deadlock XML from the ring buffer:

SELECT
    xdr.value('@timestamp', 'datetime2') AS deadlock_time,
    xdr.query('.') AS deadlock_graph
FROM (
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets t
    JOIN sys.dm_xe_sessions s ON t.event_session_address = s.address
    WHERE s.name = 'system_health'
      AND t.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS xdr_nodes(xdr)
ORDER BY deadlock_time DESC;

Save the resulting XML, then open it in SQL Server Management Studio (SSMS) by pasting it into a new .xdl file — SSMS renders it as a visual deadlock graph showing the victim (marked with a skull icon), the processes involved, and the locked resources.

Key elements to read from the graph:

• <process> nodes: Each involved SPID with currentdb, isolationlevel, clientapp, and the offending T-SQL in <inputbuf>.
• <resource> nodes: Whether locks are on a KEY (row), PAGE, OBJECT (table), or RID.
• <owner> and <waiter>: Who holds what and who wants what — the cross-dependency is the deadlock itself.

Step 2: Identify the Root Cause Pattern

Pattern A — Inconsistent Access Order (most common)

Transaction 1 locks Orders then tries to lock OrderItems. Transaction 2 locks OrderItems then tries to lock Orders. Resolution: enforce a consistent lock acquisition order in all code paths touching both tables.

Pattern B — Missing Index Causing Lock Escalation

A SELECT or UPDATE without a supporting index triggers an index scan, acquiring S-locks or U-locks on many pages. When enough pages are locked (default threshold: 5,000 locks or 40% of a table), SQL Server escalates to a TABLE lock, blocking all concurrent writers. Check the deadlock graph lockMode attribute — TAB confirms escalation.

Diagnose with:

SELECT
    esc.object_id,
    OBJECT_NAME(esc.object_id) AS table_name,
    esc.escalation_count,
    esc.page_lock_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) esc
WHERE esc.index_level = 0
ORDER BY esc.escalation_count DESC;

Pattern C — Read-Write Contention (fix with RCSI)

A writer (UPDATE/INSERT/DELETE) holds an X-lock and deadlocks with a reader (SELECT) that holds an S-lock. Under default READ COMMITTED isolation, readers block writers and vice versa. Enabling Read Committed Snapshot Isolation (RCSI) makes readers use row versions from tempdb instead of acquiring S-locks:

ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Verify tempdb has sufficient free space first:

SELECT volume_mount_point, available_bytes / 1048576 AS available_mb
FROM sys.dm_os_volume_stats(2, 1); -- filegroup 2 = tempdb data file

Pattern D — Long Transactions Holding Locks

Application code that begins a transaction, calls an external API or performs file I/O, then issues a DML statement holds locks for the entire external call duration. Any lock held beyond ~200 ms significantly raises deadlock probability under concurrency. Look for <process waittime="..." values in the deadlock XML exceeding 1,000 ms.

Step 3: Apply the Fix

Fix 1 — Implement Retry Logic (apply immediately in all cases)

Error 1205 is retryable. The victim transaction is fully rolled back; it is safe to rerun. Implement retry with exponential back-off:

// C# / ADO.NET example
int maxRetries = 3;
int attempt = 0;
while (attempt < maxRetries) {
    try {
        using var tx = conn.BeginTransaction(IsolationLevel.ReadCommitted);
        // ... your DML ...
        tx.Commit();
        break;
    } catch (SqlException ex) when (ex.Number == 1205) {
        attempt++;
        if (attempt >= maxRetries) throw;
        Thread.Sleep(TimeSpan.FromMilliseconds(50 * Math.Pow(2, attempt)));
    }
}

Fix 2 — Add or Tune Indexes

From the deadlock XML <inputbuf>, extract the query and run it through the Query Store or the Missing Index DMVs:

SELECT TOP 10
    mid.statement AS table_name,
    migs.avg_user_impact,
    migs.user_seeks + migs.user_scans AS total_reads,
    'CREATE INDEX IX_' + OBJECT_NAME(mid.object_id) + '_missing ON '
        + mid.statement
        + ' (' + ISNULL(mid.equality_columns, '') 
        + ISNULL(', ' + mid.inequality_columns, '') + ')'
        + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_statement
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
ORDER BY migs.avg_user_impact DESC;

Create the index ONLINE to avoid blocking production:

CREATE INDEX IX_Orders_CustomerId_Status
ON dbo.Orders (CustomerId, Status)
INCLUDE (OrderDate, TotalAmount)
WITH (ONLINE = ON, FILLFACTOR = 85);

Fix 3 — Reorder Table Access

Audit every stored procedure and application query touching the deadlocking tables. Establish a canonical order (e.g., always lock parent before child: Customers → Orders → OrderItems) and refactor all T-SQL and ORM queries to match. For ORMs (Entity Framework, Hibernate), explicit lock hints or raw SQL may be necessary.

Fix 4 — Use UPDLOCK to Prevent Conversion Deadlocks

A common pattern: two sessions SELECT with S-lock, both intend to UPDATE, both try to convert to X-lock — neither can because the other holds an S-lock.

-- Instead of:
SELECT @qty = Quantity FROM dbo.Inventory WHERE ProductId = @id;
UPDATE dbo.Inventory SET Quantity = @qty - 1 WHERE ProductId = @id;

-- Use UPDLOCK to acquire U-lock at read time:
SELECT @qty = Quantity FROM dbo.Inventory WITH (UPDLOCK, ROWLOCK)
WHERE ProductId = @id;
UPDATE dbo.Inventory SET Quantity = @qty - 1 WHERE ProductId = @id;

Step 4: Monitor and Validate

After deploying fixes, set up an alert on deadlock rate using the system_health session or a custom XE session targeting xml_deadlock_report. In Azure SQL Database and SQL Managed Instance, deadlock graphs appear natively in Query Store under Intelligent Query Processing.

For on-premises SQL Server, track deadlock count via:

SELECT cntr_value AS deadlocks_per_sec
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%SQLServer:Locks%'
  AND counter_name = 'Number of Deadlocks/sec'
  AND instance_name = '_Total';

A value that remains non-zero after index and isolation changes indicates additional deadlock sources — repeat the capture-diagnose-fix cycle.