Database Isolation Levels Explained: The Anomalies Each One Prevents

Every database transaction runs alongside others, and the isolation level is the dial that decides how much those concurrent transactions are allowed to see of each other’s half-finished work. It’s the “I” in ACID, and it’s the setting most teams never touch — until a double-charged customer or a negative inventory count forces them to learn what their default actually permits.

The tradeoff is concrete: stricter isolation means fewer concurrency bugs and lower throughput; looser isolation means more throughput and a wider set of anomalies you have to defend against in application code. Picking a level is really picking which anomalies you can tolerate.

The four standard levels

The SQL standard defines four isolation levels, ordered by strictness. Each one is defined by the anomalies it forbids:

Read each row as “this level guarantees the anomalies marked Prevented cannot happen.” Read Uncommitted promises almost nothing. Serializable promises the result is equivalent to running every transaction one at a time, in some order.

A detail that trips people up: the standard defines levels by which anomalies they forbid, not by how they’re implemented. Two databases can both call a level “Repeatable Read” and behave differently, as long as both forbid the required anomalies. That gap is where most production surprises live, so we’ll come back to it.

The anomalies, with concrete cases

Dirty read — Transaction A updates a row, Transaction B reads the new value, then A rolls back. B acted on data that never officially existed. Example: B reads an account balance mid-transfer, before A’s transfer aborts, and approves a withdrawal against money that was never there.

Non-repeatable read — A reads a row, B updates and commits that row, A reads it again and gets a different value inside the same transaction. A report that sums a column twice and gets two different totals is the usual symptom.

Phantom read — A runs a query with a WHERE clause (say, “all orders over $500”), B inserts a new row that matches and commits, and A re-runs the query to find an extra row. The difference from a non-repeatable read: phantoms are about rows appearing or disappearing from a result set, not a single row’s value changing.

Lost update — A and B both read a counter at 10, both add 1, both write 11. One increment vanished. This is the classic read-modify-write race, and it’s why UPDATE counter SET n = n + 1 is safer than reading n into your application and writing 11 back.

Write skew — The subtle one, and not in the table above. A and B each read overlapping data, each checks an invariant that currently holds, then each writes a different row. Individually both writes are fine; together they break the invariant. The textbook case: two on-call doctors each check “at least one other doctor is on call,” both see it’s true, and both clock off at the same moment. Now nobody is on call.

What your database actually does

Here is the gap between the standard and reality.

PostgreSQL’s “Repeatable Read” is implemented as snapshot isolation: every statement in the transaction sees a frozen snapshot taken at the transaction’s start. A useful side effect is that it also prevents phantom reads, which the standard does not require at this level. But snapshot isolation does not prevent write skew — that on-call doctor bug runs cleanly under PostgreSQL Repeatable Read.

To kill write skew in PostgreSQL you need Serializable, which uses Serializable Snapshot Isolation (SSI). SSI tracks read/write dependencies between transactions and aborts one with a serialization error when it detects a dangerous cycle. That means your application has to be ready to catch the error and retry the transaction — Serializable isn’t free, it just moves the cost from “silent corruption” to “explicit retries.”

MySQL’s InnoDB takes a different route at Repeatable Read: it uses next-key locks (a row lock plus a gap lock on the range) to block the inserts that would cause phantoms, so InnoDB suppresses phantoms through locking rather than snapshots. The behavior you get for the same level name is genuinely different from PostgreSQL.

The practical takeaway: don’t reason about your concurrency from the standard’s table alone. Reason from your specific engine’s documented behavior at the level you’ve actually set.

Transaction-heavy code is hard to audit by eye, because the bug lives in the interleaving of two transactions rather than in any single line you can point at. An editor that can hold the whole transaction path in context — every place a balance is read and later written — makes those races easier to spot before they ship.