Day 16: Transactions & concurrency

A. Lesson overview

Activity breakdown

Business scenario. Ridgeway Outdoor moves stock between its Midlands and Scotland warehouses. A transfer must remove units from one warehouse and add them to the other — never one without the other. Two operators sometimes move the same product at the same moment, and the stock report occasionally shows impossible numbers or hangs. Today you find out why, and fix it.

B. Theory explained clearly

Part 1 — Transactions

B1. ACID

B2. Transaction modes

A single statement is always atomic: an UPDATE of 1,000 rows either changes all 1,000 or none. Explicit transactions make several statements atomic.

B3. @@TRANCOUNT and “nested” transactions

SQL Server has no true nested transactions: an “inner” COMMIT commits nothing, and any ROLLBACK undoes all levels. This matters when procedures that use transactions call each other (Days 17 and 20).

B4. Savepoints

SAVE TRANSACTION name; marks a point inside a transaction. ROLLBACK TRANSACTION name; undoes work back to that point without ending the transaction (@@TRANCOUNT is unchanged). You must still COMMIT or fully ROLLBACK afterwards. Savepoints are useful for “try this optional step; if it fails, carry on without it”.

Part 2 — XACT_ABORT

When a statement inside an explicit transaction fails, SQL Server’s default behaviour depends on the error:

• Many errors — constraint violations, conversion errors, divide by zero — terminate only the statement. The transaction stays open, the batch continues with the next statement, and a later COMMIT commits whatever succeeded.
• Some errors (e.g. deadlock victim, many conversion and arithmetic errors in certain contexts) abort the whole batch and roll the transaction back.

That means this transfer can create stock out of thin air:

BEGIN TRANSACTION;
UPDATE Lab.Stock SET QuantityOnHand = QuantityOnHand - 20 WHERE WarehouseID = 1 AND ProductID = 1;  -- fails: CHECK (>= 0)
UPDATE Lab.Stock SET QuantityOnHand = QuantityOnHand + 20 WHERE WarehouseID = 2 AND ProductID = 1;  -- succeeds
COMMIT TRANSACTION;                                                                                  -- commits half a transfer!

SET XACT_ABORT ON changes the rule: any run-time error rolls back the entire transaction and aborts the batch. Make it the first line of every multi-statement transaction you write — and of every stored procedure that modifies data. Day 20 adds TRY…CATCH for controlled error handling on top.

Part 3 — Locks and blocking

B5. Lock modes

Locks are taken at row (KEY/RID), PAGE or OBJECT level. If a statement takes very many row locks (roughly 5,000 on one object), SQL Server may escalate to a table lock to save memory.

Writers hold X locks until the transaction ends. Under the default isolation level (read committed, locking version), readers release S locks as soon as each row is read. So a long-running transaction that updated a row blocks every reader of that row until it commits or rolls back.

SQL Server 2025 note: optimized locking can reduce how long and how many locks writers hold. It is off by default in SQL Server 2025, requires accelerated database recovery, and is always on in Azure SQL Database. RetailLab uses the default, so today’s labs show classic locking.

B6. Seeing locks and blocking

-- Locks held by a session
SELECT  resource_type, request_mode, request_status, resource_description
FROM    sys.dm_tran_locks
WHERE   request_session_id = <session id>
  AND   resource_database_id = DB_ID();

-- Who is blocked, by whom, waiting on what
SELECT  r.session_id, r.blocking_session_id, r.wait_type, r.wait_time AS wait_ms, t.text AS running_sql
FROM    sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE   r.blocking_session_id <> 0;

@@SPID returns your own session ID; SSMS also shows it in the query tab title and status bar. Viewing other sessions’ requests needs the VIEW SERVER STATE permission (or VIEW SERVER PERFORMANCE STATE on SQL Server 2022+); your training login has it as sysadmin. At work, ask your DBA for these monitoring permissions rather than broad rights.

SET LOCK_TIMEOUT 5000; makes a session give up after 5 seconds of waiting (error 1222) instead of waiting forever.

Part 4 — Isolation levels

B7. Concurrency phenomena

B8. Isolation levels

