13 — Transactions, Locking, and Concurrency

-- Autocommit: each statement commits immediately
INSERT INTO Orders (CustomerID, Amount) VALUES (1, 100.00);

-- Explicit transaction
BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
COMMIT TRANSACTION;

-- Rollback on error
BEGIN TRANSACTION;
    UPDATE Inventory SET Quantity = Quantity - 1 WHERE ProductID = 42;
    IF @@ROWCOUNT = 0
    BEGIN
        ROLLBACK TRANSACTION;
        RAISERROR('Product not found', 16, 1);
        RETURN;
    END
COMMIT TRANSACTION;

BEGIN TRANSACTION;           -- @@TRANCOUNT = 1
    BEGIN TRANSACTION;       -- @@TRANCOUNT = 2
        -- Do work
    COMMIT TRANSACTION;      -- @@TRANCOUNT = 1  (does NOT commit)
COMMIT TRANSACTION;          -- @@TRANCOUNT = 0  (actually commits)

-- Dangerous: inner ROLLBACK kills the whole thing
BEGIN TRANSACTION;           -- @@TRANCOUNT = 1
    BEGIN TRANSACTION;       -- @@TRANCOUNT = 2
        ROLLBACK TRANSACTION;-- @@TRANCOUNT = 0  (EVERYTHING rolled back!)
-- Now: COMMIT would error — no open transaction

BEGIN TRANSACTION;
    INSERT INTO AuditLog (Event) VALUES ('start');

    SAVE TRANSACTION step1;   -- mark savepoint

    BEGIN TRY
        DELETE FROM Orders WHERE CustomerID = 99;
        IF @@ROWCOUNT > 1000
        BEGIN
            ROLLBACK TRANSACTION step1;  -- rolls back only to savepoint
            -- @@TRANCOUNT remains 1 — outer transaction still open
        END
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION step1;
        -- log error, continue outer transaction
    END CATCH

    INSERT INTO AuditLog (Event) VALUES ('end');
COMMIT TRANSACTION;

BEGIN TRY
    BEGIN TRANSACTION;
    -- ... work ...
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() = -1
        ROLLBACK TRANSACTION;    -- doomed, must rollback
    ELSE IF XACT_STATE() = 1
        ROLLBACK TRANSACTION;    -- committable but we choose to rollback
    -- XACT_STATE() = 0: no transaction, nothing to rollback

    THROW;
END CATCH

-- Session-level (affects all subsequent queries in session)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;    -- default
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  -- no shared locks
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;          -- requires database option

-- Query-level via table hint (overrides session level for that table)
SELECT * FROM Orders WITH (NOLOCK);           -- READ UNCOMMITTED
SELECT * FROM Orders WITH (HOLDLOCK);         -- SERIALIZABLE
SELECT * FROM Orders WITH (UPDLOCK, ROWLOCK); -- UPDATE lock, row granularity

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- or equivalently:
SELECT * FROM LargeTable WITH (NOLOCK);

-- Monitor version store usage
SELECT
    DB_NAME(database_id)            AS db_name,
    reserved_page_count * 8.0 / 1024 AS reserved_mb,
    used_page_count * 8.0 / 1024   AS used_mb
FROM sys.dm_db_file_space_usage
WHERE database_id = 2;  -- tempdb

-- Current version store contents (aggregate)
SELECT
    transaction_sequence_num,
    version_sequence_num,
    database_id,
    rowset_guid,
    command_id,
    is_prepared,
    needs_rollback
FROM sys.dm_tran_version_store
ORDER BY transaction_sequence_num;  -- can be large; limit in prod

-- Active snapshot transactions holding version store open
SELECT
    t.transaction_id,
    t.transaction_sequence_num,
    t.commit_sequence_num,
    t.is_snapshot,
    s.session_id,
    s.open_transaction_count,
    s.last_request_start_time
FROM sys.dm_tran_active_snapshot_database_transactions t
JOIN sys.dm_exec_sessions s ON t.session_id = s.session_id;

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

-- Enable (requires exclusive access briefly)
-- Best run during maintenance window with no other connections
ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;

ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON;

-- Session opts into snapshot isolation
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
    -- Sees consistent snapshot from BEGIN TRANSACTION
    SELECT SUM(Amount) FROM Orders WHERE OrderDate >= '2024-01-01';
    -- ... more reads, all consistent to same point in time
COMMIT TRANSACTION;

Database
  └── Schema
        └── Table (TAB)
              └── Extent (EXT)
                    └── Page (PAG)
                          └── Key (KEY) — index rows
                          └── RID — heap row

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
    SELECT * FROM Orders WHERE CustomerID BETWEEN 100 AND 200;
    -- SQL Server holds a RangeS-S lock on the key range [100, 200]
    -- Any INSERT into this range by another transaction is blocked
    -- until this transaction commits or rolls back
