The Beginner's Guide to SQL Server Deadlocks & How to Avoid Them

If you've ever seen the error message "Transaction (Process ID X) was deadlocked on lock resources with another process and has been chosen as the deadlock victim" — congratulations, you've met a SQL Server deadlock. It can feel alarming the first time, but once you understand what's happening under the hood, it's a very solvable problem.

In this guide, you'll learn exactly what SQL Server deadlocks are, why they happen, how to detect them using built-in tools, and — most importantly — practical strategies to prevent them in your own T-SQL code. All examples use the WideWorldImporters sample database so you can follow along hands-on. 

Who is this guide for? Intermediate SQL developers who are comfortable with basic SELECT, INSERT, UPDATE queries and want to level up their understanding of SQL Server concurrency, locking, and transaction management.

What Causes a Deadlock? The Classic Scenario

A deadlock occurs when two or more transactions are each waiting for the other to release a lock — creating a circular dependency that can never resolve on its own. SQL Server detects this cycle and automatically kills one of the transactions (the "deadlock victim") so the other can proceed.

Think of it like two cars at a one-lane bridge from opposite sides. Neither can move forward until the other backs up — and neither is willing to back up. SQL Server is the traffic cop who steps in and forces one car to reverse.

A Classic Deadlock — Step by Step

Imagine two transactions running at the same time against the WideWorldImporters database:

-- SESSION 1: Updates a customer, then tries to update a stock item
BEGIN TRANSACTION;

    UPDATE Sales.Customers
       SET CreditLimit = 5000
     WHERE CustomerID = 1001;   -- acquires lock on CustomerID 1001

    WAITFOR DELAY '00:00:05';   -- simulates slow processing

    UPDATE Warehouse.StockItemHoldings
       SET QuantityOnHand = QuantityOnHand - 10
     WHERE StockItemID = 201;   -- WAITS for Session 2 to release lock

COMMIT;

-- SESSION 2: Updates the stock item first, then tries to update the customer
BEGIN TRANSACTION;

    UPDATE Warehouse.StockItemHoldings
       SET QuantityOnHand = QuantityOnHand - 5
     WHERE StockItemID = 201;   -- acquires lock on StockItemID 201

    WAITFOR DELAY '00:00:05';   -- simulates slow processing

    UPDATE Sales.Customers
       SET CreditLimit = 4500
     WHERE CustomerID = 1001;   -- WAITS for Session 1 to release lock

COMMIT;

Session 1 holds a lock on Sales.Customers and wants Warehouse.StockItemHoldings. Session 2 holds a lock on Warehouse.StockItemHoldings and wants Sales.Customers. Neither can proceed. SQL Server detects the cycle and chooses one session as the deadlock victim, rolling back its transaction and returning error 1205 to the application.

How to Detect Deadlocks in SQL Server

Method 1 — System Health Extended Events (Built-in, Zero Setup)

SQL Server automatically captures deadlock information in the system_health Extended Events session. No setup required — it's always running. Use this query to read the deadlock XML graph:

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

The DeadlockGraph column returns XML that SSMS can render as a visual deadlock diagram — showing exactly which processes, tables, and resources were involved.

Method 2 — Trace Flag 1222 (Detailed Deadlock Info in Error Log)

For persistent deadlock logging, enable Trace Flag 1222:

-- Enable globally (requires sysadmin)
DBCC TRACEON (1222, -1);

-- Verify it is active
DBCC TRACESTATUS (1222);

-- Disable when done diagnosing
DBCC TRACEOFF (1222, -1);

⚠️ Developer Note: Trace Flag 1222 writes detailed deadlock info to the SQL Server Error Log. It adds overhead, so enable it for diagnosing specific issues and turn it off in production when you're done.

How to Prevent Deadlocks — 5 Proven Strategies

Strategy 1 — Access Tables in a Consistent Order

The single most effective prevention technique is ensuring all transactions access tables in the same order. If both sessions always update Sales.Customers before Warehouse.StockItemHoldings, there is no circular dependency and therefore no deadlock.

-- GOOD: Both sessions follow the same order

-- Session 1
BEGIN TRANSACTION;
    UPDATE Sales.Customers
       SET CreditLimit = 5000
     WHERE CustomerID = 1001;

    UPDATE Warehouse.StockItemHoldings
       SET QuantityOnHand = QuantityOnHand - 10
     WHERE StockItemID = 201;
COMMIT;

-- Session 2 (same order)
BEGIN TRANSACTION;
    UPDATE Sales.Customers
       SET CreditLimit = 4500
     WHERE CustomerID = 1001;

    UPDATE Warehouse.StockItemHoldings
       SET QuantityOnHand = QuantityOnHand - 5
     WHERE StockItemID = 201;
COMMIT;

Strategy 2 — Keep Transactions Short

