Isolation levels: a price list, not a morality table
Policy vs mechanism. The isolation level is the contract — which anomalous sights a transaction is promised never to see. mvcc and locking are the mechanisms underneath that keep the promise. Levels are chosen per transaction, not per database.
The three anomalies (the yardstick the standard levels are defined against). Dirty read: reading another transaction's uncommitted data. Non-repeatable read: the same row read twice inside one transaction returns different values, or is gone — one row changed (someone UPDATE/DELETE'd it). Phantom read: the same range query run twice returns a different row count — the set changed (someone INSERTed into the range). The distinction that matters: phantoms come from rows that did not exist, so guarding existing rows cannot, by itself, stop them.
The four standard levels, loose to strict. Read Uncommitted: blocks nothing. Read Committed: blocks dirty reads. Repeatable Read: also blocks non-repeatable reads; the standard text still permits phantoms. Serializable: blocks all three — the outcome must equal some serial execution of the transactions.
Snapshot timing is the real RC/RR border. Under MVCC, Read Committed takes a fresh snapshot for every statement — re-run the same SELECT after a concurrent commit and you see the new world, hence non-repeatable reads. Repeatable Read takes one snapshot per transaction (at its first read) and reuses it for every read. RR is therefore not "two snapshots that agree": there is only ever one photo, so disagreement is impossible. RC is many photos; RR is one.
The limits of a frozen snapshot. It lives for exactly one transaction — when the transaction ends, the next one re-photographs and sees the moved world. Held long, it reads staler and staler data and pins old versions against cleanup (the GC-root effect in mvcc). And writing pulls you back to the present: if an RR transaction tries to update a row that changed after its snapshot, PostgreSQL aborts the whole transaction with a serialization error ("could not serialize access due to concurrent update") — you may not stack a write on a version you never saw, so the application retries. InnoDB takes the other route: writes use a current read — the newest committed version of the row, locked — and proceed from there.
The menu per engine — count behaviors, not names. Engines pick from the standard four, but they may skip, merge, or add. MySQL/InnoDB: all four names, four genuinely different behaviors, default Repeatable Read. PostgreSQL: four names, three behaviors — Read Uncommitted is silently executed as Read Committed; default Read Committed; its RR is snapshot isolation, its Serializable is SSI (snapshots plus conflict detection). Oracle: only two levels exist — Read Committed (default) and Serializable, which is really snapshot isolation under the hood. SQL Server: the four, plus two off-menu snapshot levels (SNAPSHOT, READ_COMMITTED_SNAPSHOT). The famous off-menu entry is Snapshot Isolation itself: stronger than RR (no phantoms for readers), weaker than Serializable — it lets through write skew, an anomaly the three-anomaly table cannot even express.
Phantoms and indexes — two layers not to conflate. The anomaly is logical: it is defined on query results, and a sequential scan that re-reads the table sees the newly inserted row just as an index scan does. Indexes enter only through the cure. InnoDB's gap locks and next-key locks lock the gaps between index records — ranges and gaps only exist inside an ordered index, so without a usable index InnoDB falls back to locking everything its scan touches, effectively the table. PostgreSQL's Serializable keeps predicate locks (SIREAD); with no index to narrow them, the grain coarsens up to the whole relation, and conflicts multiply into aborts. In both engines, at Serializable with no index, the guarantee never degrades — the concurrency does: PostgreSQL answers with retry storms, InnoDB with blocking.
The price law. Down the four levels, guarantee rises and concurrency strictly decreases — a monotone curve, within any one implementation family. The bill is paid in one of three currencies: waiting (locking implementations), retrying (abort-based implementations), or staleness (snapshot readers). The curve is not a law of nature: a better mechanism pushes the whole frontier outward — snapshot-based RR beats locking-based RC on guarantee and on read concurrency — but along any one curve, stricter still costs concurrency.
Why Serializable is not the default. Its cost leaks into the application (retry logic around aborts) or into throughput (locking). Most transactions are point reads and writes that RC fully covers, so defaults sit at the value point, and the few transactions that need more opt in individually. Why Read Uncommitted survives: approximate statistics (dashboards, rough counts) where the freshest in-flight data is acceptable — sometimes even the point (watching a long import's uncommitted progress) — at the highest throughput, since reads wait for nothing. Every level on the menu has a workload where it is the rational buy.
When Serializable semantics are genuinely required — when the business rule is a property of a set, not of one row. (1) At-least-one on shift: two doctors each check "2 doctors on shift", each leaves; the shift ends empty. Each transaction touched only its own row, yet the set invariant broke — that is write skew, and RC/RR both let it through.