SQL Isolation Levels Visualizer: four levels, four anomalies.

SQL isolation levels are a contract: which kinds of concurrency anomaly will the database prevent, and at what performance cost? The four levels in SQL-92 — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE — form a ladder from lax to strict. Hal Berenson and colleagues critiqued the standard in a 1995 paper; the gap between what "serializable" means in the standard and what databases actually ship is still real today.

Imagine a bank account with $100. Alice transfers $30 to Bob. The transaction reads the source balance, subtracts $30, writes back $70. Same time, in a different process, you run a daily “sum all balances” report. The report's transaction reads Alice's account at $100 (before her write commits) and reads Bob's account at $50 (after his receive commits). The total looks like the bank has $150, when actually it has $120. A dollar appeared from nowhere because two transactions interleaved their reads and writes in a way that left the report reading from inconsistent snapshots.

The first instinct — “just lock everything until I'm done” — works correctness-wise but kills performance. If every report blocks every write, your daily report takes hours and your bank can't process transfers. The opposite instinct — “let everyone read whatever, deal with anomalies in code” — leaves silent corruption everywhere. The right answer is in the middle: pick a precise rule for what each transaction is allowed to see, and let the database enforce it.

This is the problem isolation levels solve. Each level is a contract: which kinds of weirdness will the database prevent, and at what performance cost? The four named levels in SQL-92 — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE — form a ladder from lax to strict. Read Uncommitted lets you see another transaction's uncommitted writes (a dirty read). Read Committed only shows committed values, but the same query run twice in one transaction can return different rows (a non-repeatable read). Repeatable Read freezes a snapshot for the whole transaction but a range query like “count active users” can still see new rows appear (a phantom read). Serializable forbids all of the above.

Concrete numbers help. Postgres at SERIALIZABLE costs about 10–20% throughput on typical workloads compared to Read Committed; on contention-heavy ones the cost climbs to 40–60% because of retry. MySQL at SERIALIZABLE collapses harder because it falls back to two-phase locking and pays for every read with a lock. Most real applications run at Read Committed by default and reach for higher levels surgically — for the small set of transactions whose invariants the application can't reliably enforce in code (financial transfers, inventory decrements, on-call rotas).

The simulator above lets you watch two transactions interleave on the same row at each level. The anomaly that shows up at Read Committed disappears at Repeatable Read; the one that shows up at Repeatable Read (write skew) disappears only at Serializable. Each level has a name. Each name is a promise. The promises are the entire point — and the part the SQL standard accidentally muddied for thirty years, as Part 02 explains.

The four named isolation levels we still use — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE — were codified in the ANSI SQL-92 standard, formally ISO/IEC 9075:1992. The standard described each level by which "phenomena" (anomalies) it forbade, with three named phenomena: dirty read, non-repeatable read, and phantom read. The level called serializable forbade all three; read uncommitted forbade none. Two anomalies actually known in the literature — lost update and write skew — weren't mentioned. The whole specification of isolation took less than a page of the multi-thousand-page standard, which already hinted at the trouble to come.

Three years later, Hal Berenson, Phil Bernstein, Jim Gray, Jim Melton, Elizabeth O'Neil and Patrick O'Neil shredded the definition in A Critique of ANSI SQL Isolation Levels, published as Microsoft technical report MSR-TR-95-51 and later in SIGMOD 1995. They showed the standard was ambiguous in two damaging ways. First, the phenomena had two interpretations — "broad" (a behaviour that could happen) and "strict" (a behaviour that did happen) — and most engines had implemented the strict reading, which let through anomalies the standard's drafters thought were ruled out. Second, multiversion concurrency control engines like Oracle (Read Consistency mode, shipped in version 7, 1992) provided a level of correctness incomparable with the standard's lattice: they prevented some anomalies the standard forbade but allowed write skew, which the standard implicitly assumed Serializable would block. The paper coined snapshot isolation as the name for what Oracle and Postgres actually provided, and called write skew "A5B" in the formal taxonomy.

Berenson's paper has been taught in every database systems class for thirty years because it's the moment the field admitted that "I run at REPEATABLE READ" doesn't mean the same thing on every database. Postgres's "Repeatable Read" is snapshot isolation; MySQL's "Repeatable Read" is snapshot isolation with extra gap locks; SQL Server's "Repeatable Read" is two-phase-locking with shared read locks; SQLite's "Serializable" is "one writer at a time, all the time". The names became labels on dramatically different mechanisms.