Set it per session: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; — it stays in force for the session (or until the procedure ends) — or per table with hints: WITH (HOLDLOCK) = serializable, WITH (NOLOCK) = read uncommitted.

Row versioning. Two database options keep previous row versions in tempdb (or, with accelerated database recovery, in the user database):

• ALLOW_SNAPSHOT_ISOLATION ON lets sessions request SNAPSHOT explicitly.
• READ_COMMITTED_SNAPSHOT ON (RCSI) changes the default READ COMMITTED to read the last committed version instead of waiting for writers. Readers no longer block on writers, and writers no longer block on readers. It is off by default in SQL Server and on by default in Azure SQL Database. Turning it on needs exclusive access to the database.

Trade-offs: version storage and tempdb load; and under snapshot-based reading, a transaction may act on data that has since changed, so read-then-write logic still needs UPDLOCK or an atomic UPDATE.

About NOLOCK. WITH (NOLOCK) is not “faster reads with no downside”. It can return uncommitted data, skip rows, or read the same row twice while pages move. Don’t use it for anything whose numbers matter.

B9. Preventing lost updates

-- Vulnerable: read, compute, write
DECLARE @q int = (SELECT QuantityOnHand FROM Lab.Stock WHERE …);
UPDATE Lab.Stock SET QuantityOnHand = @q - 5 WHERE …;

-- Safe 1: one atomic statement
UPDATE Lab.Stock SET QuantityOnHand = QuantityOnHand - 5 WHERE … AND QuantityOnHand >= 5;

-- Safe 2: lock the row for update when reading it
BEGIN TRANSACTION;
DECLARE @q int = (SELECT QuantityOnHand FROM Lab.Stock WITH (UPDLOCK) WHERE …);
…
UPDATE …;
COMMIT;

UPDLOCK takes a U lock: other readers can still read (S is compatible), but a second session asking for UPDLOCK on the same row waits — so the two read-compute-write sequences run one after the other.

Part 5 — Deadlocks

A deadlock happens when two sessions each hold a lock the other needs: A holds row 1 and wants row 2; B holds row 2 and wants row 1. Neither can proceed. SQL Server’s deadlock monitor detects the cycle within seconds, chooses a victim (by default the one cheapest to roll back), rolls back its transaction and raises error 1205: Transaction (Process ID n) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Prevention and handling:

• Access tables and rows in the same order in all code (e.g. always the lower WarehouseID first).
• Keep transactions short; don’t wait for user input inside them.
• Use indexes so statements touch fewer rows (Day 23).
• Retry on 1205 in application code or with TRY…CATCH (Day 20).
• SET DEADLOCK_PRIORITY LOW volunteers a session as the preferred victim.
• The built-in system_health Extended Events session records deadlock graphs for later analysis.

C. Dataset and starting state

Setup (run once in any window; re-run to reset)

USE RetailLab;
GO

DROP TABLE IF EXISTS Lab.Stock;
SELECT WarehouseID, ProductID, QuantityOnHand, ReorderLevel
INTO   Lab.Stock
FROM   Inventory.Stock;

ALTER TABLE Lab.Stock ADD CONSTRAINT PK_LabStock PRIMARY KEY (WarehouseID, ProductID);
ALTER TABLE Lab.Stock ADD CONSTRAINT CK_LabStock_NonNegative CHECK (QuantityOnHand >= 0);
GO

SELECT WarehouseID, ProductID, QuantityOnHand
FROM   Lab.Stock
WHERE  ProductID IN (1, 2, 26)
ORDER BY ProductID, WarehouseID;

Expected: (1,1) 16, (2,1) 53, (1,2) 25, (2,2) 0, (1,26) 18, (2,26) 40.

Open the two sessions.

1. In SSMS, open a new query window and run USE RetailLab; SELECT @@SPID AS SessionA; — note the number. Rename the tab mentally as Session A.
2. Open a second window, run USE RetailLab; SELECT @@SPID AS SessionB; — Session B.
3. In both, confirm SELECT @@TRANCOUNT; returns 0 before each exercise.

Validation. Expected: 49, 1142, 0.

