SQL Server Blocking vs Deadlocks: What They Are, How to Find Them, and How to Fix Them

Blocking and deadlocks are the two most common locking problems in SQL Server and they get confused constantly. A DBA gets a call that the application is slow and users are timing out. The first question is always: is this blocking or a deadlock? The answer determines everything about how you investigate it, how you fix it, and whether it resolves on its own or requires intervention.

They are fundamentally different problems. Blocking is one session waiting for another to release a lock. It resolves on its own when the lock is released. A deadlock is two sessions waiting on each other in a circle that can never resolve. SQL Server has to kill one of them to break the cycle. The diagnostic queries are different, the resolution strategies are different, and the prevention approaches are different.

This article covers both in full: what each is, how SQL Server handles each, how to detect and diagnose each, how to fix each, and what RCSI actually does and does not solve.

1 What Blocking Is and How It Works Beginner

Blocking occurs when one SQL Server session holds a lock on a resource and another session needs a conflicting lock on the same resource. The second session waits. It does not error. It does not crash. It simply sits in a queue until the first session releases its lock.

SQL Server uses locks to maintain data consistency during concurrent access. A session reading a row takes a shared lock. A session modifying a row takes an exclusive lock. Shared and exclusive locks are incompatible. If Session 1 holds an exclusive lock on a row that Session 2 wants to read or modify, Session 2 waits. This is blocking and it is by design. It is SQL Server protecting you from reading uncommitted data or two sessions overwriting each other’s changes.

Blocking becomes a problem when the wait is long. A transaction that holds locks for seconds or minutes while doing other work, querying slowly, or waiting for application logic to complete blocks every other session that needs those same resources. The blocked sessions pile up. Their waits accumulate. The application sees timeouts and slowdowns that appear to be performance problems but are actually queuing problems.

The Blocking Chain

Blocking frequently cascades into chains. Session 1 blocks Session 2. Session 3 needs something Session 2 holds and now blocks on Session 2. Session 4 blocks on Session 3. The entire chain traces back to Session 1, which is called the lead blocker. Killing Session 4 does nothing. The chain immediately re-forms. The only effective intervention is finding and addressing the lead blocker at the head of the chain.

2 What a Deadlock Is and How SQL Server Handles It Beginner

A deadlock is a specific type of circular blocking that can never resolve itself. Session 1 holds a lock on Resource A and wants a lock on Resource B. Session 2 holds a lock on Resource B and wants a lock on Resource A. Neither session can proceed. Neither session will release its locks because it is waiting to acquire more locks before it can commit. They will wait forever unless something intervenes.

SQL Server has a dedicated background thread called the lock monitor that checks for deadlock cycles every five seconds. When it detects a cycle it selects one of the sessions as the deadlock victim, rolls back that session’s transaction, and raises error 1205. The surviving session receives its requested lock and proceeds normally. The victim receives an error and must handle the rollback.

The selection of which session becomes the victim is based on the cost of rolling back each transaction. SQL Server picks the session whose rollback is cheapest, generally the one with the least undo work. You can influence this with the DEADLOCK_PRIORITY session setting, but you cannot guarantee which session SQL Server picks without controlling both sessions.

3 The Key Differences Side by Side Beginner

4 Detecting and Diagnosing Blocking Intermediate

Blocking is visible in real time through DMVs. The key view is sys.dm_exec_requests which shows every active request and its blocking session ID. A non-zero blocking_session_id means the session is blocked.

-- ============================================================
-- BLOCKING: Real-time detection
-- Run this when users report slowness or timeouts
-- ============================================================

SELECT
    r.session_id                            AS BlockedSession,
    r.blocking_session_id                   AS BlockedBy,
    DB_NAME(r.database_id)                  AS DatabaseName,
    r.wait_type,
    r.wait_time / 1000                      AS WaitSeconds,
    r.status,
    s.login_name,
    s.host_name,
    s.program_name,
    LEFT(qt.text, 500)                      AS BlockedQuery
FROM sys.dm_exec_requests         r
JOIN sys.dm_exec_sessions          s  ON s.session_id    = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;

