Deadlocks and Blocking in SQL Server

• BLOCKING: Session B waits because Session A holds a lock B needs. Normal and temporary. Becomes a problem when A holds locks too long.

DEADLOCK : A waits for B AND B waits for A -> nobody can continue.
           SQL Server detects it (every ~5 sec), kills the cheaper one
           ("deadlock victim") with error 1205, the other continues.

Session A: locks Row 1  -----> wants Row 2 (held by B)
Session B: locks Row 2  -----> wants Row 1 (held by A)

HOW TO PRACTICE: Open TWO (or three) query windows. Run PART A once.

Then follow the steps in the commented blocks.

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Part A: Setup (run once)

DROP TABLE IF EXISTS dbo.WarehouseStock;
DROP TABLE IF EXISTS dbo.CustomerWallet;

CREATE TABLE dbo.WarehouseStock
(
    ItemID   INT PRIMARY KEY,
    ItemName VARCHAR(50) NOT NULL,
    Qty      INT NOT NULL
);

INSERT INTO dbo.WarehouseStock (ItemID, ItemName, Qty) VALUES
(1, 'Laptop', 50), (2, 'Mouse', 500), (3, 'Keyboard', 300), (4, 'Monitor', 80);

CREATE TABLE dbo.CustomerWallet
(
    WalletID   INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    Balance    DECIMAL(12,2) NOT NULL
);

INSERT INTO dbo.CustomerWallet (WalletID, CustomerID, Balance) VALUES
(1, 1, 100000), (2, 2, 50000), (3, 3, 25000);
GO

(4 rows affected)
(3 rows affected)

Part B: CREATE Blocking

Run these steps in two query windows in SSMS (Window 1 and Window 2), one step at a time, to see how the sessions affect each other.

-- WINDOW 1 : open transaction and keep it open (do NOT commit yet)
BEGIN TRAN;
    UPDATE dbo.WarehouseStock SET Qty = Qty - 1 WHERE ItemID = 1;

-- WINDOW 2 : this waits (spinning icon "Executing query...")
SELECT * FROM dbo.WarehouseStock WHERE ItemID = 1;

-- WINDOW 3 : find who is blocking whom -> run PART C queries here

-- WINDOW 1 : finish, and window 2 continues immediately
COMMIT;

Part C: Diagnose Blocking (run these while Part B is blocked)

Who is blocked and by whom

SELECT r.session_id            AS BlockedSession,
       r.blocking_session_id   AS BlockingSession,
       r.wait_type,
       r.wait_time / 1000.0    AS WaitSeconds,
       r.status,
       t.text                  AS BlockedQuery
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

What the Blocking session last ran (it may be idle with an open transaction)

SELECT s.session_id, s.login_name, s.host_name, s.status, s.open_transaction_count,
       t.text AS LastQuery
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE s.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0);

Locks currently held / requested in this database

SELECT l.request_session_id AS SessionID,
       l.resource_type, l.resource_description,
       l.request_mode       AS LockMode,      -- S, X, U, IX, IS ...
       l.request_status     AS Status,        -- GRANT or WAIT
       OBJECT_NAME(p.object_id) AS TableName
FROM sys.dm_tran_locks l
LEFT JOIN sys.partitions p ON p.hobt_id = l.resource_associated_entity_id
WHERE l.resource_database_id = DB_ID()
ORDER BY l.request_session_id;

Quick classic tool

EXEC sp_who2;

Last resort: kill the blocking session (it will Roll Back its work)

KILL 57;    -- replace 57 with the BlockingSession number

Part D: CREATE a Deadlock

Run these steps in two query windows in SSMS (Window 1 and Window 2), one step at a time, to see how the sessions affect each other.

-- WINDOW 1 (step 1)
BEGIN TRAN;
    UPDATE dbo.WarehouseStock SET Qty = Qty - 1 WHERE ItemID = 1;      -- locks item 1

-- WINDOW 2 (step 2)
BEGIN TRAN;
    UPDATE dbo.WarehouseStock SET Qty = Qty - 1 WHERE ItemID = 2;      -- locks item 2

