Isolation Levels & Their Anomalies

ACID, Precisely left you with one sentence: isolation is a dial your database turns down by default — Postgres to read committed, not serializable. This lesson reads every notch on that dial. And the first thing you learn is unsettling: the notches are labeled wrong.

Start with what a level even is. The SQL standard doesn't define isolation levels by a mechanism — it defines them by which anomalies (it calls them phenomena) each one is allowed to let through. There are three in the standard — dirty read, non-repeatable read, phantom — and the four levels are just rows in a table of which ones they forbid:

Clean ladder. Except it's a lie in three ways, and every one bites in production. First, the standard's definitions are ambiguous — the 1995 paper A Critique of ANSI SQL Isolation Levels (Berenson, Bernstein, Gray, and the O'Neils) showed the English can be read two different ways, so "repeatable read" doesn't pin down a single behavior. Second, the table is incomplete — it silently omits real anomalies that do happen: lost update and write skew (we'll meet the second one and never forget it). Third — and this is the one that will actually page you — the names don't match what databases do.

Martin Kleppmann built a test suite called Hermitage to nail down what each engine's levels really mean, and the results are a catalogue of broken promises. His summary: "PostgreSQL, MySQL and MS SQL Server all boast an isolation level called 'repeatable read', but it means something different in every one of them." Oracle's "serializable" is not serializable at all — it's snapshot isolation. Only "read committed" behaves the same everywhere — the one honest name on the dial. We measured a piece of this ourselves, on PostgreSQL 16 and MySQL 8.4, and you'll see the receipts throughout this lesson. The takeaway to carry into all of it: a level is defined by which anomalies leak through it — and the label on the notch is not a portable guarantee. So let's meet the anomalies.

Every isolation bug is one of a small handful of two-transaction races. Learn these five as concrete interleavings — T1 does this, T2 does that, in this order — and the whole topic snaps into focus. (Each is reproduced live in the lab below; the values here are the ones we actually measured on Postgres and MySQL.)

• Dirty read — you read another transaction's uncommitted write. T2 does UPDATE x = 999 and hasn't committed; T1 reads x and sees 999; then T2 rolls back. T1 acted on a value that never existed. (Measured on MySQL at Read Uncommitted: T1 read 999. On Postgres at the same level: T1 read 100 — the committed value — because Postgres refuses to do dirty reads at all.)
• Non-repeatable read — you read the same row twice and it changed under you. T1 reads x = 100; T2 commits x = 200; T1 re-reads and gets 200. Same query, same transaction, two answers. (Measured at Read Committed: 100 then 200.)
• Phantom — you run the same predicate query twice and new rows appear. T1 runs SELECT count(*) WHERE v ≥ 500 and gets 0; T2 inserts a matching row and commits; T1 re-runs and gets 1. Nothing you read changed — but the set of matching rows did. (Measured at Read Committed: 0 then 1.)
• Lost update — two read-modify-writes clobber each other. Both read a balance of 100; T1 writes 100 − 30 = 70; T2, still holding its stale 100, writes 100 − 40 = 60. The final balance is 60 — but two debits of 30 and 40 should leave 30. T1's entire debit vanished. (Measured at Read Committed: final 60.)
• Write skew — the subtle one, and the reason this lesson exists. Two transactions each read a shared invariant, each independently decide their write is safe, and each write a different row — and together they break the invariant, with no conflict the database can see. We'll give it its own section, because it's the anomaly that separates "looks serializable" from "is serializable."

Notice the shape: the first three are about reads going stale or growing; the last two are about writes based on stale reads. That split is exactly why the levels stop them at different points — which is the ladder we climb next.

Forget the four ANSI names for a moment and look at what you actually run in production. There are really three tiers, and climbing them turns anomalies off one group at a time.

Tier 1 — Read Committed. The honest name; the default in Postgres, Oracle, SQL Server. Its one job is to kill the dirty read: you only ever see committed data. It does this by re-reading the latest committed value on every statement — which is exactly why it still lets non-repeatable reads and phantoms through. Each query is a fresh look at a moving world.

(Two engines, same name, different edges. At Read Uncommitted, MySQL genuinely hands you the dirty read — we measured T1 reading the uncommitted 999. Postgres silently upgrades Read Uncommitted to Read Committed and returned 100; it has no dirty-read mode at all. Same SQL, opposite behavior — the names-lie point, in one keystroke.)

Tier 2 — Snapshot Isolation. This is the tier almost everyone calls "repeatable read," and almost no engine implements the way ANSI wrote it. Instead of re-reading live data, the transaction takes a consistent snapshot — a frozen photo of the whole committed database as of its start — and reads everything from that photo for its entire life. (This is the machinery from MVCC: Readers Don't Block Writers — multiple versions of each row, and a snapshot that picks the right ones.) One mechanism, and three anomalies fall at once:

• Non-repeatable read — gone. We ran the identical interleaving at Postgres's repeatable read: T1 read 100, T2 committed 200, T1 re-read — and still got 100. The snapshot doesn't move.
• Phantom — gone. Same test with a matching INSERT: at Read Committed the count went 0 → 1; at Postgres's repeatable read it stayed 0 → 0. (Note: ANSI says repeatable read still permits phantoms — but real snapshot isolation is stronger than the standard here. The name undersells it, which is its own kind of lie.)
• Lost update — caught. Two read-modify-writes that left 60 under Read Committed instead aborted under Postgres's repeatable read: the moment T2 tried to commit its write over a row T1 had already committed, Postgres raised ERROR: could not serialize access due to concurrent update, and the balance correctly stayed 70. Snapshot isolation enforces first-committer-wins: if two transactions write the same row, the second to commit is thrown out and must retry.

So Snapshot Isolation looks fantastic — dirty reads, non-repeatable reads, phantoms, lost updates, all handled. It looks, in fact, serializable. It is not. There is one anomaly left, and it walks straight through a consistent snapshot.

Here is the invariant: at least one doctor must be on call. Alice and Bob are both on call, and both feel unwell and want to go home. Each opens a transaction. Watch it at snapshot isolation — Postgres's repeatable read, the level you just decided looks bulletproof:

-- T1 (Alice)                          -- T2 (Bob), concurrently
SELECT count(*) FROM doctors               SELECT count(*) FROM doctors
  WHERE on_call;   -->  2                     WHERE on_call;   -->  2
-- "2 on call, safe for me to leave"        -- "2 on call, safe for me to leave"
UPDATE doctors SET on_call = false         UPDATE doctors SET on_call = false
  WHERE name = 'alice';                       WHERE name = 'bob';
COMMIT;   -->  ok                           COMMIT;   -->  ok

Both read 2. Both conclude, correctly for the state they saw, that it's safe to leave. Both write — but to different rows (alice and bob), so there is no write-write conflict for the database to catch, and first-committer-wins never triggers. Both commit. We ran exactly this on Postgres and counted the doctors afterward:

on_call = 0

Zero doctors on call. The invariant is broken — and not one error fired. This is write skew, and it is the whole reason "snapshot isolation is not serializable." Look at why your usual defenses fail here. You cannot re-read your way out: re-reading gives you the same consistent snapshot — that's the entire point of snapshot isolation, and here it's the trap. There's no lost update to catch, because the two transactions never touched the same row. Each transaction, in isolation, is perfectly correct. The bug only exists in the gap between two correct decisions made on the same frozen photo. It is the generalization of lost update — same disease (act on a stale read), but spread across different rows, so the engine's write-conflict radar sees nothing.

Write skew is everywhere once you can name it: two users claiming the last seat because both saw it free; a meeting room double-booked because both checked availability first; an account overdrawn because a withdrawal and a transfer each checked the balance was sufficient; two moderators both approving a post because each saw it un-approved. Any invariant that spans more than one row and is checked-then-acted-upon is a write-skew waiting to happen.

There are exactly three ways to stop it, and no others:

1. Turn the dial to true Serializable. We reran the doctors, unchanged, at Postgres's SERIALIZABLE. This time one commit failed:

   ERROR:  could not serialize access due to read/write dependencies among transactions
   DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
   HINT:  The transaction might succeed if retried.
   

   The other committed, and the count held at on_call = 1. Postgres's serializable engine watches for the dependency cycle that write skew creates — T1 read what T2 wrote and T2 read what T1 wrote — and aborts one transaction (the "pivot") to break it. The cost is that abort: your application must catch the error and retry.

2. Materialize the conflict. Force the two transactions to actually collide by taking an explicit lock on the rows you read, so the database does see a conflict. How to do that — and pessimistic locks versus optimistic ones — is the whole of the next lesson, Pessimistic vs Optimistic Locking.

3. Make it a constraint. If the invariant can be expressed as something the database enforces on its own — a UNIQUE index, a CHECK, a foreign key — then the second write becomes a real constraint violation the engine catches for free. (This is ACID, Precisely's point that consistency is your invariant to declare, cashed out.)

If you remember one thing from this lesson, remember this: snapshot isolation prevents the anomalies you can see coming and lets through the one you can't. "Repeatable read" is not serializable. Now go break it yourself.

Reading about interleavings is one thing; driving them is how they stick. Below is a real two-transaction simulator. Pick an isolation level and an anomaly, then interleave T1 and T2 step by step — click the next operation in either column, and watch each read resolve: against a frozen snapshot at snapshot isolation and serializable, against live committed data at read committed, or against another transaction's uncommitted buffer at read uncommitted (that's the dirty read, live). The writes buffer, take locks, and on commit are checked for first-committer-wins and dependency cycles — so the verdict you get is the one the real database gives, down to the error string.

Do this in order and you'll feel the ladder. Run write skew at Snapshot Isolation and watch the doctors both go home — zero on call, no error. Then switch the level to Serializable and run the identical schedule: one transaction aborts with the read/write-dependencies error, and the invariant holds. Then try lost update at Read Committed (final 60) versus Snapshot Isolation (aborted). Same code, same interleaving — only the notch on the dial changed.

Drop the four ANSI names and choose from the three honest tiers you can actually reason about:

• Read Committed — the sane default for most workloads. It costs almost nothing and stops the one anomaly (dirty reads) that has no legitimate use. You accept that a single transaction can see different data across statements — usually fine, because most transactions read once and write.
• Snapshot Isolation (your engine's "repeatable read") — reach for it when a transaction reads the same data more than once or must compute over a consistent view (a report, a multi-step calculation, a balance check followed by related reads). You get a stable photo and lost-update protection, cheaply, thanks to MVCC. Just remember the hole: write skew.
• Serializable — the only level that is actually correct under all concurrency, and the only cure for write skew short of hand-locking. Use it when an invariant spans multiple rows and money or safety is on the line.

The reason people avoid serializable is a myth worth killing: "it's too slow." Postgres's serializable is Serializable Snapshot Isolation (SSI) — the first production implementation of it (Ports & Grittner, 2012) — and it runs only slightly slower than plain snapshot isolation on read-heavy workloads, far faster than the old lock-everything approach. The real tax isn't throughput; it's that serializable works by aborting conflicting transactions, so every transaction needs a retry loop. Get could not serialize access…? Roll back, run it again. Budget for that and serializable is entirely practical — and it's how you sleep at night when the invariant matters.

A closing rule of thumb: default to Read Committed; upgrade a specific transaction to Snapshot Isolation when it needs a stable view; upgrade to Serializable when a cross-row invariant must not break — and always, always check what your database's level names actually mean before you trust them.

• A level is defined by which anomalies leak through it — not by a mechanism, and NOT by its name. The SQL names are unreliable: Hermitage found "repeatable read" means something different in every database, and Oracle's "serializable" is only snapshot isolation. "Read committed" is the one honest name. Always check YOUR engine.
• The five anomalies are concrete two-transaction races: dirty read (see uncommitted — MySQL RU gave 999, Postgres refused), non-repeatable read (100→200), phantom (0→1), lost update (final 60, not 30), and write skew.
• The honest ladder is three tiers. Read Committed kills dirty reads only. Snapshot Isolation (the real "repeatable read") adds a consistent per-transaction snapshot — measured to kill non-repeatable reads (100→100), phantoms (0→0), and lost updates (aborted with could not serialize access due to concurrent update). Serializable adds the rest.
• Write skew is the boss. Two transactions read a shared invariant on the same snapshot, each act correctly, each write a different row → invariant broken, no error (measured: on_call = 0). You cannot re-read your way out — the snapshot is doing its job. Snapshot isolation ≠ serializable.
• Three fixes for write skew: true Serializable (Postgres SSI aborts a pivot with could not serialize access due to read/write dependencies… → on_call = 1, and you retry), an explicit lock to materialize the conflict, or a database constraint.
• Serializable isn't slow — it's abort-and-retry. Postgres SSI ≈ snapshot-isolation speed on read-heavy work; the cost is the retry loop, not throughput. Next — Pessimistic vs Optimistic Locking: we just said "take a lock to materialize the conflict" — now let's see the two ways databases actually do that, and when each one wins.