Isolation Levels: The Mechanism Behind Each

The practical lesson Isolation Levels describes the levels by what they let you see. Inside the engine there is a single tuple-visibility function (from MVCC Internals: Version Chains and Snapshots) and a single place where a writer discovers that its target has moved; the levels are configurations of those two points. The first knob is snapshot age: Read Committed calls for a new snapshot at the start of every statement, Repeatable Read and Serializable take one at the transaction's first statement and reuse it. That single difference produces the whole non-repeatable-read and phantom distinction, because a snapshot that never changes cannot see a row change or appear.

The second knob is what a writer does when the row moved. An UPDATE locates a tuple through its snapshot, tries to lock it, and finds xmax already set by a concurrent transaction. It waits for that transaction to end. If the other aborted, the update proceeds on the tuple as found. If the other committed, there is a newer version the snapshot cannot see — and here the levels diverge.

At Read Committed each statement begins with GetTransactionSnapshot(), so each statement sees everything committed before *it* started. Between two statements of one transaction the world may change; that is the level's definition, not a defect. The interesting behaviour is in UPDATE and DELETE. The statement found tuple version V1 through its snapshot and wants to modify it; but V1's xmax says T1 superseded it and T1 has since committed. Updating V1 would be a lost update. Aborting would make Read Committed useless for counters. So PostgreSQL follows V1's t_ctid chain to the newest committed version V2, re-evaluates the statement's WHERE clause against V2 — the EvalPlanQual re-check — and if it still qualifies, updates V2 instead. The statement effectively sees one row from the future.

This is why UPDATE accounts SET balance = balance - 10 is safe at Read Committed while SELECT balance followed by UPDATE … SET balance = 90 is not: the first re-reads balance from V2 during the chase; the second computed 90 from V1 in the application and the engine has no way to know. It is also why a Read Committed UPDATE can update a row that no longer matches the snapshot it started with, and skip one that the chase found no longer qualifying.

One snapshot, taken at the first statement, for the life of the transaction. Every read repeats; no phantom can appear because an inserted row's xmin is either in the in-progress list or above snap.xmax. The transaction is reading a frozen world, and it costs nothing extra to read it — no locks, no marks. This is snapshot isolation, and both PostgreSQL and InnoDB implement Repeatable Read this way (which is why both prevent phantoms in plain reads, stronger than the SQL standard requires).

Writers under a frozen snapshot have a problem the chase cannot solve: following the chain to a newer version means writing on the basis of data the transaction is not allowed to see. The rule is first-updater-wins (equivalently, first-committer-wins): a transaction that tries to update a row already updated by a concurrent transaction waits for it; if the other commits, the waiter aborts with "could not serialize access due to concurrent update"; if the other aborts, the waiter proceeds. This is exactly what turns the lost-update schedule into an error rather than a wrong answer, and it needs no extra structure — it is the xmax check the writer was already doing, with "abort" in place of "chase". Its blind spot is any conflict with no write–write component: write skew.

PostgreSQL's Serializable is serializable snapshot isolation (Cahill, Röhm and Fekete 2008; in PostgreSQL since 9.1). It starts as Repeatable Read and adds bookkeeping. Every read records an SIREAD lock on what was read — a tuple, or an index page or the whole relation when the read was a range or a scan — in a shared-memory predicate lock table separate from the ordinary lock manager. SIREAD locks conflict with nothing; they are memory, not blocking. When a transaction *writes* a tuple, the engine checks whether a concurrent transaction holds an SIREAD lock covering it and, if so, records a rw-dependency edge from the reader to the writer: "the reader saw a version the writer replaced". Edges are also found in the other direction, when a read encounters a tuple written by a concurrent transaction.

The theory says every non-serializable execution under snapshot isolation contains a transaction with an rw-edge *in* and an rw-edge *out*, both to transactions concurrent with it, where the transaction at the end of the out-edge commits first. That is the dangerous structure. PostgreSQL checks for it at each new edge and at commit, and when it finds one it aborts a transaction in it with SQLSTATE 40001 — preferring the one that has not committed yet, and never one that has. The check is conservative: it does not verify that a full cycle exists, so some transactions abort that would have been fine (false positives), particularly when many tuple-level SIREAD locks are promoted to page or relation locks to fit max_pred_locks_per_transaction. What it never does is wait. A Serializable transaction in PostgreSQL blocks in exactly the places a Repeatable Read one does, and pays for its guarantee in retries.

That is the answer to why Serializable "costs retries rather than blocking" here: the mechanism is detection over snapshots, not prevention with locks. The application must be built for 40001 as normal operation — the practical lesson shows the loop — and hot spots with many overlapping reads and writes will see abort rates that make the level unusable, which is when a deliberate SELECT … FOR UPDATE on a single representative row is the right tool instead.

InnoDB takes the other road. Its Repeatable Read is snapshot isolation for plain SELECTs (a read view fixed at the first read) but its locking reads and writes already use next-key locks, so SELECT … FOR UPDATE over a range blocks inserts into that range and phantoms cannot appear in locked ranges. Setting the level to SERIALIZABLE changes one thing: every plain SELECT is executed as SELECT … FOR SHARE (unless autocommit is on and the statement is a lone read). Reads now take shared next-key locks held to commit; a concurrent write to anything a transaction read must wait for it. This is strict two-phase locking, and the theorem from Concurrency Control: Schedules and Serializability guarantees serializability.

The write-skew example blocks instead of aborting: TA's SELECT locks both doctor rows in shared mode, TB's SELECT does too, TA's UPDATE of alice needs an exclusive lock and waits for TB's shared lock, TB's UPDATE of bob waits for TA's — a deadlock, which the detector breaks by aborting one. Same outcome as SSI in the end, reached by blocking rather than by tracking, and with the costs of blocking: readers stall writers, throughput under contention falls, and a long Serializable report holds shared locks on everything it read. Read Committed in InnoDB, for symmetry, uses a read view per statement and drops gap locks for ordinary statements.

The matrix from the practical lesson said *whether* each level prevents each anomaly. This one says *what* prevents it. Read it column by column: the same anomaly is stopped by a different part of the engine at each level, and where two engines differ in mechanism, they differ in cost.

Snapshot-per-statement is almost free: a walk of the running list per statement. Snapshot-per-transaction costs nothing to take and something to keep — every version created after it is pinned until it ends, which is the horizon problem from UPDATE, DELETE and Dead Tuples. First-updater-wins costs aborts on write–write conflicts, which is the same work a lock wait would have spent, spent differently. SSI costs memory for predicate locks, CPU at every read and write for the edge checks, and aborts including false positives; it never adds a wait. Locking reads cost waits and deadlocks and never add an abort of their own.

Choose by workload shape, not by name. Many short conflicting transactions: locking reads or explicit FOR UPDATE, because retries would be the workload. Many long reads with occasional writes: SSI, because reads must not block. And read the other engine's documentation before assuming its Serializable means the same thing — Oracle's is snapshot isolation and permits write skew.