SQL Server Blocking and Deadlocks: Detection, Diagnosis and Resolution (2026)

How Blocking Works

SQL Server uses locks to maintain data consistency. When session A holds an exclusive lock on a row and session B tries to read or modify that same row, B must wait until A releases the lock (by committing or rolling back). Session B is blocked.

Blocking is not inherently a bug — it is the correct behavior of a transactional database. The problem arises when:

• Transactions are kept open longer than necessary (application holds a connection with an uncommitted transaction while doing non-database work)
• A single long-running UPDATE holds locks on many rows, blocking all reads
• Under the default READ COMMITTED isolation level, readers take shared locks and block writers (and vice versa)

Detecting Live Blocking

The fastest check for current blocking:

-- Sessions currently blocked
SELECT
    r.session_id                                AS blocked_spid,
    r.blocking_session_id                       AS blocker_spid,
    r.wait_type,
    r.wait_time / 1000.0                        AS wait_s,
    r.wait_resource,
    DB_NAME(r.database_id)                      AS db_name,
    s.login_name,
    s.host_name,
    s.program_name,
    SUBSTRING(t.text, r.statement_start_offset/2+1,
        (CASE WHEN r.statement_end_offset = -1 THEN LEN(t.text)*2
              ELSE r.statement_end_offset END - r.statement_start_offset)/2+1) AS blocked_sql
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;

-- What is the BLOCKER actually doing / holding
SELECT
    s.session_id,
    s.status,
    s.login_name,
    s.host_name,
    s.last_request_start_time,
    t2.text AS blocker_sql,
    r.command,
    r.open_transaction_count
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t2
WHERE s.session_id = 55;  -- replace with the blocker SPID

Walking the Blocking Chain

In a busy OLTP system you often get blocking chains — A blocks B which blocks C which blocks D. To see the full chain with a recursive CTE:

WITH blocking_chain AS (
    -- Anchor: sessions that are not themselves blocked (root blockers)
    SELECT
        session_id,
        blocking_session_id,
        wait_type,
        wait_time,
        CAST(session_id AS VARCHAR(1000)) AS chain,
        0 AS depth
    FROM sys.dm_exec_requests
    WHERE blocking_session_id = 0
      AND session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id > 0)

    UNION ALL

    -- Recursive: sessions blocked by a session already in the chain
    SELECT
        r.session_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time,
        CAST(bc.chain + ' → ' + CAST(r.session_id AS VARCHAR(10)) AS VARCHAR(1000)),
        bc.depth + 1
    FROM sys.dm_exec_requests r
    JOIN blocking_chain bc ON r.blocking_session_id = bc.session_id
)
SELECT chain, wait_type, wait_time / 1000.0 AS wait_s, depth
FROM blocking_chain
ORDER BY chain;

Capturing Blocking History

Live DMVs only show current state. To capture blocking over time, use a scheduled job or the built-in blocked process threshold:

-- Enable blocked process reporting (reports sessions blocked > 5 seconds)
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'blocked process threshold', 5;  -- seconds
RECONFIGURE;

-- Capture via Extended Events (system_health already captures this by default)
-- Or create a dedicated session:
CREATE EVENT SESSION [BlockedProcesses] ON SERVER
ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.database_name, sqlserver.sql_text, sqlserver.username)
)
ADD TARGET package0.ring_buffer(SET max_memory = 51200)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);

ALTER EVENT SESSION [BlockedProcesses] ON SERVER STATE = START;

-- Read blocked process reports from ring buffer
SELECT
    xdr.value('@timestamp', 'datetime2')    AS blocked_at,
    xdr.value('(blocked-process/process/@spid)[1]', 'int') AS blocked_spid,
    xdr.value('(blocking-process/process/@spid)[1]', 'int') AS blocker_spid,
    xdr.value('(blocked-process/process/inputbuf)[1]', 'nvarchar(max)') AS blocked_sql,
    xdr.value('(blocking-process/process/inputbuf)[1]', 'nvarchar(max)') AS blocker_sql
