Why database isolation levels exist

Only the top level is simply correct. The others exist because correctness costs money — and the standard's list of what can go wrong turned out to be one entry short.

The picture version

The whole idea in seven pictures, for a reader who has never thought about two things happening in a database at once. The prose below fills in the seams the pictures skip.

Why it exists

You and your flatmate share a bank account with two pots: checking and savings, £100 in each. The bank’s rule is the friendly kind — either pot may go negative, as long as the two together stay at or above zero. At 14:32:07 you tap your card for £150 out of checking. At 14:32:07 your flatmate withdraws £150 from savings at a cash machine across town.

Both requests do the honest thing. Yours reads both pots, sees £100 + £100, works out that after your £150 the pair sits at £50, and approves. Theirs reads both pots, sees £100 + £100, works out the same £50, and approves. Neither one lied. Neither one read stale data in any way it could detect. And the account ends the second at minus £100, which the bank’s own rule says is impossible.

Follow this account for the rest of the post. Everything below is about the gap that just opened: each transaction was individually correct, and the pair of them was not.

The clean fix is obvious and unaffordable. Run transactions strictly one at a time — your card tap finishes completely, then the cash machine starts — and your flatmate’s request reads £-50 + £100 and gets declined. Correct, by construction. Also a database that serves one customer at a time, which is not a database anybody can run a bank on.

So every real database does the unaffordable thing partially. An isolation level is how you say how much of it you want.

Why it matters now

You picked one this morning without knowing it. Postgres hands you READ COMMITTED by default; MySQL’s InnoDB hands you REPEATABLE READ; Oracle hands you READ COMMITTED. All three ship defaulting to something weaker than serializable, because serializable is the expensive one and most workloads never notice the difference. Most.

The three places this stops being academic: money and inventory (the joint account above, the last seat on a flight, a coupon redeemable once), any “check-then-act” rule your application enforces in SQL rather than in a constraint, and any migration between engines — because, as the seams below get into, REPEATABLE READ in Postgres and REPEATABLE READ in MySQL are not the same product with the same name. They are different products with the same name.

The short answer

isolation level = how much "run them one at a time" you're paying for

Picture to keep: a turnstile at the top of the stairs. The strictest setting is a turnstile that admits one person at a time — nothing surprising can happen on the stairs, and the queue outside is long. Every weaker setting is a wider gate: more people through per second, and one more way for two of them to trip over each other on the way up.

Where that picture breaks, and it’s the useful part: the strictest level does not actually make transactions take turns. It promises that the result is one that some single-file order could have produced. How the engine keeps that promise while still running everything at once is the whole of How it works.

How it works

Build the ladder from the failures. Keep the joint account in view.

Naive attempt: run transactions one at a time. This is the definition of correctness we want, and it’s called serial execution. Your tap commits, the cash machine reads the real £-50, declines. Perfect. Why it breaks: throughput of one. Every card tap now queues behind whatever long-running report happens to be sweeping the accounts table, because the rule is no overlap, not no overlap with the rows I touch.

Fix: run them concurrently, and let each read whatever is in the row right now. Why it breaks: your flatmate’s withdrawal writes savings down to £-50, then their card is declined for an unrelated reason and the transaction rolls back. Your request read that £-50 in the gap. You get declined on the strength of a withdrawal that never happened. That’s a dirty read — reading a change that later gets undone.

Fix: only ever read data from transactions that committed. That rule has a name: READ COMMITTED — Postgres’s default, and Oracle’s. Why it breaks: your request reads savings twice — once to apply the rule, once to print the receipt. Between the two, your flatmate commits. Same row, same transaction, two different answers. Your decision and your receipt now disagree. That’s a non-repeatable read.

Fix: fix the rows you’ve read for the whole transaction, not just for one statement. That’s REPEATABLE READ. Why it breaks: you don’t read a row, you read a set — SELECT SUM(balance) FROM pots WHERE account = 42. Holding still the rows you already saw does nothing about a row that didn’t exist when you looked. Your flatmate’s bank app opens a third pot mid-transaction and pays £150 of wages into it; you run the same query again and the sum has gone from £200 to £350. There was nothing to hold still, because there was nothing there. That’s a phantom read.