SELECT COUNT(*) AS StockRows, SUM(QuantityOnHand) AS TotalUnits, @@TRANCOUNT AS OpenTran FROM Lab.Stock;

TotalUnits = 1142 is the invariant for today: transfers move units, they never create or destroy them.

D. Worked examples

Worked example 1 — Nesting and savepoints (single session)

BEGIN TRANSACTION;                                      -- @@TRANCOUNT = 1
    UPDATE Lab.Stock SET QuantityOnHand = 17 WHERE WarehouseID = 1 AND ProductID = 1;
    BEGIN TRANSACTION;                                  -- 2
        UPDATE Lab.Stock SET QuantityOnHand = 26 WHERE WarehouseID = 1 AND ProductID = 2;
    COMMIT TRANSACTION;                                 -- 1  (nothing committed yet)
    SELECT @@TRANCOUNT AS AfterInnerCommit;
ROLLBACK TRANSACTION;                                   -- 0  (both updates undone)

SELECT @@TRANCOUNT AS AfterRollback, ProductID, QuantityOnHand
FROM   Lab.Stock WHERE WarehouseID = 1 AND ProductID IN (1, 2) ORDER BY ProductID;

BEGIN TRANSACTION;
    UPDATE Lab.Stock SET QuantityOnHand = 17 WHERE WarehouseID = 1 AND ProductID = 1;
    SAVE TRANSACTION BeforeOptionalStep;
    UPDATE Lab.Stock SET QuantityOnHand = 26 WHERE WarehouseID = 1 AND ProductID = 2;
    ROLLBACK TRANSACTION BeforeOptionalStep;            -- undo only the second update
    SELECT @@TRANCOUNT AS AfterSavepointRollback;       -- still 1
ROLLBACK TRANSACTION;                                   -- tidy up: undo the first too

Expected result: AfterInnerCommit = 1; then AfterRollback = 0 with quantities 16 and 25 (both updates undone despite the inner COMMIT); then AfterSavepointRollback = 1.

Worked example 2 — Half a transfer without XACT_ABORT

SET XACT_ABORT OFF;            -- the default, made explicit

BEGIN TRANSACTION;
UPDATE Lab.Stock SET QuantityOnHand = QuantityOnHand - 20 WHERE WarehouseID = 1 AND ProductID = 1;  -- 16 - 20 < 0
UPDATE Lab.Stock SET QuantityOnHand = QuantityOnHand + 20 WHERE WarehouseID = 2 AND ProductID = 1;
COMMIT TRANSACTION;

SELECT SUM(QuantityOnHand) AS TotalUnits FROM Lab.Stock;
SELECT WarehouseID, QuantityOnHand FROM Lab.Stock WHERE ProductID = 1 ORDER BY WarehouseID;

Expected result: the first UPDATE fails with error 547 (The UPDATE statement conflicted with the CHECK constraint “CK_LabStock_NonNegative”…); the second succeeds; the COMMIT commits it. TotalUnits is now 1162 — 20 units created from nothing — and product 1 shows 16 and 73.

Now re-run the setup in section C (to restore 1142) and run the same script with SET XACT_ABORT ON; as the first line. Expected: the error rolls back the whole transaction and the batch stops — the two SELECTs do not even run. Run them separately: total 1142, product 1 still 16 and 53.

Worked example 3 — Blocking, and how to see it (two sessions)

Expected result:

• A2 shows locks such as OBJECT IX, PAGE IX and KEY X — all GRANT.
• B1 waits.
• A3 returns one row: session_id = B’s ID, blocking_session_id = A’s ID, wait_type = LCK_M_S (waiting for a shared lock), and B’s query text.
• After A4, B1 returns 18 — the committed value. B never saw 99.

Worked example 4 — Dirty read (two sessions)

Expected result: B1 returns 0 immediately (no blocking) — a value that never officially existed. B2 returns 40. A report run at B1 would have flagged the Daypack as out of stock in Scotland. WITH (NOLOCK) on the table gives the same result as B1.

Worked example 5 — Deadlock (two sessions)

