How to Fix SQL Server Deadlocks: Causes, Detection and Real Fixes

A deadlock happens when two sessions each hold a lock the other one needs. Neither can continue, so SQL Server picks one as the victim, rolls its transaction back and raises error 1205. The other session carries on. Occasional deadlocks are normal in busy systems; frequent ones point to a fixable pattern in your code or indexes.

What a deadlock looks like

The classic example is two transactions updating the same two rows in opposite order:

-- Session 1
BEGIN TRAN;
UPDATE dbo.Account SET Balance -= 100 WHERE AccountId = 1;
UPDATE dbo.Account SET Balance += 100 WHERE AccountId = 2;
COMMIT;

-- Session 2 (runs at the same time)
BEGIN TRAN;
UPDATE dbo.Account SET Balance -= 50 WHERE AccountId = 2;
UPDATE dbo.Account SET Balance += 50 WHERE AccountId = 1;
COMMIT;

Session 1 locks row 1 and waits for row 2. Session 2 locks row 2 and waits for row 1. That cycle is a deadlock.

Step 1: Capture the deadlock graph

Do not guess. SQL Server records every deadlock in the built-in system_health Extended Events session, including both queries, the locks involved and which session was chosen as the victim.

-- Recent deadlocks from the system_health ring buffer (SQL Server)
SELECT
    x.ev.value('@timestamp', 'datetime2') AS deadlock_time_utc,
    x.ev.query('.')                       AS deadlock_graph
FROM (
    SELECT CAST(st.target_data AS XML) AS target_data
    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'
      AND st.target_name = 'ring_buffer'
) AS t
CROSS APPLY t.target_data.nodes(
    'RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(ev)
ORDER BY deadlock_time_utc DESC;

Save the XML with a .xdl extension and open it in SSMS to see the graph visually. The ring buffer only holds recent events; for older history, read the system_health event files instead. In Azure SQL Database, create a database-scoped Extended Events session that captures the xml_deadlock_report event.

Step 2: Read the graph

In the graph, look for three things:

• The two statements involved (the input buffer of each process).
• The resources: which table, index and key or page each side was waiting for.
• The lock modes, such as S (shared), U (update) and X (exclusive).

Most deadlocks fall into a few patterns: opposite access order, a reader and a writer on the same index, or a read-then-update pattern inside one transaction.

Step 3: Apply the right fix

Access objects in the same order

If every code path updates tables and rows in a consistent order, such as parent before child or lowest ID first, the cycle cannot form. This is the most reliable fix for the classic example above.

Keep transactions short

Locks are held until the transaction ends. Do validation, lookups and calculations before BEGIN TRAN, never wait for user input or call external services inside a transaction, and commit as soon as the writes are done.

Add the right indexes

Without a suitable index, an UPDATE ... WHERE may scan and lock far more rows than it changes, which makes collisions much more likely. An index on the filter columns lets SQL Server lock only the rows it needs. Check the deadlock graph for scans on large tables.

Use read committed snapshot isolation for readers

With READ_COMMITTED_SNAPSHOT on, readers see the last committed version of a row instead of taking shared locks, so readers and writers stop blocking each other. It is enabled by default in Azure SQL Database. On SQL Server, test it first, because it adds version-store load in tempdb and changes read behaviour slightly.

ALTER DATABASE [YourDb]
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;  -- needs a quiet moment; disconnects other sessions

Use UPDLOCK for read-then-update

A common hidden deadlock: two sessions both read a row with shared locks, then both try to update it. Neither can convert its lock. Taking an update lock on the read prevents this:

BEGIN TRAN;
SELECT @Qty = Quantity
FROM dbo.Stock WITH (UPDLOCK, ROWLOCK)
WHERE ProductId = @ProductId;

UPDATE dbo.Stock
SET Quantity = @Qty - @Ordered
WHERE ProductId = @ProductId;
COMMIT;

Step 4: Retry error 1205 in the application

Even a well-designed system can hit an occasional deadlock. Because the victim's transaction is rolled back cleanly, it is safe to retry it. Add a small retry with a short, randomised delay:

// C# example: retry a unit of work on deadlock (SQL error 1205)
for (int attempt = 1; ; attempt++)
{
    try
    {
        await SaveOrderAsync(order);   // runs the whole transaction
        break;
    }
    catch (SqlException ex) when (ex.Number == 1205 && attempt < 3)
    {
        await Task.Delay(Random.Shared.Next(50, 250) * attempt);
    }
}

Retry the whole transaction, not just the failed statement, and cap the number of attempts. Retries hide symptoms, so keep monitoring the deadlock count.

Quick checklist

• Capture the graph from system_health before changing anything.
• Identify the two statements and the resources they fought over.
• Make access order consistent and transactions short.
• Index the columns used to find rows being updated.
• Consider RCSI for read and write conflicts.
• Use UPDLOCK for read-then-update patterns.
• Retry error 1205 in the application as a safety net.

Related reading:

• SQL Server Isolation Levels Explained
• How to Delete or Update Millions of Rows Safely
• How to Find Slow Queries in SQL Server