FROM (
    SELECT CAST(target_data AS XML) AS target_xml
    FROM sys.dm_xe_session_targets t
    JOIN sys.dm_xe_sessions s ON t.event_session_address = s.address
    WHERE s.name = 'BlockedProcesses'
      AND t.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_xml.nodes('//RingBufferTarget/event') AS xTable(xdr)
ORDER BY blocked_at DESC;

Fixing Blocking: Short-Term and Long-Term

Short-term (emergency):

-- Kill the blocking session (use sparingly — will roll back its transaction)
KILL 55;  -- replace with the root blocker SPID

Long-term fixes:

• Shorten transactions — do all non-database work before opening a transaction; commit immediately after the last DML statement
• Add covering indexes — queries that scan large ranges hold shared locks on more rows than necessary; a covering index reduces scan scope
• Use NOLOCK / READ UNCOMMITTED — a fast workaround for read-heavy reports, but risks dirty reads; not suitable for financial data
• Enable RCSI — the correct long-term solution (see next section)
• Lock timeouts — SET LOCK_TIMEOUT 5000 makes blocked sessions fail fast instead of waiting indefinitely
• Reduce transaction scope — process large batch updates in smaller chunks to release locks between chunks

Read Committed Snapshot Isolation (RCSI)

RCSI is the most impactful single change you can make to reduce blocking in an OLTP database. Under RCSI:

• Readers never block writers — reads see the last committed version of a row stored in the version store (TempDB), never taking shared locks
• Writers never block readers — writers generate row versions but do not block concurrent reads
• Writers still block other writers (exclusive locks still apply)

-- Enable RCSI (requires brief exclusive DB access; do during low-traffic window)
-- Step 1: Set to single-user to clear connections
ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Step 2: Enable RCSI
ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON;

-- Step 3: Return to multi-user
ALTER DATABASE YourDB SET MULTI_USER;

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

How Deadlocks Work

A deadlock requires at least two sessions, each holding a resource the other needs:

-- Session A
BEGIN TRAN;
UPDATE dbo.Accounts SET Balance = Balance - 100 WHERE AccountID = 1;  -- locks row 1
-- (pause — waiting for session B to release row 2)
UPDATE dbo.Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

-- Session B (concurrent)
BEGIN TRAN;
UPDATE dbo.Accounts SET Balance = Balance - 50 WHERE AccountID = 2;   -- locks row 2
-- (pause — waiting for session A to release row 1)
UPDATE dbo.Accounts SET Balance = Balance + 50 WHERE AccountID = 1;

Session A holds row 1 and waits for row 2. Session B holds row 2 and waits for row 1. Neither can proceed. SQL Server's deadlock monitor detects this cycle every ~5 seconds, picks the session with the lower deadlock priority (or smaller rollback cost) as the victim, kills it with error 1205, and lets the other session proceed.

Reading the Deadlock Graph

The deadlock graph is an XML document (visualized as a diagram in SSMS) with three key areas:

• Process nodes (ovals) — each participating session, showing the SQL statement being executed and its lock requests
• Resource nodes (rectangles) — the locked objects (rows, pages, tables) involved in the cycle
• Edges — arrows showing "owns" (solid) and "waits for" (dashed) relationships
• Victim — the process node marked with an X

To read a deadlock graph: find the cycle. Follow the "waits for" edges until you come back to where you started. Every lock in that cycle is a potential resolution point.

Extracting Deadlocks from System Health Session

SQL Server's built-in system_health Extended Events session captures all deadlock graphs automatically — no setup required:

-- Extract deadlock graphs from system_health ring buffer
WITH system_health_data AS (
    SELECT CAST(target_data AS XML) AS target_xml
    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'
)
SELECT
    xdr.value('@timestamp', 'datetime2(3)')           AS deadlock_time,
    xdr.query('.')                                     AS deadlock_graph_xml
FROM system_health_data
CROSS APPLY target_xml.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS xTable(xdr)
ORDER BY deadlock_time DESC;

Copy the deadlock_graph_xml value, save it as a .xdl file, and open it in SSMS to see the visual deadlock graph with process details.

Extended Events Deadlock Trace

For persistent deadlock capture with more detail than system_health provides:

CREATE EVENT SESSION [DeadlockCapture] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(
    SET filename = N'C:\SQLLogs\Deadlocks.xel',
    max_file_size = 50,       -- MB
    max_rollover_files = 10
)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS, TRACK_CAUSALITY = ON);

