Transactions and Locking

Analogy categoryAnalogies

Transactions and the ACID Guarantee

A transaction is a group of one or more statements bounded by BEGIN TRANSACTION and either COMMIT or ROLLBACK, executed as a single atomic unit — either every statement's effect is permanently applied, or none is, even if the server crashes mid-transaction. This atomicity, combined with consistency, isolation, and durability (ACID), is what lets a banking transfer that debits one account and credits another be safe: if the credit fails after the debit succeeded, ROLLBACK undoes the debit too, so money is never lost mid-operation.

🏏

Cricket analogy: A cricket run-out review is atomic like a transaction: the third umpire either confirms both the dismissal AND updates the scorecard together, or overturns it and nothing changes — there's no state where the batter is half out.

sql

BEGIN TRANSACTION;

UPDATE dbo.Accounts SET Balance = Balance - 500 WHERE AccountId = 101;

IF @@ERROR <> 0
BEGIN
    ROLLBACK TRANSACTION;
    RETURN;
END

UPDATE dbo.Accounts SET Balance = Balance + 500 WHERE AccountId = 202;

IF @@ERROR <> 0
BEGIN
    ROLLBACK TRANSACTION;
    RETURN;
END

COMMIT TRANSACTION;

Locks: Shared, Exclusive, and Update

SQL Server enforces isolation by acquiring locks at row, page, or table granularity as statements execute: a shared (S) lock is taken for reads and allows other readers to hold shared locks concurrently, an exclusive (X) lock is taken for writes and blocks all other access, and an update (U) lock is used while SQL Server is deciding whether a row will be modified, preventing the classic conversion deadlock that would occur if two readers both tried to upgrade their own shared locks to exclusive at the same time. Under the default READ COMMITTED isolation level, shared locks are released as soon as the read completes rather than held until the transaction ends, unlike exclusive locks, which are always held until COMMIT or ROLLBACK.

🏏

Cricket analogy: A shared lock is like several commentators simultaneously reading the same live scorecard feed — many can view at once — while an exclusive lock is like the official scorer being the only one allowed to actually edit the scorebook at that instant.

Isolation Levels and Deadlocks

Isolation levels control how much one transaction is shielded from concurrent changes by others: READ UNCOMMITTED allows dirty reads of uncommitted data, READ COMMITTED (the default) prevents dirty reads but allows non-repeatable reads and phantom rows, REPEATABLE READ holds shared locks until the transaction ends to prevent changes to already-read rows, and SERIALIZABLE additionally locks ranges to prevent phantom inserts, while SNAPSHOT ISOLATION uses row versioning instead of locks so readers never block writers. A deadlock occurs when two transactions each hold a lock the other needs and neither can proceed; SQL Server's lock monitor detects the cycle and kills one transaction as the deadlock victim (typically the one that is cheapest to roll back), returning error 1205 to that session while letting the other proceed.

🏏

Cricket analogy: READ UNCOMMITTED is like reporting a wicket before the third umpire confirms it on review — you might be reporting something that gets overturned, a dirty read of unconfirmed data.

You can inspect current blocking and lock waits with sys.dm_tran_locks and sys.dm_os_waiting_tasks, and capture deadlock graphs automatically via Extended Events (the system_health session captures deadlocks by default) without needing to run trace flags manually.

SERIALIZABLE and REPEATABLE READ hold locks for the duration of the transaction, which can dramatically increase blocking under concurrent load. Prefer READ COMMITTED SNAPSHOT (row versioning) when you need consistent reads without blocking writers, but be aware it increases tempdb usage for the version store.

A transaction bounded by BEGIN TRANSACTION/COMMIT/ROLLBACK is atomic — all its statements succeed together or none do.
Shared (S) locks allow concurrent readers; exclusive (X) locks block all other access; update (U) locks prevent conversion deadlocks.
Under READ COMMITTED (the default), shared locks are released as soon as the read completes, not held until COMMIT.
Isolation levels trade concurrency for consistency: READ UNCOMMITTED is loosest, SERIALIZABLE is strictest.
SNAPSHOT ISOLATION uses row versioning so readers don't block writers and vice versa, at the cost of tempdb space.
A deadlock happens when two transactions each hold a lock the other needs; SQL Server kills one as the deadlock victim.
Extended Events' system_health session captures deadlock graphs automatically for later diagnosis.