Fix: hold still the query, not the rows — the whole space of rows the WHERE clause describes, including the ones nobody has written yet. With dirty reads, non-repeatable reads and phantoms all ruled out, you’re at the level SQL-92 calls SERIALIZABLE. Those four levels against those three anomalies are the grid at the front of every engine’s concurrency chapter.

So your engine now forbids all three. Go back to 14:32:07 — before you read on, commit to an answer: is the joint account safe?

The list was one entry short

It isn’t safe, and the way it isn’t is the most interesting thing about this topic.

Give both requests a snapshot: a frozen picture of the whole database as of the moment each transaction started — which is exactly what keeping several versions of every row, or MVCC, makes cheap. Now check the list. Dirty read? No — every snapshot is built from committed data. Non-repeatable read? No — the snapshot doesn’t move for the transaction’s whole life. Phantom? No — a row created after your snapshot simply isn’t in your picture. All three anomalies are gone, and the account still ends the second at minus £100, because you and your flatmate each read a consistent picture, each wrote a different row, and neither write was visible to the other’s snapshot. Nothing on the list was violated. The money is gone anyway. Look again at Postgres’s version of that grid and you’ll find a fourth column beside the three anomalies, headed serialization anomaly. It has to be there because the three don’t add up to correctness — and the standard knew it, which is why SQL-92 defines SERIALIZABLE positively (the result must match some serial order) rather than as the bottom row of the anomaly table.

That anomaly is write skew, and this is not a hypothetical someone thought up later. It’s the argument of Berenson, Bernstein, Gray, Melton, O’Neil and O’Neil’s 1995 paper A Critique of ANSI SQL Isolation Levels, which named the mechanism snapshot isolation, catalogued write skew as the anomaly A5B, and used — genuinely — a bank constraint over two jointly held balances as the example.

Why did the list fail? Because it was never a definition of correctness — it was a list, and the shape of the list is suspiciously close to the shape of a lock manager. Hold no read locks and you get dirty reads; hold them for one statement and you get non-repeatable reads; hold them until commit and you still get phantoms; lock ranges and you don’t. My read is that the anomalies were picked to describe what the lock-based implementations of the day happened to do; the paper’s own and sharper claim is that the ANSI definitions fail to characterize even those implementations correctly. Either way the moral holds: define safety by enumerating what can go wrong and you are one entry short forever — and snapshot isolation is the proof, because it doesn’t even sit on the ladder. It forbids phantoms, which SQL-92 permits at REPEATABLE READ, and permits write skew, which lock-based REPEATABLE READ forbids. It is not stronger or weaker than that rung. It is off to one side.

Fix: define the top of the ladder positively, by subtraction from nothing. True SERIALIZABLE means: whatever order the engine actually ran things in, the outcome is one that some single-file order would have produced. Not “no dirty reads and no phantoms” — “indistinguishable from taking turns.” That definition has no list to fall off the end of, and there are two families of way to keep it:

• Pessimistic — make transactions genuinely take turns whenever they’d conflict. 2PL with range locks is the classic; MySQL’s InnoDB reaches for gap locks in the same spirit. You pay in waiting, and in deadlocks.
• Optimistic — let everyone run on snapshots, but watch the read/write dependencies between live transactions, and abort one the moment the pattern of dependencies couldn’t have come from any serial order. This is SSI, which Postgres has used for its SERIALIZABLE level since 9.1 in 2011. You pay in retries: ERROR: could not serialize access due to read/write dependencies among transactions, and your application is expected to run the whole transaction again.

That’s the real shape of the bill, and it’s worth carrying: the pessimistic route charges you latency, the optimistic route charges you retry logic. Neither is free, which is why the menu has four items instead of one.

Show the seams

