Transaction Isolation Levels & Anomalies

Snapshot reads solved blocking, but interleaved writes still corrupt the report

The storage engine ended with versioning doing half the concurrency job: each write creates a new row version stamped with its transaction identifier, readers open a snapshot from their start time, and long reports never block incoming edits. Snapshots stop blocking, but they do not stop wrong answers. One transfer still moves money while another reads balances for a report, and without rules the report observes state halfway through the transfer. A transaction, which is a group of reads and writes the database treats as one unit, rarely runs alone. Isolation, which is the set of guarantees about which interleavings of concurrent transactions are allowed, defines what the report may see. Weaker isolation permits more concurrency but exposes more anomalies, which are surprising observations such as seeing edits that later vanish.

Running transactions one at a time would avoid every anomaly and fail on throughput, because each transfer would queue behind the last: at 500 transfers a second with 20 milliseconds of work each, serial execution caps near 50 a second and the queue grows without bound. A tempting second fix is handling conflicts in application code with a distributed lock service instead of isolation levels, and it fails on multi-row predicates: two checkouts counting 99 qualifying promos each still both insert, because no single-row lock covers the count, and the 101st discount ships anyway. Think of a restaurant kitchen where two cooks share one ticket rail and need rules for when each may grab or rewrite a ticket: one analogy for the whole idea, where serial execution is one cook at a time and isolation levels are increasingly careful rules for sharing the rail.

T1: BEGIN; SELECT balance FROM accounts WHERE id=1; -- sees 100
T2: BEGIN; UPDATE accounts SET balance=0 WHERE id=1; COMMIT;
T1: SELECT balance FROM accounts WHERE id=1; -- sees 0? depends on isolation

Four anomalies show what weaker isolation lets slip through

Each anomaly names a concrete wrong observation. A dirty read sees another transaction's uncommitted edit that may later roll back, meaning the value never officially existed. A non-repeatable read sees one value for a row and a different value for the same row later in the same transaction because another transaction committed in between. A phantom read sees a set of rows matching a condition grow or shrink between two reads because another transaction inserted or deleted a matching row. Write skew and lost update share a shape where two transactions read the same state and each writes based on it, so a rule spanning rows breaks or one edit silently overwrites the other.

Isolation levels trade surprise elimination against waiting and retries

Each level forbids a larger set of interleavings at a larger operational cost. Read Uncommitted allows reading uncommitted edits and is rarely useful outside exotic scans. Read Committed, which shows only committed data with a fresh snapshot per statement and is the default in PostgreSQL, the leading open-source relational database, and Oracle, its commercial counterpart, removes dirty reads while leaving non-repeatable and phantom observations. Repeatable Read holds a snapshot from transaction start in PostgreSQL's snapshot implementation, removing non-repeatable reads while predicate phantoms can remain without extra locking. Snapshot Isolation adds write-write conflict detection so concurrent edits to the same row fail one writer, preventing lost updates while multi-row write skew remains. Serializable, which guarantees results equivalent to some serial order through locking or optimistic detection, removes all anomalies while charging blocking or aborts with retries.

Most traffic stays on the default while money paths opt into strength

Read Committed plus database constraints covers most online transaction traffic, because constraints such as uniqueness are checked atomically at commit even under weak isolation. The application enforces narrow invariants with check-then-insert guarded by a constraint, and reserves retries on serialization failure, which is error code 40001 meaning the transaction was aborted to preserve serial order, for critical sections only. Stronger isolation is scoped to narrow transactions: ledger moves and inventory decrements use Serializable or an explicit row lock, username claims rely on a unique constraint with duplicate-key retry, and reports needing one stable moment use a Repeatable Read snapshot for the whole export.

-- Serializable section with retry on 40001
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM shifts WHERE on_call=true;
-- application checks count >=1
DELETE FROM shifts WHERE id=$1;
COMMIT; -- may raise 40001 serialization_failure, then retry

A promo cap shows how phantoms spend real money on launch day

A promotion promises the first 100 buyers a discount. Two checkouts run concurrently under Read Committed, each counts 99 qualifying orders, each concludes budget remains, and each inserts its discount, paying out 101 discounts. Seat booking fails the same way when two transactions count one free seat and sell it twice. These bugs hide in staging with one user and appear on launch day as double charges, negative inventory, or overbooked capacity discovered through customer complaints rather than error logs, because each transaction was individually correct and only the interleaving was wrong.