The lineage of correctness theory is older still. Eswaran, Gray, Lorie & Traiger's 1976 paper The Notions of Consistency and Predicate Locks in a Database System introduced two-phase locking and predicate locks. Bernstein, Hadzilacos & Goodman's 1987 textbook Concurrency Control and Recovery in Database Systems formalised serialisability via the multiversion theorem. Adya, Liskov & O'Neil's 2000 paper Generalized Isolation Level Definitions replaced the standard's phenomenon-list with a precise vocabulary of dependency graphs and forbidden cycles. By the time CockroachDB and YugabyteDB were designed in the 2010s, the rigorous vocabulary was already in place; the SQL-92 names persist mostly as a legacy compatibility surface for tools that grew up on Oracle and DB2.

The SQL standard names four phenomena. Dirty read: T1 sees an uncommitted write of T2; if T2 rolls back, T1 has acted on a value that never existed. Non-repeatable read: T1 reads the same row twice in one transaction and gets different values because T2 committed in between. Phantom read: T1 runs the same range query twice and gets a different row count because T2 inserted or deleted a matching row. Lost update: two transactions both read X, both write X based on what they read; one wins and the other's update vanishes silently.

The standard layers these into four levels. Read Uncommitted permits everything; Read Committed forbids dirty reads only; Repeatable Read forbids dirty and non-repeatable; Serializable forbids all three named phenomena. Real engines implement stronger guarantees than the standard requires — Postgres's Serializable actually IS serializable in the technical sense (any concurrent execution is equivalent to some serial order), achieved by the SSI algorithm; many engines' "Repeatable Read" is closer to snapshot isolation than to the standard's definition.

The hidden fifth phenomenon is write skew, which the SQL standard never named. Two transactions each read a shared dataset, each modifies a disjoint subset, and the combined result violates an invariant that held in either single-transaction execution. Snapshot isolation lets it through; only true serialisability blocks it. Berenson's 1995 paper named it A5B; Adya, Liskov, and O'Neil's 2000 paper Generalized Isolation Level Definitions formalised an entire family of related anomalies (G0, G1a, G1b, G1c, G-single, G2-item, G2) using directed serialisation graphs, replacing the SQL-92 phenomenon-list with a precise mathematical foundation. Modern databases — CockroachDB, FoundationDB, Spanner — describe their isolation in Adya's vocabulary rather than the SQL standard's.

Most modern relational engines — Postgres, Oracle, MySQL InnoDB, SQL Server (with READ_COMMITTED_SNAPSHOT on), CockroachDB, YugabyteDB — implement isolation via Multiversion Concurrency Control (MVCC). The foundational treatment is Phil Bernstein and Nathan Goodman's 1981 ACM Computing Surveys paper Concurrency Control in Distributed Database Systems, with the multiversion theorem coming from Bernstein, Hadzilacos & Goodman's 1987 textbook Concurrency Control and Recovery in Database Systems (Addison-Wesley). The trick is that every row update creates a new version; old versions stay around long enough to serve readers who started before the writer committed. Readers never block writers, writers never block readers — a property that two-phase-locking schemes can't match.

Postgres tags each row with two transaction IDs: xmin (the txn that created this version) and xmax (the txn that deleted or updated it). A transaction reading at snapshot S sees a row if its xmin is ≤ S, was committed by S, and either xmax is null or xmax > S or wasn't committed by S. The garbage collector (vacuum) reclaims dead row versions once no active transaction can still see them. The downside: long-running transactions hold back vacuum and bloat the heap; the famous Postgres "transaction ID wraparound" outage at Sentry (2015) and Mailchimp (2019) traces back to this mechanism. RFC-style technical debt: the xmin/xmax are 32-bit integers; once they wrap around 2 billion, the database freezes operations until a manual vacuum can recycle them.

Oracle's implementation uses undo segments: every change writes the old value to a circular undo log; readers walk the log backwards to reconstruct the snapshot. The notorious ORA-01555: snapshot too old error happens when a long-running query needs an old version that the undo log already overwrote. MySQL InnoDB uses similar undo logs in the rollback segment. The mechanisms differ in detail, the contract is the same: a SELECT in a transaction sees the database state as of the snapshot, regardless of concurrent writes.

The classic write-skew example is the on-call rota: at least one doctor must be on call. Doctors Alice and Bob are both on call. Both run a transaction that reads the rota, sees two doctors active, and removes itself. Under snapshot isolation both transactions see the same starting state, neither modifies what the other read, both commit. Now nobody is on call. The invariant holds individually for each transaction; it fails when both run concurrently.