-- WINDOW 1 (step 3) : waits for item 2
    UPDATE dbo.WarehouseStock SET Qty = Qty - 1 WHERE ItemID = 2;

-- WINDOW 2 (step 4) : waits for item 1  ==> DEADLOCK!
    UPDATE dbo.WarehouseStock SET Qty = Qty - 1 WHERE ItemID = 1;

Within ~5 seconds one window shows:
  Msg 1205 ... Transaction (Process ID xx) was deadlocked on lock resources with
  another process and has been chosen as the deadlock victim. Rerun the transaction.
In the OTHER window run:  COMMIT;

Part E: Read the Deadlock Graph (system_health Extended Event, always on)

SELECT TOP (5)
       xed.value('@timestamp', 'datetime2') AS DeadlockTime,
       xed.query('.')                       AS DeadlockGraphXml    -- click it in SSMS, save as .xdl to view the graph
FROM (
        SELECT CAST(target_data AS XML) AS TargetData
        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 data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(xed)
ORDER BY DeadlockTime DESC;
GO

Part F: How to Avoid Deadlocks

Access objects in the Same Order everywhere (e.g. always lower ItemID first)

CREATE OR ALTER PROCEDURE dbo.usp_MoveStock
    @FromItem INT, @ToItem INT, @Qty INT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @First INT  = IIF(@FromItem < @ToItem, @FromItem, @ToItem),
            @Second INT = IIF(@FromItem < @ToItem, @ToItem, @FromItem);

    BEGIN TRAN;
        -- lock both rows in a fixed order (UPDLOCK = "I'm going to update this")
        SELECT Qty FROM dbo.WarehouseStock WITH (UPDLOCK, ROWLOCK) WHERE ItemID = @First;
        SELECT Qty FROM dbo.WarehouseStock WITH (UPDLOCK, ROWLOCK) WHERE ItemID = @Second;

        UPDATE dbo.WarehouseStock SET Qty = Qty - @Qty WHERE ItemID = @FromItem;
        UPDATE dbo.WarehouseStock SET Qty = Qty + @Qty WHERE ItemID = @ToItem;
    COMMIT TRAN;
END;
GO

Retry automatically when chosen as deadlock victim (error 1205)

CREATE OR ALTER PROCEDURE dbo.usp_MoveStockWithRetry
    @FromItem INT, @ToItem INT, @Qty INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Retry INT = 3;

    WHILE @Retry > 0
    BEGIN
        BEGIN TRY
            EXEC dbo.usp_MoveStock @FromItem, @ToItem, @Qty;
            SET @Retry = 0;                               -- success -> exit loop
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK TRAN;
            IF ERROR_NUMBER() = 1205 AND @Retry > 1
            BEGIN
                SET @Retry -= 1;
                WAITFOR DELAY '00:00:00.200';             -- small back-off
            END
            ELSE
                THROW;
        END CATCH;
    END;
END;
GO

EXEC dbo.usp_MoveStockWithRetry @FromItem = 2, @ToItem = 1, @Qty = 5;
SELECT * FROM dbo.WarehouseStock;
GO

Other techniques

• Keep transactions short; never wait for user input inside a transaction.
• Add indexes so UPDATE/DELETE touch fewer rows (scans lock more rows).
• Use READ COMMITTED SNAPSHOT so readers don't take shared locks.
• SET DEADLOCK_PRIORITY LOW for less important jobs (they become the victim).
• SET LOCK_TIMEOUT 5000 so a blocked statement fails after 5 seconds instead of waiting forever.

SET LOCK_TIMEOUT 5000;          -- milliseconds, -1 = wait forever (default)
SELECT @@LOCK_TIMEOUT AS LockTimeoutMs;
SET LOCK_TIMEOUT -1;
GO

Practice Questions

1. Create a deadlock between dbo.WarehouseStock and dbo.CustomerWallet (two different tables).
2. Repeat it with SET DEADLOCK_PRIORITY LOW in window 2. Which window is the victim now?
3. While blocked, run C1 - C3 and write down: blocked session, blocking session, lock modes.
4. Use SET LOCK_TIMEOUT 3000 in window 2 and read the error number you get.