Four checkout lines show which level each workload earns

Product browsing tolerates being stale by a row, so Read Committed maximizes throughput where a slightly old feed is invisible. A consistent export needs one moment frozen across many statements, so a Repeatable Read snapshot fits without paying serializable aborts. Decrementing inventory or moving money cannot tolerate double-spend, so Serializable or an explicit row lock is the honest cost. Claiming a coupon or username is a uniqueness race best settled by letting the constraint serialize contenders and retrying the loser on a duplicate-key error.

Abort rates stay low below a few percent contention on distinct rows, then climb fast on hot rows: a single popular counter under strict serializability can abort double-digit percentages of attempts. Keep serializable transactions tiny, touch few rows, and always code the retry loop rather than hoping contention stays low.

Locks bring deadlocks and timeouts that the retry budget must cover

Stronger isolation costs operational surface beyond throughput. Two transactions locking rows in opposite order wait on each other forever, which is a deadlock, and the database kills one after about a second by default. Application code must catch the deadlock and the serialization failure and retry the whole transaction with backoff and jitter, which is randomized delay that avoids synchronized retries, rather than crashing the request. A statement timeout measured in seconds plus a shorter lock timeout stops a forgotten transaction from holding locks past lunch, locks are acquired in a consistent row order when touching several rows, and attempt counters feed metrics so a rising abort rate alerts before users feel it. A single stuck transaction otherwise blocks background cleaning, bloats the table, and slowly degrades every query.

-- sane guardrails for stronger isolation
SET statement_timeout = '5s';
SET lock_timeout = '2s';
-- retry SQLSTATE 40001 (serialization) and 40P01 (deadlock)
-- with exponential backoff + jitter, max ~3-5 attempts

Version columns solve single-row races without serializing everything

Editing a profile or cart is a read-modify-write on one row that does not need full serializability. Optimistic locking, which adds a version integer read with the row and requires it to still match on write, handles it at Read Committed cost: the update affects one row when the version matches and zero rows when another transaction won, and the loser rereads and retries. One extra column buys race safety that most object mappers ship by default. The pattern breaks exactly where expected: multi-row predicates still need predicate protection, and blind increments under hot contention retry so often that an explicit lock or serializable transaction states the serialization honestly.

-- optimistic lock: no SERIALIZABLE needed
UPDATE accounts SET balance = $1, version = version + 1
WHERE id = $2 AND version = $3; -- 0 rows means someone won, re-read + retry

Serializable detection aborts while locking enforcement blocks

PostgreSQL Serializable uses snapshot isolation with dependency tracking: transactions run on snapshots while the engine records read-write dependencies, and when the dependency graph could cycle, meaning no serial order explains the observed results, it aborts one transaction with error 40001. Aborts are therefore the mechanism rather than a malfunction, which is why the retry loop is mandatory. MySQL InnoDB, which is the default storage backend of the widespread MySQL database, takes the opposite route with locking reads and gap locks that physically block new matching rows but serialize more aggressively. Either implementation proves the same point: serializability is enforced optimistically through abort and retry or pessimistically through block and wait, and conflicting concurrent transactions never both sail through.

Retries wrap whole transactions with bounded backoff, not single statements

Every serializable transaction needs the same epilogue: catch the serialization and deadlock errors, wait with exponential backoff plus jitter, and retry the entire transaction a bounded three to five times with tens to hundreds of milliseconds between attempts. Retrying only the failed statement reuses a poisoned snapshot that already observed the conflict, while retrying forever turns a hotspot into a livelock where contenders starve each other indefinitely. The attempt counter belongs in metrics so rising contention pages before checkout latency does.

let attempts = 0;
while (true) {
  try { return await runSerializableTxn(work); }
  catch (e) {
    if (!isRetryable(e) || ++attempts > 4) throw e;
    await sleep(rand(25 * 2 ** attempts)); // backoff + jitter
  }
}

Three rules keep isolation choices narrow and honest

Isolation keeps concurrent strangers honest on one machine by forbidding the interleavings that corrupt shared truth, with the retry loop on error 40001 as the price of serial order. Honesty gets harder when the truth itself lives on several machines: the primary dies mid-write, a replica lags seconds behind, and a promoted copy may never have received the last second of edits. Surviving the writer's death without losing acknowledged writes is the replication contract.