For two decades after snapshot isolation entered Oracle (1992) and Postgres 7.1 (2001), the only ways to prevent write skew were explicit row locks (SELECT ... FOR UPDATE escalates the read into a write-lock acquisition), schema-level constraints (a CHECK constraint that the row count is positive), or bumping to two-phase-locking serialisable, which crippled throughput. Michael Cahill, Uwe Röhm and Alan Fekete changed that with Serializable Isolation for Snapshot Databases (SIGMOD 2008, expanded in Cahill's 2009 PhD thesis). Their algorithm, Serializable Snapshot Isolation (SSI), tracks read-write dependencies between transactions; when a "dangerous structure" (two consecutive rw-edges) appears in the dependency graph, one transaction is aborted and asked to retry. SSI guarantees full serialisability with the throughput of snapshot isolation in the no-conflict case — you only pay when transactions actually conflict.

Postgres shipped SSI in 9.1 (September 2011) under the SERIALIZABLE isolation level. Dan Ports's Serializable Snapshot Isolation in PostgreSQL (VLDB 2012) describes the implementation in detail; it adds about 10–20% overhead in benchmarks (TPC-C, TPC-E) compared to snapshot isolation, with the overhead concentrated in the read-tracking data structures. CockroachDB inherits SSI as its default level. Google's Spanner takes a different route: external consistency via TrueTime and Paxos-replicated commit-wait, described in Corbett et al's OSDI 2012 paper. Spanner serialises by physical timestamp using globally synchronised clocks (within a few milliseconds, via GPS and atomic clocks in every datacentre); the cost is a per-commit wait of one clock-uncertainty interval, but the guarantee is stronger than serialisability — any external observer sees a single global order of transactions.

The names on the menu of SET TRANSACTION ISOLATION LEVEL are deceptively uniform. The mechanisms behind them are not.

Postgres defaults to Read Committed. Each statement sees a fresh snapshot taken at statement start; write conflicts are detected with row-level locks acquired on UPDATE/DELETE. Setting Repeatable Read gives snapshot isolation: one snapshot for the whole transaction. Serializable adds SSI on top — same snapshot, plus rw-conflict tracking, plus retryable serialisation failures. Postgres's SERIALIZABLE truly is serialisable in the formal sense.

MySQL InnoDB defaults to Repeatable Read. Implementation: snapshot isolation for non-locking SELECTs, plus gap locks on range scans to prevent phantom inserts. The gap-lock trick gives MySQL phantom-free Repeatable Read without the cost of full SSI; it's a clever middle ground that Postgres's lock-free design declined to follow. SERIALIZABLE in InnoDB upgrades every SELECT to SELECT ... LOCK IN SHARE MODE, falling back to two-phase-locking with shared read locks — which causes the throughput cliff that drives apps back to Repeatable Read.

Oracle defaults to Read Committed with statement-level snapshots. SERIALIZABLE in Oracle is snapshot isolation; Oracle does not implement true serialisability via SSI or otherwise. Write skew is permitted. The Oracle documentation acknowledges this in passing; production engineers learn it the hard way.

SQL Server defaults to Read Committed with two-phase-locking (it acquires shared read locks). The optional READ_COMMITTED_SNAPSHOT database setting flips it to MVCC-style. SQL Server's SERIALIZABLE is two-phase-locking with key-range locks. Deadlocks and lock-escalation incidents at Read Committed are common enough that "the problem is locking" is a default debugging hypothesis.

SQLite is Serializable by default in the formal sense, achieved by the simplest possible mechanism: only one writer at a time. Readers run concurrently; when a writer arrives it acquires the database-wide write lock. The recently-added WAL mode (SQLite WAL, 2010) allows readers to continue while a writer is active. The cost: SQLite handles only modest write throughput, which is fine for embedded use and surprisingly capable for many web apps as benchmarks from Fly.io and others have shown.

Cassandra historically had no isolation across rows or partitions; lightweight transactions (LWT, IF NOT EXISTS) added Paxos-based linearisable single-row CAS in 2.0 (2013). Cassandra's "isolation" applies only within a single partition's row; cross-partition transactions are not supported by the engine. Apps that need them have to compose them at the application layer or migrate to ScyllaDB's alternator/Tablets, FoundationDB, or Spanner.

Stronger isolation is enforced by either locks (two-phase locking, 2PL) or by aborting and retrying conflicting transactions (optimistic concurrency control, SSI, MVCC with conflict checks). Both are expensive at high concurrency. Lock-based serialisability degrades sharply at contention — throughput falls off a cliff once the conflict ratio crosses a few percent because every blocked transaction holds locks that block the next one. OCC retries can cascade: a hot row sees a parade of transactions all trying to update it, each aborting because of the previous, with goodput approaching zero while CPU stays at 100%.

Concrete numbers from public benchmarks. On Postgres TPC-C with mixed workload, switching from Read Committed to Serializable typically costs 10–20% throughput; the cost rises to 40–60% on contention-heavy workloads where SSI's retry rate climbs. Bailis, Davidson, Fekete, Ghodsi, Hellerstein and Stoica's VLDB 2014 paper Highly Available Transactions: Virtues and Limitations defined which isolation levels remain available under network partition and which do not — a useful complement to the CAP theorem when picking a level for a distributed system. Read Committed and Snapshot Isolation can be highly available; Serializable cannot, in any system that uses synchronous coordination.

Kyle Kingsbury's Jepsen test suite (jepsen.io) is the industry-standard mechanical check: it generates random concurrent workloads, kills servers and partitions networks during execution, and analyses the recorded history with the Knossos / Elle linearisability and serialisability checkers (Kingsbury & Alvaro, VLDB 2020). Jepsen has found dozens of isolation bugs in shipping databases: Postgres CIC index corruption (2020), MongoDB causal consistency violations (multiple), CockroachDB pre-1.0 stale-read issues, Yugabyte read-committed wrong defaults (2021), TiDB stale-read window (2023), FaunaDB linearisability gaps (2018), Cassandra LWT issues (multiple). Most of those reports were fixed; the ones that haven't been are documented in the vendor's "known issues" page. The Jepsen reports are the closest thing the database industry has to peer-reviewed correctness claims.

Most apps run at Read Committed and add row-level locking (SELECT ... FOR UPDATE) where they need read-then-write atomicity. It's a per-query opt-in to higher isolation, surgically applied. Higher levels are reserved for the few transactions whose invariants the application cannot reliably enforce in code: financial transfers (debit and credit must both succeed), inventory decrements (cannot go negative), uniqueness across a logical key that crosses physical rows. The cost of getting these wrong is silent corruption; the cost of running everything at Serializable is throughput your business often can't afford.

Three questions decide the level for a transaction. What invariant must this transaction preserve? If it's a single-row invariant ("balance >= 0"), a CHECK constraint plus row-level locking on UPDATE is sufficient at Read Committed. If it's a multi-row invariant ("there is at least one doctor on call"), only Serializable will do, or you must take an explicit lock on a sentinel row that all transactions in the family also lock. The rota example from Part 04 is the multi-row case.

How often will this transaction conflict with other transactions? Low-conflict workloads (different users editing different rows) tolerate Serializable cheaply; SSI's retry rate stays near zero. High-conflict workloads (everybody decrementing the same inventory counter) need a different approach — explicit locking, sharded counters, eventual reconciliation, or a CRDT-style merge. Increasing isolation when contention is the bottleneck makes the problem worse, not better; the database happily aborts every transaction trying to touch the hot row.

What is the application's tolerance for retry? Serialisable on Postgres signals conflict via SQLSTATE 40001; the app must catch it, retry the whole transaction, and only give up after a bounded number of attempts. ORM layers often hide this loop behind helpers like Rails' retry_on_serialization_failure or Django's transaction.atomic; without retry handling, raising the level just produces user-visible errors instead of silent corruption. The trade-off is corruption-by-silence vs failure-by-retry, and the second is almost always preferable.

The pragmatic stack: keep the database default for read-mostly transactions; bump to Serializable for the small set of correctness-critical transactions that touch invariants the application can't enforce; use SELECT FOR UPDATE for the surgical case where you need read-then-write atomicity on a single row. Document the choice in the migration; isolation level is a property of business logic, not of infrastructure, and it deserves a comment in the code that explains why this transaction warranted the higher cost. Most production isolation-related bugs are discovered years after the code was written, by someone who doesn't remember why the default was changed; the comment is the only line of defence.

Two anti-patterns recur. The first is the read-modify-write loop implemented at Read Committed without a lock: read the counter, increment it in application memory, write it back. Concurrent runs lose updates silently. The fix is either an in-database increment (UPDATE counters SET n = n + 1 WHERE id = ?, which the engine serialises atomically), an explicit row lock (SELECT n FROM counters WHERE id = ? FOR UPDATE), or a CAS-style optimistic update (UPDATE counters SET n = ?new WHERE id = ? AND n = ?old) with retry on zero-row-affected. The second anti-pattern is the distributed lock via Redis or Zookeeper used to compensate for an inadequate database isolation level: the lock can fail (network partition, lease expiry, GC pause) and leave the invariant broken. Martin Kleppmann's 2016 essay How to do distributed locking is the canonical critique. The right tool is database-level isolation, not application-level locks layered on top of it.