-- ============================================================
-- Find the LEAD BLOCKER (head of the blocking chain)
-- The session doing the blocking that is NOT itself blocked
-- Killing a middle-chain session rarely helps permanently
-- ============================================================

SELECT
    s.session_id                            AS LeadBlockerSession,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    r.command,
    r.total_elapsed_time / 1000             AS ElapsedSeconds,
    LEFT(qt.text, 500)                      AS LeadBlockerQuery,
    t.text                                  AS OpenTransaction
FROM sys.dm_exec_sessions         s
LEFT JOIN sys.dm_exec_requests    r  ON r.session_id    = s.session_id
LEFT JOIN sys.dm_exec_connections c  ON c.session_id    = s.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) qt
WHERE s.session_id IN (
    -- Sessions that are blocking others but not blocked themselves
    SELECT DISTINCT blocking_session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id > 0
)
AND s.session_id NOT IN (
    SELECT session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id > 0
)
ORDER BY s.session_id;

-- ============================================================
-- Full blocking chain: who is blocking whom
-- ============================================================

WITH BlockingChain AS (
    SELECT
        r.session_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time / 1000          AS WaitSeconds,
        DB_NAME(r.database_id)      AS DatabaseName,
        CAST(0 AS INT)              AS Level,
        CAST(r.session_id AS VARCHAR(1000)) AS ChainPath
    FROM sys.dm_exec_requests r
    WHERE r.blocking_session_id = 0
    AND   r.session_id IN (
        SELECT DISTINCT blocking_session_id
        FROM sys.dm_exec_requests
        WHERE blocking_session_id > 0
    )
    UNION ALL
    SELECT
        r.session_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time / 1000,
        DB_NAME(r.database_id),
        bc.Level + 1,
        CAST(bc.ChainPath + ' -> ' + CAST(r.session_id AS VARCHAR(10)) AS VARCHAR(1000))
    FROM sys.dm_exec_requests r
    JOIN BlockingChain bc ON bc.session_id = r.blocking_session_id
)
SELECT
    REPLICATE('  ', Level) + CAST(session_id AS VARCHAR(10)) AS SessionTree,
    blocking_session_id,
    WaitSeconds,
    wait_type,
    DatabaseName,
    ChainPath
FROM BlockingChain
ORDER BY ChainPath;

5 Detecting and Diagnosing Deadlocks Intermediate

Unlike blocking, deadlocks happen fast and are gone by the time you look. You must capture them proactively. The system_health Extended Events session that SQL Server runs automatically captures deadlock XML. This is available without any additional setup on SQL Server 2012 and later.

-- ============================================================
-- DEADLOCKS: Read from system_health XE session ring buffer
-- No setup required -- SQL Server captures this automatically
-- ============================================================

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

-- ============================================================
-- Extract victim and process details from deadlock XML
-- ============================================================

;WITH DeadlockData AS (
    SELECT
        xdr.value('@timestamp', 'datetime2')        AS DeadlockTime,
        xdr.query('.')                              AS DeadlockGraph,
        xdr.value('(//victim-list/victimProcess/@id)[1]', 'varchar(50)') AS VictimID
    FROM (
        SELECT CAST(xst.target_data AS XML) AS target_data
        FROM sys.dm_xe_session_targets  xst
        JOIN sys.dm_xe_sessions          xs  ON xs.address = xst.event_session_address
        WHERE xs.name = 'system_health' AND xst.target_name = 'ring_buffer'
    ) AS data
    CROSS APPLY target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS t(xdr)
)
SELECT
    DeadlockTime,
    VictimID,
    -- Extract query text for each process in the deadlock
    DeadlockGraph.value('(//process[@id=sql:column("VictimID")]/inputbuf)[1]', 'nvarchar(max)') AS VictimQuery
FROM DeadlockData
ORDER BY DeadlockTime DESC;

-- ============================================================
-- Count deadlocks in the last 24 hours
-- ============================================================