ALTER EVENT SESSION [DeadlockCapture] ON SERVER STATE = START;

-- Query the file target
SELECT
    DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()),
        xdr.value('@timestamp','datetime2(3)'))     AS deadlock_local_time,
    xdr.query('.')                                   AS deadlock_xml
FROM (
    SELECT CAST(event_data AS XML) AS event_xml
    FROM sys.fn_xe_file_target_read_file(
        'C:\SQLLogs\Deadlocks*.xel', NULL, NULL, NULL)
) AS data
CROSS APPLY event_xml.nodes('event') AS xTable(xdr)
ORDER BY deadlock_local_time DESC;

Common Deadlock Patterns

1. Access Order Deadlock

Two transactions access the same rows in different order. Solution: enforce a consistent access order in all code that modifies multiple rows.

2. Reader-Writer Deadlock

A reader (SELECT with shared lock) and a writer (UPDATE with exclusive lock) deadlock when the reader also needs to upgrade its lock. Common under READ COMMITTED with long transactions. Solution: RCSI eliminates read-write deadlocks entirely.

3. Index vs Table Deadlock

Session A scans a non-clustered index and tries to do key lookups into the clustered index. Session B updates the clustered index and tries to update the non-clustered index. The two lock paths cross. Solution: add covering columns to the non-clustered index to eliminate the key lookup.

4. Cascade / Trigger Deadlock

A DELETE on a parent table triggers a cascading DELETE on a child table. Concurrent inserts into the child table lock child rows first, then try to read the parent. Deadlock cycle forms between the cascade path and the insert path. Solution: avoid cascading deletes; do multi-step deletes in explicit transactions with consistent order.

Fixing Deadlocks

Fix 1: Consistent lock order — ensure all code paths that update multiple tables or rows do so in the same order (e.g., always update AccountID in ascending order).

-- Instead of arbitrary order:
UPDATE Accounts WHERE AccountID IN (1, 5) -- might process 5 then 1 in one session, 1 then 5 in another

-- Force consistent order:
UPDATE Accounts
SET ...
WHERE AccountID IN (
    SELECT AccountID FROM (VALUES(1),(5)) v(AccountID)
    -- ORDER BY AccountID  -- SQL Server processes in index key order with a sorted index scan
)
OPTION (MAXDOP 1);

Fix 2: Enable RCSI — eliminates read-write deadlocks completely.

Fix 3: Shorten transactions — the shorter the transaction, the shorter the lock hold time, the less chance of a cycle forming.

Fix 4: Add covering indexes — eliminate key lookups that create cross-index lock paths.

Fix 5: Set deadlock priority — for non-critical batch processes that can be safely retried, lower their deadlock priority so they are always chosen as the victim rather than the OLTP session:

SET DEADLOCK_PRIORITY LOW;  -- this session is always chosen as victim
-- Do batch work
SET DEADLOCK_PRIORITY NORMAL;

Application-Level Retry Logic

Even with all the fixes above, occasional deadlocks can occur. Applications should always handle error 1205 with an automatic retry:

-- T-SQL retry pattern
DECLARE @retries INT = 3;
DECLARE @attempt INT = 0;

WHILE @attempt < @retries
BEGIN
    BEGIN TRY
        BEGIN TRAN;
        -- Your DML here
        UPDATE dbo.Accounts SET Balance = Balance - 100 WHERE AccountID = @ID;
        COMMIT TRAN;
        SET @attempt = @retries;  -- success — exit loop
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRAN;
        IF ERROR_NUMBER() = 1205  -- deadlock victim
        BEGIN
            SET @attempt += 1;
            IF @attempt < @retries
                WAITFOR DELAY '00:00:00.1';  -- brief back-off before retry
            ELSE
                THROW;  -- exhausted retries — surface to caller
        END
        ELSE
            THROW;  -- non-deadlock error — surface immediately
    END CATCH
END

Read Next

• Wait Statistics: sys.dm_os_wait_stats
• Locking: Lock Types, Escalation and RCSI
• Isolation Levels: READ UNCOMMITTED to Snapshot
• TempDB Internals: Version Store and Optimization
• All SQL Server Articles