The longer a transaction holds locks, the higher the chance of a deadlock. Move non-essential work — like sending emails, calling APIs, or formatting data — outside of your transaction boundaries.

-- BAD: Doing expensive work inside the transaction
BEGIN TRANSACTION;
    UPDATE Sales.Orders SET Comments = 'Approved' WHERE OrderID = 73910;

    EXEC dbo.SendEmailNotification @OrderID = 73910;  -- slow! holds locks longer
COMMIT;


-- GOOD: Do the expensive work outside the transaction
UPDATE Sales.Orders SET Comments = 'Approved' WHERE OrderID = 73910;

EXEC dbo.SendEmailNotification @OrderID = 73910;  -- runs after lock is released

Strategy 3 — Use Read Committed Snapshot Isolation (RCSI)

By default, SQL Server readers block writers and vice versa. RCSI uses row versioning so readers never block writers, eliminating a large class of deadlocks between SELECT and UPDATE statements.

-- Enable Read Committed Snapshot Isolation on WideWorldImporters
ALTER DATABASE WideWorldImporters
SET READ_COMMITTED_SNAPSHOT ON;

-- Verify
SELECT name, is_read_committed_snapshot_on
  FROM sys.databases
 WHERE name = 'WideWorldImporters';

💡 Pro Tip: RCSI is enabled by default in Azure SQL Database. If you're writing code for Azure, you may already benefit from this without any extra configuration.

Strategy 4 — Optimize Your Queries and Add Indexes

Slow queries hold locks longer. A missing index on a WHERE clause column can force SQL Server to scan thousands of rows — locking all of them instead of just the ones you need.

-- Without an index, this scans all OrderLines and locks many rows
SELECT ol.StockItemID,
       SUM(ol.Quantity) AS TotalOrdered
  FROM Sales.OrderLines AS ol
 WHERE ol.OrderID = 73910    -- no index on OrderID? Full scan!
 GROUP BY ol.StockItemID;


-- Add a covering index to narrow the lock footprint
CREATE NONCLUSTERED INDEX IX_OrderLines_OrderID
    ON Sales.OrderLines (OrderID)
    INCLUDE (StockItemID, Quantity);

Strategy 5 — Handle Deadlocks Gracefully with a Retry Pattern

Even with perfect design, occasional deadlocks can happen under high concurrency. Catch error 1205 and retry the transaction automatically:

DECLARE @RetryCount INT = 0;
DECLARE @MaxRetries INT = 3;

WHILE @RetryCount <= @MaxRetries
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

            UPDATE Sales.Orders
               SET Comments = 'Retry-safe update'
             WHERE OrderID = 73910;

            UPDATE Warehouse.StockItemHoldings
               SET QuantityOnHand = QuantityOnHand - 1
             WHERE StockItemID = 201;

        COMMIT;
        BREAK;  -- success, exit the loop

    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 1205   -- Deadlock victim error
        BEGIN
            IF XACT_STATE() <> 0 ROLLBACK;
            SET @RetryCount += 1;
            WAITFOR DELAY '00:00:00.500';  -- brief pause before retry
        END
        ELSE
        BEGIN
            IF XACT_STATE() <> 0 ROLLBACK;
            THROW;  -- re-raise non-deadlock errors
        END
    END CATCH
END;

Common Mistakes & Tips

• Accessing tables in different orders across stored procedures — document and standardize your access patterns.
• Using SELECT * in a transaction — this reads more columns than needed, potentially locking more rows.
• Leaving transactions open while waiting for user input — always keep transactions fast and server-side.
• Forgetting to handle error 1205 in application code — without a retry, the user just sees a vague error.
• Over-indexing — too many indexes on write-heavy tables can actually increase deadlocks because SQL Server has to maintain lock consistency across all affected indexes.
• Ignoring the deadlock graph — SSMS renders it visually. Learn to read it; it tells you exactly which queries and resources were involved.

Summary & Key Takeaways

Frequently Asked Questions

Q: Can I prevent ALL deadlocks? Practically speaking, no — but you can make them extremely rare. Following consistent lock ordering, keeping transactions short, enabling RCSI, and adding proper indexes will eliminate the vast majority of deadlocks in typical OLTP workloads. Always add retry logic so that the rare deadlock that does occur is invisible to the end user.

Q: How do I find out which query caused the deadlock? Open SSMS and run the system_health Extended Events query shown in this article. Right-click the DeadlockGraph XML value and choose "Save Results As..." to save it as an .xdl file. Then open that file in SSMS — it renders a visual diagram showing exactly which processes, queries, and resources were involved in the deadlock cycle.

Q: Does RCSI affect performance? RCSI uses tempdb to store row versions, so it does add tempdb I/O and storage overhead. For most OLTP workloads, the concurrency gains far outweigh this cost. Monitor your tempdb usage after enabling it. In Azure SQL Database it's on by default, which is a good indicator of its general suitability.