;WITH DeadlockCount AS (
    SELECT
        CAST(xdr.value('@timestamp', 'datetime2') AS DATETIME2(0)) AS DeadlockTime
    FROM (
        SELECT CAST(xst.target_data AS XML) AS target_data
        FROM sys.dm_xe_session_targets  xst
        JOIN sys.dm_xe_sessions          xs  ON xs.address = xst.event_session_address
        WHERE xs.name = 'system_health' AND xst.target_name = 'ring_buffer'
    ) AS data
    CROSS APPLY target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS t(xdr)
)
SELECT
    COUNT(*)                    AS DeadlockCount,
    MIN(DeadlockTime)           AS OldestDeadlock,
    MAX(DeadlockTime)           AS MostRecentDeadlock
FROM DeadlockCount
WHERE DeadlockTime >= DATEADD(HOUR, -24, GETDATE());

6 Resolving Active Blocking Beginner

When blocking is happening right now and users are impacted, the immediate options are to wait for the lead blocker to finish naturally, or to kill the lead blocker if the wait is unacceptable. Killing the lead blocker rolls back its transaction immediately and releases all its locks. The entire blocked chain proceeds.

-- Kill the lead blocker
-- Replace 54 with the actual lead blocker session ID from the detection queries
KILL 54;

-- KILL rolls back the transaction and releases all locks
-- The blocked sessions will proceed within seconds
-- Confirm blocking is cleared:
SELECT session_id, blocking_session_id, wait_type, wait_time
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;

-- Check what the lead blocker had open before killing
-- (run BEFORE killing if time allows)
SELECT
    s.session_id,
    s.open_transaction_count,
    s.login_time,
    s.last_request_start_time,
    s.login_name,
    s.host_name,
    s.program_name
FROM sys.dm_exec_sessions s
WHERE s.session_id = 54;  -- lead blocker session ID

7 Fixing Blocking Long Term Intermediate

Persistent blocking problems have four main causes and each has a specific fix.

Long-Running Transactions

Transactions that stay open longer than necessary hold locks longer than necessary. The fix is to keep transactions as short as possible. Open the transaction, do the data modification work, commit immediately. Do not open a transaction, call a web service, wait for user input, or perform non-database operations in the middle of an open transaction. Every second an open transaction holds locks is a second that other sessions must wait.

Missing Indexes Causing Lock Escalation

When SQL Server scans a table instead of seeking an index, it takes shared locks on every row it reads even if it only needs one. On large tables this means thousands of row locks that escalate to a table lock. A table lock blocks every other session that needs any row in the table. Adding a covering index converts a full table scan into a narrow index seek, dramatically reducing the lock footprint.

-- Check for lock escalation events in the current session
-- Lock escalation converts many row locks to a table lock
-- This is a common cause of unexpected widespread blocking

SELECT
    OBJECT_NAME(ddios.object_id)            AS TableName,
    ddios.index_id,
    ddios.range_scan_count,
    ddios.singleton_lookup_count,
    ddios.leaf_insert_count,
    ddios.leaf_update_count,
    ddios.leaf_delete_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios
WHERE OBJECTPROPERTY(ddios.object_id, 'IsUserTable') = 1
ORDER BY ddios.range_scan_count DESC;

-- High range_scan_count on a table = queries scanning instead of seeking
-- Add indexes to reduce scan count and reduce lock footprint

-- Check lock escalation settings per table
SELECT
    OBJECT_NAME(object_id)                  AS TableName,
    lock_escalation_desc
FROM sys.tables
WHERE OBJECTPROPERTY(object_id, 'IsUserTable') = 1
ORDER BY OBJECT_NAME(object_id);

-- Disable lock escalation on a specific table (use carefully)
-- Only appropriate when you have verified escalation is the blocking root cause
ALTER TABLE dbo.YourTable SET (LOCK_ESCALATION = DISABLE);

Implicit Transactions

Applications using IMPLICIT_TRANSACTIONS ON or database drivers that default to wrapping each statement in a transaction hold locks until an explicit COMMIT or ROLLBACK is issued. If the application does not commit promptly, locks accumulate. Check for sessions with a high open_transaction_count in sys.dm_exec_sessions.

Blocking Alerts

Set up a blocking alert so you are notified when blocking exceeds a threshold rather than discovering it from user complaints. SQL Server Agent has a built-in blocked process threshold setting that fires an alert when any session has been blocked for longer than a configured number of seconds.

-- Configure blocked process threshold to fire after 10 seconds of blocking
-- This enables the blocked process report event
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'blocked process threshold (s)', 10;
RECONFIGURE;