COMMIT;

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

-- Change escalation mode
ALTER TABLE Orders SET (LOCK_ESCALATION = AUTO);    -- partition-first
ALTER TABLE Orders SET (LOCK_ESCALATION = DISABLE); -- never escalate
ALTER TABLE Orders SET (LOCK_ESCALATION = TABLE);   -- default behavior

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

-- Anti-deadlock: always use UPDLOCK when reading to update
BEGIN TRANSACTION;
    SELECT Balance FROM Accounts WITH (UPDLOCK, ROWLOCK)
    WHERE AccountID = 1;
    -- No other session can acquire UPDLOCK or X on this row
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
COMMIT;

-- Queue consumer: skip rows locked by other readers
SELECT TOP(1) *
FROM WorkQueue WITH (READPAST, ROWLOCK, UPDLOCK)
WHERE Processed = 0
ORDER BY QueuedAt;

-- Prevent phantom inserts during a check-then-insert pattern
BEGIN TRANSACTION;
    IF NOT EXISTS (SELECT 1 FROM Users WITH (UPDLOCK, HOLDLOCK) WHERE Email = 'a@b.com')
    BEGIN
        INSERT INTO Users (Email) VALUES ('a@b.com');
    END
COMMIT;
-- HOLDLOCK holds the range lock preventing concurrent inserts

-- Instead of: SELECT * FROM Orders WITH (NOLOCK)
-- Enable RCSI once:
ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT ON;
-- Then queries under default READ COMMITTED read from version store, no shared locks
SELECT * FROM Orders;  -- now reads without shared locks, sees only committed data

SET LOCK_TIMEOUT 5000;  -- wait max 5 seconds (milliseconds)
-- 0 = fail immediately if lock not available
-- -1 = wait indefinitely (default)

BEGIN TRY
    SELECT * FROM Orders WITH (UPDLOCK, ROWLOCK) WHERE OrderID = 1;
EXCEPT
-- Alternatively handle in CATCH:
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 1222  -- Lock request timeout
        PRINT 'Could not acquire lock within timeout';
    ELSE
        THROW;
END CATCH

Session A: X lock on Table1 row 1 → waiting for X lock on Table2 row 1
Session B: X lock on Table2 row 1 → waiting for X lock on Table1 row 1
→ Deadlock: SQL Server kills one session

-- Dangerous: Session A locks Orders then Accounts; Session B locks Accounts then Orders
-- Safe: Both sessions always lock in same order: Orders → Accounts
BEGIN TRANSACTION;
    UPDATE Orders SET Status = 'processing' WHERE OrderID = @id;
    UPDATE Accounts SET Balance = Balance - @amount WHERE AccountID = @acct;
COMMIT;

-- Deadlock-prone: Session A and B both acquire S lock, both try to promote to X
SELECT qty FROM Inventory WHERE ProductID = @pid;
UPDATE Inventory SET qty = qty - 1 WHERE ProductID = @pid;

-- Safe: Acquire U lock on read, convert to X on update
SELECT qty FROM Inventory WITH (UPDLOCK, ROWLOCK) WHERE ProductID = @pid;
UPDATE Inventory SET qty = qty - 1 WHERE ProductID = @pid;

-- Pre-compute values before beginning transaction
DECLARE @newTotal DECIMAL(18,2) = dbo.ComputeNewTotal(@orderID);

BEGIN TRANSACTION;
    UPDATE Orders SET Total = @newTotal WHERE OrderID = @orderID;
COMMIT;

-- T-SQL retry pattern in stored procedure
DECLARE @retries INT = 0;
retry:
BEGIN TRY
    BEGIN TRANSACTION;
    -- ... work ...
    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    IF ERROR_NUMBER() = 1205 AND @retries < 3
    BEGIN
        SET @retries += 1;
        WAITFOR DELAY '00:00:00.100';  -- 100ms backoff
        GOTO retry;
    END
    THROW;
END CATCH

-- System health session (always running) captures deadlocks automatically
-- Read deadlock graph from system_health XE session:
SELECT
    xdr.value('@timestamp', 'datetime2') AS DeadlockTime,
    xdr.query('.') AS DeadlockGraph
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 data
CROSS APPLY target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY DeadlockTime DESC;

-- Who is blocking whom (simple)
SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time / 1000.0    AS wait_seconds,
    r.status,
    SUBSTRING(t.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
          ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1
    ) AS current_statement,
    s.login_name,
    s.host_name,
    s.program_name
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;