• The level names do not port. Postgres’s REPEATABLE READ is snapshot isolation: stronger than the standard requires (no phantoms) and weaker in a way the standard never anticipated (write skew). InnoDB’s REPEATABLE READ is different again — plain SELECTs read the snapshot from the transaction’s first read, but locking reads and UPDATE/DELETE work against the latest committed row, so one transaction can hold two different views of the same data. Oracle offers only READ COMMITTED, SERIALIZABLE and a read-only mode; its SERIALIZABLE sees, in the docs’ own words, “only changes committed at the time the transaction — not the query — began,” and raises ORA-08177 when a write collides. That is the snapshot-isolation shape rather than the dependency-checking one, and reading Oracle’s SERIALIZABLE as snapshot isolation is the standard interpretation in the concurrency-control literature — though Oracle’s own docs don’t use the phrase. And Postgres accepts READ UNCOMMITTED and quietly gives you READ COMMITTED instead. So SET TRANSACTION ISOLATION LEVEL REPEATABLE READ is not a portable request. It’s a per-engine one that happens to share spelling.
• The level is a ceiling on the database’s window, not on your program’s. SERIALIZABLE protects what happens between BEGIN and COMMIT. If your app reads a balance in one HTTP request and writes it in the next, there are two transactions and the database was never shown the thing you wanted protected. No isolation level reaches outside its own transaction.
• The honest alternative is often not a higher level. Write skew needs two different rows; it evaporates if the invariant lives in one. Keep the account’s total in a single row that both withdrawals must update, and the two transactions collide on an ordinary write-write conflict, which every level from READ COMMITTED up already handles. (A per-row CHECK won’t do this on its own — in standard SQL a CHECK sees only the row it’s attached to, so it can’t police a sum across rows.) Isolation levels are one tool for enforcing invariants, and frequently the expensive one.
• Weak defaults are mostly the right call. Two transactions that never touch the same data can’t produce any of these anomalies, and the overwhelming majority of queries in a normal application are exactly that. Paying for serializability across the whole workload to protect the handful of transactions carrying a cross-row invariant is a bad trade. All three engines above let you raise the level for one transaction, and that is usually the answer.

You started with isolation level = how much "run them one at a time" you're paying for. What did this post add about the top of the price list? — that the top item isn’t the one at the top of the standard’s table. Ruling out the three named anomalies is not the same as being correct, and the level worth the name is the one defined by what it guarantees (a result some serial order could have produced) rather than by the list of surprises it happens to forbid. Everything below it is a discount you are choosing to take, and the joint account is the bill arriving.

Check yourself

Before you go — your team is getting could not serialize access due to read/write dependencies errors under load on Postgres SERIALIZABLE, and someone proposes dropping to REPEATABLE READ to make them stop. Do they stop, and what did you just trade?

And one more — you move the whole application to SERIALIZABLE and the joint account is now safe. Your colleague points out that the mobile app reads the two balances in one API call, shows a confirmation screen, and posts the withdrawal thirty seconds later in a second call. Is that path safe too?

Famous related terms

• Dirty read — dirty read = reading a change that later gets rolled back — the one anomaly every level above the bottom rung forbids, which is why READ UNCOMMITTED is barely implemented (Postgres accepts the words and ignores them; Oracle doesn’t offer it at all).
• Write skew — write skew = two transactions read the same consistent picture, write different rows, and jointly break a rule neither could see — the anomaly that isn’t on the standard’s list.
• Snapshot isolation — snapshot isolation ≈ MVCC + "read one frozen snapshot, resolve write conflicts at commit" — the level the standard forgot to name; Postgres sells it as REPEATABLE READ and Oracle as SERIALIZABLE. See MVCC for how the snapshots are built.
• Two-phase locking (2PL) — 2PL = take all your locks, then release all your locks, never interleave the two phases — the pessimistic road to serializability, and the road that leads through deadlock.
• ACID — ACID = atomicity + consistency + isolation + durability — this post is the I; the write-ahead log is most of the A and D.

Going deeper

• Berenson et al., A Critique of ANSI SQL Isolation Levels (SIGMOD 1995) — the primary source, and the answer to “why isn’t this a clean ladder?”: it’s where snapshot isolation was defined and where write skew was catalogued as the hole in the standard’s list.
• Postgres, “Transaction Isolation” — the answer to “what does each level actually promise me on the engine I’m running?”, including the standard’s anomaly table annotated with where Postgres is deliberately stricter.
• Ports & Grittner, Serializable Snapshot Isolation in PostgreSQL (VLDB 2012) — the rabbit hole, answering “how do you get serializability without making anybody wait?” by walking through the dependency patterns SSI hunts for.