-- Create a SQL Agent alert that fires on blocking threshold
USE msdb;
EXEC sp_add_alert
    @name              = 'SQLYARD - Blocking Detected',
    @message_id        = 0,
    @severity          = 0,
    @enabled           = 1,
    @delay_between_responses = 60,
    @include_event_description_in = 1,
    @event_description_keyword = N'blocked',
    @performance_condition = N'SQLServer:General Statistics|Processes blocked||>|0';

8 Fixing Deadlocks Intermediate

Deadlocks are prevented by eliminating the circular dependency. The two most reliable approaches are fixing the resource access order and using row versioning isolation.

Fix the Access Order

Most deadlocks happen because two transactions access the same resources in opposite orders. Transaction A locks Table1 then Table2. Transaction B locks Table2 then Table1. When they run concurrently they deadlock. The fix is to ensure all transactions that access both tables always access them in the same order. Transaction B should access Table1 then Table2 just like Transaction A. With a consistent access order the circular dependency cannot form.

-- DEADLOCK EXAMPLE: Access order problem
-- This combination deadlocks under concurrent execution

-- Session 1:
BEGIN TRANSACTION;
UPDATE dbo.Orders   SET Status = 2 WHERE OrderID = 101;  -- locks Orders row
UPDATE dbo.Customers SET LastOrder = GETDATE() WHERE CustomerID = 5;  -- needs Customers
-- waits here if Session 2 has Customers locked

-- Session 2 (concurrent):
BEGIN TRANSACTION;
UPDATE dbo.Customers SET LastOrder = GETDATE() WHERE CustomerID = 5;  -- locks Customers
UPDATE dbo.Orders   SET Status = 2 WHERE OrderID = 101;  -- needs Orders
-- waits here if Session 1 has Orders locked
-- DEADLOCK: circular wait

-- FIX: Consistent access order in both sessions
-- Always update Customers first, then Orders

-- Session 1 (fixed):
BEGIN TRANSACTION;
UPDATE dbo.Customers SET LastOrder = GETDATE() WHERE CustomerID = 5;
UPDATE dbo.Orders   SET Status = 2 WHERE OrderID = 101;
COMMIT;

-- Session 2 (fixed):
BEGIN TRANSACTION;
UPDATE dbo.Customers SET LastOrder = GETDATE() WHERE CustomerID = 5;
UPDATE dbo.Orders   SET Status = 2 WHERE OrderID = 101;
COMMIT;
-- Same order: no circular dependency possible

Application-Level Retry for Error 1205

-- Stored procedure with deadlock retry logic
-- Retry up to 3 times with a brief wait between attempts

CREATE PROCEDURE dbo.usp_UpdateOrderWithRetry
    @OrderID    INT,
    @CustomerID INT,
    @NewStatus  INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Attempts    INT = 0;
    DECLARE @MaxAttempts INT = 3;
    DECLARE @Success     BIT = 0;

    WHILE @Attempts < @MaxAttempts AND @Success = 0
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;

            UPDATE dbo.Customers
            SET    LastOrder = SYSDATETIME()
            WHERE  CustomerID = @CustomerID;

            UPDATE dbo.Orders
            SET    Status = @NewStatus
            WHERE  OrderID = @OrderID;

            COMMIT TRANSACTION;
            SET @Success = 1;

        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0
                ROLLBACK TRANSACTION;

            IF ERROR_NUMBER() = 1205  -- deadlock victim
            BEGIN
                SET @Attempts += 1;
                IF @Attempts < @MaxAttempts
                    WAITFOR DELAY '00:00:00.050';  -- 50ms wait before retry
            END
            ELSE
                THROW;  -- re-raise non-deadlock errors
        END CATCH
    END

    IF @Success = 0
        RAISERROR('Operation failed after %d deadlock retries', 16, 1, @MaxAttempts);
END

9 RCSI: What It Solves and What It Does Not Intermediate

Read Committed Snapshot Isolation is the single most impactful configuration change for reducing blocking in OLTP SQL Server databases. It is frequently recommended as the primary fix for blocking problems and for most OLTP workloads it genuinely is. But it is not a universal fix and enabling it without understanding the trade-offs has caused problems in production environments.

