Locking and Blocking

-- Session-level (affects all subsequent queries)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;    -- default
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;          -- requires database option

-- Query-level override via table hint
SELECT * FROM dbo.Orders WITH (NOLOCK);            -- READ UNCOMMITTED
SELECT * FROM dbo.Orders WITH (HOLDLOCK);          -- SERIALIZABLE

-- Check current state
SELECT name, is_read_committed_snapshot_on
FROM sys.databases WHERE name = DB_NAME();

-- Enable (briefly requires single-user access during the switch)
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;

-- sys.dm_tran_version_store_space_usage columns: database_id, reserved_page_count, reserved_space_kb
SELECT DB_NAME(database_id)              AS db_name,
       reserved_page_count * 8.0 / 1024 AS version_store_mb
FROM sys.dm_tran_version_store_space_usage
ORDER BY reserved_page_count DESC;

-- Session holding version store open (oldest snapshot transaction)
SELECT TOP 5
    s.session_id, s.login_name,
    t.elapsed_time_seconds,
    t.transaction_sequence_num
FROM sys.dm_tran_active_snapshot_database_transactions t
JOIN sys.dm_exec_sessions s ON t.session_id = s.session_id
ORDER BY t.elapsed_time_seconds DESC;

-- Check current escalation setting
SELECT name, lock_escalation_desc FROM sys.tables WHERE name = 'Orders';

-- Escalate to partition first, then table (helps partitioned tables)
ALTER TABLE dbo.Orders SET (LOCK_ESCALATION = AUTO);

-- Detect lock escalation via Extended Events
CREATE EVENT SESSION [LockEscalation] ON SERVER
ADD EVENT sqlserver.lock_escalation (
    ACTION (sqlserver.sql_text, sqlserver.session_id)
    WHERE sqlserver.database_name = N'YourDatabase'
)
ADD TARGET package0.ring_buffer;
GO
ALTER EVENT SESSION [LockEscalation] ON SERVER STATE = START;

-- Atomic queue pop — skips rows locked by other consumers
BEGIN TRANSACTION;
    UPDATE TOP (1) dbo.WorkQueue
    SET    Status = 'Processing', StartedAt = SYSDATETIME()
    OUTPUT DELETED.WorkItemID, DELETED.Payload
    WHERE  Status = 'Pending'
      AND  ScheduledFor <= SYSDATETIME()
    ORDER BY QueuedAt;  -- requires workaround; see below
COMMIT;

-- More portable pattern using READPAST + UPDLOCK
DECLARE @ItemID INT;
BEGIN TRANSACTION;
    SELECT TOP (1) @ItemID = WorkItemID
    FROM   dbo.WorkQueue WITH (READPAST, ROWLOCK, UPDLOCK)
    WHERE  Status = 'Pending'
      AND  ScheduledFor <= SYSDATETIME()
    ORDER BY QueuedAt;

    IF @ItemID IS NOT NULL
        UPDATE dbo.WorkQueue
        SET    Status = 'Processing', StartedAt = SYSDATETIME()
        WHERE  WorkItemID = @ItemID;
COMMIT;

BEGIN TRANSACTION;
    -- UPDLOCK prevents other sessions from acquiring S lock
    -- HOLDLOCK (= SERIALIZABLE) holds a range lock preventing phantom inserts
    IF NOT EXISTS (
        SELECT 1 FROM dbo.Customer WITH (UPDLOCK, HOLDLOCK)
        WHERE  Email = @Email
    )
    BEGIN
        INSERT INTO dbo.Customer (Email, ...) VALUES (@Email, ...);
    END
COMMIT;

-- Session A: holds X on Orders row 1, wants X on Accounts row 1
-- Session B: holds X on Accounts row 1, wants X on Orders row 1
-- → SQL Server kills one session

-- Bad: A locks Orders then Accounts; B locks Accounts then Orders → deadlock
-- Good: both sessions always lock Orders → Accounts
BEGIN TRANSACTION;
    UPDATE dbo.Orders  SET Status = 'Processing' WHERE OrderID = @id;
    UPDATE dbo.Accounts SET Balance = Balance - @amt WHERE AccountID = @acct;
COMMIT;

-- Deadlock-prone: Session A and B both acquire S, both promote to X → deadlock
SELECT Qty FROM dbo.Inventory WHERE ProductID = @pid;
UPDATE dbo.Inventory SET Qty = Qty - 1 WHERE ProductID = @pid;

-- Fixed: UPDLOCK prevents the second S lock from being granted
BEGIN TRANSACTION;
    SELECT Qty FROM dbo.Inventory WITH (UPDLOCK, ROWLOCK) WHERE ProductID = @pid;
    UPDATE dbo.Inventory SET Qty = Qty - 1 WHERE ProductID = @pid;
COMMIT;

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

-- Current blocking chains
SELECT  r.session_id      AS blocked_session,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time / 1000.0  AS wait_sec,
        r.wait_resource,
        SUBSTRING(st.text, (r.statement_start_offset/2)+1, 200) AS blocked_stmt,
        s.open_transaction_count,
        s.login_name,
        s.program_name
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) st
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;

-- What is the blocking session doing?
SELECT s.session_id, s.status,
       s.open_transaction_count,
       SUBSTRING(st.text, 1, 200) AS current_or_last_stmt
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(
    ISNULL(r.sql_handle, s.last_request_sql_handle)) st
WHERE s.session_id = @blocking_session_id;

SET LOCK_TIMEOUT 5000;  -- fail after 5 seconds (milliseconds)

-- Instead of: SELECT * FROM dbo.Orders WITH (NOLOCK)
-- Enable RCSI once at the database level:
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;
-- Then the default READ COMMITTED reads from version store — no S locks, consistent results
SELECT * FROM dbo.Orders;