Expected result: one session (usually whichever SQL Server judges cheaper to roll back — either is possible) gets Msg 1205 … has been chosen as the deadlock victim. Rerun the transaction. Its transaction is already rolled back. The other session’s UPDATE then completes; roll it back. SUM(QuantityOnHand) is 1142 again.

Prevention: if both transfers updated warehouse 1 before warehouse 2 (a fixed order), B1 would have waited behind A1 instead of taking a lock A later needs. The guided lab writes transfers that way.

E. Guided practical lab — A safe stock transfer

Write a transfer script that is atomic, refuses impossible transfers, prevents lost updates and avoids deadlocks — then test it with two sessions.

Step 1 — Reset and record the invariant

1. Do: Re-run the setup in section C.
2. Run: SELECT SUM(QuantityOnHand) AS TotalUnits FROM Lab.Stock;
3. Observe: 1142.
4. Why it matters: A correct transfer never changes this number.
5. Confirm: 1142.

Step 2 — Write the transfer

1. Do: In Session A, write the script with variables, XACT_ABORT, a consistent lock order, UPDLOCK reads, a business check and atomic updates.
2. Run:

   SET XACT_ABORT ON;
   
   DECLARE @ProductID int = 26, @FromWarehouse int = 2, @ToWarehouse int = 1, @Qty int = 10;
   DECLARE @Available int;
   
   BEGIN TRANSACTION;
   
   -- Lock BOTH rows, always in WarehouseID order, before changing anything
   SELECT @Available = MAX(CASE WHEN WarehouseID = @FromWarehouse THEN QuantityOnHand END)
   FROM   Lab.Stock WITH (UPDLOCK, HOLDLOCK)
   WHERE  ProductID = @ProductID
     AND  WarehouseID IN (@FromWarehouse, @ToWarehouse);
   
   IF @Available IS NULL OR @Available < @Qty
   BEGIN
       ROLLBACK TRANSACTION;
       PRINT CONCAT('Transfer refused: only ', COALESCE(@Available, 0), ' available.');
   END
   ELSE
   BEGIN
       UPDATE Lab.Stock
       SET    QuantityOnHand = QuantityOnHand + CASE WHEN WarehouseID = @FromWarehouse THEN -@Qty ELSE @Qty END
       WHERE  ProductID = @ProductID
         AND  WarehouseID IN (@FromWarehouse, @ToWarehouse);
   
       IF @@ROWCOUNT <> 2
       BEGIN
           ROLLBACK TRANSACTION;
           PRINT 'Transfer refused: destination has no stock record.';
       END
       ELSE
       BEGIN
           COMMIT TRANSACTION;
           PRINT CONCAT('Transferred ', @Qty, ' units of product ', @ProductID, '.');
       END
   END;
   
   SELECT WarehouseID, QuantityOnHand FROM Lab.Stock WHERE ProductID = @ProductID ORDER BY WarehouseID;
   SELECT SUM(QuantityOnHand) AS TotalUnits, @@TRANCOUNT AS OpenTran FROM Lab.Stock;

3. Observe: Transferred 10 units of product 26.; warehouse 1 = 28, warehouse 2 = 30; total 1142; OpenTran 0.
4. Why it matters:
   • One UPDATE moves both quantities, so there is no window where only one side has changed.
   • The UPDLOCK, HOLDLOCK read locks both rows in one statement, so every transfer of this product queues on the same locks — no deadlock between opposite-direction transfers, no lost update.
   • IF … BEGIN … END and PRINT are control-of-flow statements; Day 17 covers them properly.
5. Confirm: total 1142.

Step 3 — Try an impossible transfer

1. Do: Change the variables to @ProductID = 2, @FromWarehouse = 2, @ToWarehouse = 1, @Qty = 5 and run again.
2. Run: the same script.
3. Observe: Transfer refused: only 0 available. Quantities unchanged (25 and 0).
4. Why it matters: The business rule is checked before anything changes; the CHECK constraint remains as a final safety net.
5. Confirm: total 1142.

Step 4 — Two operators at once

1. Do: Run the transfer in both sessions with a pause inserted, so they overlap.