What RCSI Does

Under standard READ COMMITTED, readers take shared locks on data they read. These shared locks conflict with exclusive locks that writers hold, causing readers to block on writers and writers to block on readers. Under RCSI, readers do not take shared locks. Instead they read from a row version store in tempdb that reflects the last committed state of the data. Writers do not block readers. Readers do not block writers. The most common source of blocking in OLTP systems disappears.

-- Enable RCSI on a database
-- Requires brief exclusive access -- plan a maintenance window

-- Check current state
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'YourDatabase';

-- Enable RCSI (requires single user mode briefly)
ALTER DATABASE YourDatabase
SET READ_COMMITTED_SNAPSHOT ON;

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

What RCSI Does NOT Solve

• Writer-writer blocking. Two sessions trying to modify the same row still block each other. Only one session can hold an exclusive lock on a row at a time. RCSI only eliminates reader-writer conflicts.
• All deadlocks. RCSI eliminates deadlocks that involve a reader and a writer in the cycle. Deadlocks between two writers on different resources in different orders are not affected.
• Long-running transaction problems. A long-running transaction still blocks other writers on the rows it holds exclusive locks on. RCSI does not shorten transactions or reduce their lock duration.
• Lock escalation blocking. If a transaction escalates to a table lock, that table lock still blocks other writers under RCSI.

The Trade-offs

• TempDB version store overhead. Row versions are stored in tempdb. On a high-write workload, the version store grows. TempDB must have adequate space and I/O capacity. Monitor tempdb version store usage after enabling RCSI.
• Slightly increased per-row storage overhead. Each row requires a 14-byte version pointer when RCSI is active, even if the row is not currently versioned.
• HOLDLOCK interaction. As covered in the SQLYARD MERGE article, RCSI combined with HOLDLOCK hints can introduce deadlocks that did not exist before. If you enable RCSI on a database containing MERGE statements with HOLDLOCK, test for deadlocks under concurrent load.

-- Monitor tempdb version store after enabling RCSI
SELECT
    reserved_page_count * 8 / 1024          AS VersionStoreMB,
    reserved_space_kb / 1024                AS VersionStoreAllocatedMB
FROM sys.dm_db_file_space_usage
WHERE database_id = 2;  -- tempdb

-- Watch for version store cleanup lag
SELECT
    SUM(version_store_reserved_page_count) * 8 / 1024 AS VersionStoreMB
FROM sys.dm_os_buffer_descriptors
WHERE database_id = 2;

10 Index Design and Transaction Length Intermediate

The two most effective long-term prevention strategies for both blocking and deadlocks are good index design and short transactions. These address the root causes rather than compensating for them.

Index Design Reduces Lock Footprint

A query that seeks a specific row through an index locks only that row. A query that scans a table locks every row it reads. On a large table this means thousands of locks versus one lock. Fewer locks means less conflict with concurrent sessions, less chance of escalation to a table lock, and less deadlock surface area. Review the most frequently blocked queries and confirm they have appropriate supporting indexes.

Short Transactions Reduce Lock Duration

The longer a transaction is open the longer it holds its locks and the more likely it is to conflict with other sessions. Keep transactions short by doing all data reads before opening the transaction, opening the transaction only for the actual data modification work, committing as soon as the modification is complete, and never performing non-database operations inside an open transaction.

11 Quick Reference: Which Problem Do You Have? Beginner

References

• Microsoft Docs: Transaction Locking and Row Versioning Guide
• Microsoft Docs: sys.dm_exec_requests
• Microsoft Docs: Use the System Health Session
• Microsoft Docs: SQL Server Deadlocks Guide
• Microsoft Docs: SET DEADLOCK_PRIORITY
• SQLYARD: SQL Server Deadlock Alerts: Why They Don't Fire and How to Fix Them
• SQLYARD: How to Find the Lead Blocker in SQL Server
• SQLYARD: SQL Server Locking: The Old Way and Optimized Locking in SQL Server 2025
• SQLYARD: SQL Server MERGE: Why the Community Argues About It and What to Use Instead
• SQLYARD: SQL Server Performance Tuning Complete Guide