-- Full blocking chain including the head blocker
WITH BlockingChain AS (
    SELECT
        session_id,
        blocking_session_id,
        wait_type,
        wait_time,
        CAST(session_id AS VARCHAR(MAX)) AS chain
    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,
        bc.chain + ' → ' + CAST(r.session_id AS VARCHAR(10))
    FROM sys.dm_exec_requests r
    JOIN BlockingChain bc ON r.session_id = bc.blocking_session_id
)
SELECT * FROM BlockingChain;

-- Active locks on a specific database
SELECT
    l.resource_type,
    l.resource_description,
    l.resource_associated_entity_id,
    l.request_mode,
    l.request_status,
    l.request_session_id,
    OBJECT_NAME(p.object_id)    AS object_name
FROM sys.dm_tran_locks l
LEFT JOIN sys.partitions p
    ON l.resource_associated_entity_id = p.hobt_id
WHERE l.resource_database_id = DB_ID()
ORDER BY l.request_session_id, l.resource_type;

-- Top wait types (snapshot since server restart)
SELECT TOP 20
    wait_type,
    wait_time_ms / 1000.0           AS wait_seconds,
    max_wait_time_ms / 1000.0       AS max_wait_seconds,
    waiting_tasks_count,
    CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS DECIMAL(5,2)) AS pct
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    -- Benign waits (incomplete list; use sp_BlitzFirst for full exclusion list)
    'SLEEP_TASK','SQLTRACE_BUFFER_FLUSH','WAITFOR','LAZYWRITER_SLEEP',
    'BROKER_TO_FLUSH','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE',
    'FT_IFTS_SCHEDULER_IDLE_WAIT','HADR_FILESTREAM_IOMGR_IOCOMPLETION',
    'HADR_WORK_QUEUE','HADR_CLUSAPI_CALL','HADR_TIMER_TASK',
    'REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE','SERVER_IDLE_CHECK',
    'SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MASTERDBREADY',
    'SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP',
    'SLEEP_SYSTEMTASK','SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT',
    'SP_SERVER_DIAGNOSTICS_SLEEP','SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
    'WAIT_XTP_OFFLINE_CKPT_NEW_LOG','XE_DISPATCHER_WAIT','XE_TIMER_EVENT',
    'BROKER_EVENTHANDLER','CHECKPOINT_QUEUE','DBMIRROR_EVENTS_QUEUE',
    'SQLTRACE_WAIT_ENTRIES','WAIT_XTP_CKPT_CLOSE'
)
ORDER BY wait_time_ms DESC;

-- Table with rowversion column
CREATE TABLE Products (
    ProductID   INT PRIMARY KEY,
    Name        NVARCHAR(100),
    Price       DECIMAL(10,2),
    RowVer      ROWVERSION  -- auto-updated on every modification
);

-- Application reads row and stores RowVer
-- Later, update only if no one else modified it:
UPDATE Products
SET Price = @newPrice
WHERE ProductID = @id
  AND RowVer = @capturedRowVer;   -- compare stored version

IF @@ROWCOUNT = 0
    THROW 50001, 'Record was modified by another user. Please refresh and retry.', 1;

BEGIN TRANSACTION;
    SELECT 1 FROM Configs WITH (UPDLOCK, HOLDLOCK)
    WHERE ConfigKey = @key;

    IF @@ROWCOUNT = 0
        INSERT INTO Configs (ConfigKey, ConfigValue) VALUES (@key, @value);
    ELSE
        UPDATE Configs SET ConfigValue = @value WHERE ConfigKey = @key;
COMMIT;

-- Consumer: dequeue one item at a time
DECLARE @id INT;
BEGIN TRANSACTION;
    SELECT TOP(1) @id = MessageID
    FROM MessageQueue WITH (UPDLOCK, ROWLOCK, READPAST)
    WHERE ProcessedAt IS NULL
    ORDER BY QueuedAt;

    IF @id IS NOT NULL
    BEGIN
        UPDATE MessageQueue SET ProcessedAt = SYSDATETIME()
        WHERE MessageID = @id;
        COMMIT;
        -- process message
    END
    ELSE
        ROLLBACK;

-- Enable once at database level:
ALTER DATABASE Reporting SET ALLOW_SNAPSHOT_ISOLATION ON;

-- Reporting session:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
    -- All reads see a consistent snapshot from this point
    SELECT * FROM Orders WHERE OrderDate >= '2024-01-01';
    SELECT * FROM OrderLines WHERE OrderDate >= '2024-01-01';
    -- Both queries see same committed state even if writers modify rows between them
COMMIT;