2. Run:

   Step	Session	Action
   A1	A	Set @ProductID = 1, @FromWarehouse = 2, @ToWarehouse = 1, @Qty = 30. Insert WAITFOR DELAY '00:00:10'; on the line after the UPDLOCK SELECT. Run.
   B1	B	Within 10 seconds, run the original script with @ProductID = 1, @FromWarehouse = 2, @ToWarehouse = 1, @Qty = 30. It waits.
   —	—	A finishes after 10 seconds; B then continues

3. Observe: A transfers 30 (warehouse 2: 53 → 23). B, having waited, reads 23 and is refused (only 23 available). Warehouse 1 = 46, warehouse 2 = 23. Total 1142.

4. Why it matters: Without UPDLOCK, both sessions could have read 53, both decided “enough stock”, and the second update would have failed the CHECK constraint — or, with a less strict design, oversold.

5. Confirm: total 1142; @@TRANCOUNT 0 in both sessions.

F. Independent practice

Reset Lab.Stock (section C) before each task.

Task 1 — Savepoints

In one transaction: add 5 units to (1, 2); set a savepoint; set (2, 2) to 100; roll back to the savepoint; commit. Show product 2’s quantities.

Success criteria: warehouse 1 = 30, warehouse 2 = 0; @@TRANCOUNT 0; total 1147 (5 units added deliberately).

Task 2 — Non-repeatable read, then prevent it

Using two sessions:

1. Session A (default isolation) starts a transaction and reads (1, 26).
2. Session B updates (1, 26) to 50 (autocommit).
3. Session A reads (1, 26) again, then commits.

Then repeat with Session A at REPEATABLE READ.

Success criteria: first run: A reads 18, then 50. Second run: B waits until A commits; A reads 18 twice; then B’s update completes.

Task 3 — Lost update, then fix it

Using two sessions, both run: read QuantityOnHand for (2, 26) into a variable, WAITFOR DELAY '00:00:05', then UPDATE … SET QuantityOnHand = @q - 5. Start them within a few seconds of each other. Then fix it two ways: (a) with WITH (UPDLOCK) on the read inside a transaction; (b) with a single atomic UPDATE.

Success criteria: unfixed: the final value is 35, not 30 (one update lost). Fixed: 30.

Task 4 — Phantoms and SERIALIZABLE

Session A, in a transaction, counts rows in warehouse 2 twice; between the counts, Session B inserts (2, 3, 10, 5). Run once with REPEATABLE READ and once with SERIALIZABLE in Session A.

Success criteria: REPEATABLE READ: A counts 20, then 21 (phantom). SERIALIZABLE: B waits until A commits; A counts 20 twice. Remove the inserted row afterwards (or reset).

Stretch challenge — Snapshot isolation and update conflicts

Enable ALLOW_SNAPSHOT_ISOLATION on RetailLab. Show that a SNAPSHOT transaction in Session A keeps seeing 40 for (2, 26) after Session B updates and commits 45 — and that A then fails when it tries to update the same row.

Success criteria: A reads 40, B commits 45, A still reads 40; A’s UPDATE fails with error 3960 (Snapshot isolation transaction aborted due to update conflict…) and its transaction is rolled back. Turn the option off afterwards.

G. Solutions and explanations

SET XACT_ABORT ON;
BEGIN TRANSACTION;
    UPDATE Lab.Stock SET QuantityOnHand = QuantityOnHand + 5 WHERE WarehouseID = 1 AND ProductID = 2;
    SAVE TRANSACTION AfterReceipt;
    UPDATE Lab.Stock SET QuantityOnHand = 100 WHERE WarehouseID = 2 AND ProductID = 2;
    ROLLBACK TRANSACTION AfterReceipt;
COMMIT TRANSACTION;

SELECT WarehouseID, QuantityOnHand FROM Lab.Stock WHERE ProductID = 2 ORDER BY WarehouseID;
SELECT @@TRANCOUNT AS OpenTran, SUM(QuantityOnHand) AS TotalUnits FROM Lab.Stock;

DECLARE @q int = (SELECT QuantityOnHand FROM Lab.Stock WHERE WarehouseID = 2 AND ProductID = 26);
WAITFOR DELAY '00:00:05';
UPDATE Lab.Stock SET QuantityOnHand = @q - 5 WHERE WarehouseID = 2 AND ProductID = 26;

SET XACT_ABORT ON;
BEGIN TRANSACTION;
DECLARE @q int = (SELECT QuantityOnHand FROM Lab.Stock WITH (UPDLOCK) WHERE WarehouseID = 2 AND ProductID = 26);
WAITFOR DELAY '00:00:05';
UPDATE Lab.Stock SET QuantityOnHand = @q - 5 WHERE WarehouseID = 2 AND ProductID = 26;
COMMIT TRANSACTION;

UPDATE Lab.Stock SET QuantityOnHand = QuantityOnHand - 5
WHERE  WarehouseID = 2 AND ProductID = 26 AND QuantityOnHand >= 5;

ALTER DATABASE RetailLab SET ALLOW_SNAPSHOT_ISOLATION ON;

ALTER DATABASE RetailLab SET ALLOW_SNAPSHOT_ISOLATION OFF;

H. Knowledge check

1. Understanding. After BEGIN TRAN; BEGIN TRAN; COMMIT;, what is @@TRANCOUNT, and has anything been committed?

2. Debugging. A two-statement transfer inside BEGIN TRAN … COMMIT sometimes leaves stock created from nothing. The first statement occasionally fails a CHECK constraint. What is happening, and what is the one-line fix?

3. Query interpretation. Session A holds an uncommitted update on a row. Session B runs SELECT … WITH (NOLOCK) on that row. What does B see, and is it reliable?

4. Understanding. Which isolation level prevents phantoms by locking, and what is its main cost?

5. Debugging. Two procedures update the same two tables in opposite orders and occasionally fail with error 1205. What is the error, and how do you prevent it?

I. Troubleshooting and cleanup

Cleanup. In both sessions run SELECT @@TRANCOUNT; — if not 0, run ROLLBACK TRANSACTION;. Then, in one session:

ALTER DATABASE RetailLab SET ALLOW_SNAPSHOT_ISOLATION OFF;   -- if you did the stretch
DROP TABLE IF EXISTS Lab.Stock;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Close Session B. Core data is unchanged; keep the Day 12 objects.

J. Completion checklist, recap and references

Completion checklist

• Worked example 2 showed 1162 without XACT_ABORT and 1142 with it.
• You saw LCK_M_S blocking in worked example 3 and a 1205 deadlock in worked example 5.
• Your transfer script kept the total at 1142 and refused the impossible transfer.
• Tasks 2–4 reproduced a non-repeatable read, a lost update and a phantom — and their fixes.
• Both sessions end with @@TRANCOUNT = 0.

Recap

• Transactions give atomicity; only the outermost COMMIT commits; any ROLLBACK undoes everything; savepoints undo part of a transaction.
• Errors don’t automatically roll back a transaction: use SET XACT_ABORT ON.
• Writers hold X locks until commit; under locking read committed, readers wait for them. Diagnose with sys.dm_tran_locks and sys.dm_exec_requests.
• Isolation levels trade consistency for concurrency: READ UNCOMMITTED (dirty reads) → READ COMMITTED → REPEATABLE READ → SERIALIZABLE; SNAPSHOT/RCSI use row versions instead of read locks.
• Prevent lost updates with atomic UPDATEs or UPDLOCK; prevent deadlocks with a consistent access order and short transactions; retry on 1205 and 3960.

Next: Day 17 — Stored procedures: parameters, return values, output parameters, multiple result sets and dynamic SQL inside procedures.

References (Microsoft Learn)

• Transactions (Transact-SQL)
• SET XACT_ABORT (Transact-SQL)
• SAVE TRANSACTION (Transact-SQL)
• Transaction locking and row versioning guide
• SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
• sys.dm_tran_locks
• sys.dm_exec_requests
• Deadlocks guide
• Optimized locking
• Table hints (Transact-SQL) — UPDLOCK, HOLDLOCK, NOLOCK