Transaction Isolation Levels

Why isolation levels matter

A database that ran one transaction at a time would need no isolation levels at all — every transaction would see a clean, committed database and leave one behind. But serial execution wastes hardware: while one transaction waits on disk or the network, dozens of cores sit idle. So databases interleave transactions, and the moment they do, transactions can observe each other's half-finished work. Isolation levels are the contract that says exactly how much of that interleaving is allowed to leak through.

The gold standard is serializability: the concurrent execution must produce the same result as some serial order of the same transactions. That is the strongest possible guarantee, but enforcing it costs concurrency. So the SQL standard offers a menu of weaker guarantees, each admitting one more class of anomaly in exchange for more throughput. Choosing a level is a deliberate correctness-versus-performance trade — and choosing wrong ships silent data-corruption bugs that only surface under load.

The anomalies: what can go wrong

Every isolation level is defined by which of these it permits. Understanding the anomalies is the whole subject — the levels are just named cut-off points.

• Dirty read (P1). Transaction T1 reads a row that T2 wrote but has not committed. If T2 then rolls back, T1 acted on data that never existed. This is the most dangerous anomaly and the only one forbidden even at READ COMMITTED.
• Non-repeatable read / fuzzy read (P2). T1 reads a row, T2 commits an UPDATE to it, T1 reads the same row again and gets a different value. A single row changed underneath a running transaction.
• Phantom read (P3). T1 runs a predicate query (SELECT ... WHERE age > 30), T2 commits an INSERT or DELETE of a row matching that predicate, T1 re-runs the query and the set of matching rows changed. The rows T1 already read are unchanged; new ones appeared or vanished.
• Lost update. Two transactions read the same value, each computes a new value from it, and both write — the second overwrites the first, silently discarding an update. Read-modify-write on a counter is the classic trap.
• Read skew. A transaction reads two related rows at different times and sees a state that violates a cross-row invariant (e.g. reads account A before a transfer and account B after, and the two don't sum correctly).
• Write skew. The subtle one. Two transactions read an overlapping set, each verifies an invariant holds, then each writes a different row. No two writes touch the same row, so nothing conflicts — yet the combined effect breaks the invariant. Snapshot isolation permits this; serializability forbids it.

How the standard levels are defined

The SQL-92 standard defines isolation levels negatively: a level is specified by which phenomena it is required to prevent. It is a floor, not a ceiling — an engine may prevent more than required and still conform.

Isolation level	Dirty read	Non-repeatable read	Phantom read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Possible
SERIALIZABLE	Prevented	Prevented	Prevented

Notice what the standard does not mention: lost update and write skew. That omission is the crux of the famous 1995 critique "A Critique of ANSI SQL Isolation Levels" by Berenson, Bernstein, Gray, Melton, O'Neil, and O'Neil. They showed the three-phenomenon definition is ambiguous and, crucially, that a real and popular implementation strategy — snapshot isolation — sits between REPEATABLE READ and SERIALIZABLE in a way the standard's grid cannot express. It prevents all three ANSI phenomena yet still permits write skew, so it is neither REPEATABLE READ (it's stronger — no phantoms) nor SERIALIZABLE (it's weaker — write skew). The standard's vocabulary simply has no name for it.

MVCC and snapshot isolation: how real engines do it

Textbook isolation is enforced with two-phase locking (2PL): readers take shared locks, writers take exclusive locks, and everyone holds them until commit. It works, but readers block writers and writers block readers — a throughput disaster for read-heavy workloads. Every major modern database instead uses Multi-Version Concurrency Control (MVCC).

Under MVCC, an UPDATE does not overwrite a row in place; it writes a new version tagged with the transaction ID that created it, leaving the old version intact. A reader is handed a snapshot — a consistent picture of which transaction IDs had committed at the moment the snapshot was taken. When the reader touches a row, it walks the version chain and returns the newest version visible to its snapshot, ignoring anything committed after. The payoff is the defining MVCC property: readers never block writers, and writers never block readers. A reader always has a version to read; it never waits.

The isolation level then just controls when the snapshot is taken:

• READ COMMITTED (PostgreSQL default): a new snapshot is taken at the start of each statement. So each statement sees the latest committed data — but two statements in the same transaction can disagree, which is exactly a non-repeatable read.
• REPEATABLE READ / SNAPSHOT (PostgreSQL, Oracle): one snapshot is taken at the transaction's first query and reused for the whole transaction. Every read is consistent with every other. In PostgreSQL this is genuine snapshot isolation, so it also prevents phantoms — stronger than the standard requires.

Snapshot isolation's write path uses first-committer-wins: if two transactions update the same row, the first to commit wins and the second gets a serialization error (PostgreSQL: ERROR: could not serialize access due to concurrent update). This kills lost updates on the same row. But — and this is the whole reason SERIALIZABLE exists — it does nothing about write skew, where the two transactions write different rows.

Getting true serializability back: SSI

To recover full serializability without paying 2PL's blocking cost, PostgreSQL (since 9.1, 2011) implements Serializable Snapshot Isolation (SSI), based on Michael Cahill's 2008 PhD work. SSI runs on top of ordinary snapshot isolation but tracks read-write dependencies between concurrent transactions. Serializability is violated only when those dependencies form a specific pattern — two consecutive rw-antidependency edges where a transaction reads data another later writes — a "dangerous structure." When SSI detects one, it aborts one of the transactions with a serialization failure, and the application retries.

This is the deep shift in how to think about strong isolation on MVCC: the cost of SERIALIZABLE is not blocking, it's aborts. Under low contention, dangerous structures are rare and SSI is nearly free. Under high contention, retry storms can dominate — which is why READ COMMITTED remains the pragmatic default and SERIALIZABLE is reserved for the transactions that genuinely need it.

Worked example: the on-call doctors write skew

The canonical write-skew scenario. A hospital enforces an invariant: at least one doctor must remain on call. Two doctors, Alice and Bob, are both on call and both decide to leave at the same instant.

-- Invariant: COUNT(*) WHERE on_call = true must stay >= 1
-- Both transactions run at SNAPSHOT / REPEATABLE READ isolation.

-- Transaction T1 (Alice removing herself)        Transaction T2 (Bob removing himself)
BEGIN;                                             BEGIN;
SELECT count(*) FROM doctors                       SELECT count(*) FROM doctors
  WHERE on_call = true;   -- returns 2               WHERE on_call = true;   -- returns 2
-- T1 sees 2 >= 1, safe to leave                   -- T2 sees 2 >= 1, safe to leave
UPDATE doctors SET on_call = false                 UPDATE doctors SET on_call = false
  WHERE name = 'Alice';                              WHERE name = 'Bob';
COMMIT;   -- ok, different row                      COMMIT;   -- ok, different row
-- Result: on_call count is now 0. Invariant violated.

Both transactions read the same predicate (on_call = true), both saw the count 2, both wrote — but to different rows. Snapshot isolation's first-committer-wins only detects write-write conflicts on the same row, so it happily commits both. The result is zero doctors on call, a state no serial order of these two transactions could ever produce (in any serial order, the second transaction would see count 1 and abort its logic).

Under SERIALIZABLE (SSI), PostgreSQL notices the read-write dependency cycle — each transaction read a row set the other wrote into — and aborts one with a serialization failure. The fix at weaker levels is to force a write-write conflict, e.g. SELECT ... FOR UPDATE to lock the read rows, or materialize the invariant into a single row both transactions must update.

How the engines actually behave

The single most important practical fact: the same level name means different guarantees in different databases. The standard defines a floor; each engine picks where it lands above it.

Engine · level	Mechanism	Phantoms?	Write skew?
PostgreSQL · READ COMMITTED (default)	Per-statement snapshot (MVCC)	Possible	Possible
PostgreSQL · REPEATABLE READ	Snapshot isolation	Prevented	Possible
PostgreSQL · SERIALIZABLE	SSI (snapshot + dependency abort)	Prevented	Prevented
Oracle · SERIALIZABLE	Snapshot isolation (misnamed)	Prevented	Possible
MySQL/InnoDB · REPEATABLE READ (default)	MVCC + next-key locks	Mostly prevented*	Possible
SQL Server · SERIALIZABLE	Strict two-phase locking + range locks	Prevented	Prevented

* InnoDB's REPEATABLE READ prevents phantoms for locking reads via next-key (gap) locks, but plain consistent-read snapshots can still surface subtle anomalies; its behavior is famously not full snapshot isolation. Oracle's "SERIALIZABLE" is snapshot isolation and permits write skew — a level named SERIALIZABLE that is not, in fact, serializable, which is precisely the trap the 1995 critique warned about.

Setting the level in practice

-- Per-transaction (recommended: scope it tightly)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  -- ... reads and writes that need the strong guarantee ...
COMMIT;

-- Session default
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Application-side retry loop is MANDATORY at SERIALIZABLE / REPEATABLE READ
-- because the engine may abort with a serialization failure (SQLSTATE 40001).

import psycopg2
from psycopg2.errors import SerializationFailure

def run_serializable(conn, work, max_retries=5):
    for attempt in range(max_retries):
        try:
            conn.set_isolation_level(
                psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE)
            with conn:                 # commits on success, rolls back on error
                with conn.cursor() as cur:
                    work(cur)          # your reads + writes
            return                     # committed cleanly
        except SerializationFailure:
            conn.rollback()            # SQLSTATE 40001 — retry the whole txn
            continue
    raise RuntimeError("gave up after repeated serialization failures")

The retry loop is not optional at SERIALIZABLE. SSI's contract is: either your transaction runs as if serial, or it is aborted and you run it again. Code that assumes a transaction always succeeds will lose writes the first time contention spikes.

Common misconceptions and pitfalls

• "SERIALIZABLE just means slower." On a lock-based engine, yes — more and longer locks. On an MVCC engine with SSI, it means readers still don't block, but transactions can be aborted. The failure mode is retries, not waiting. Different tuning entirely.
• "REPEATABLE READ prevents lost updates." Only for updates to the same row (first-committer-wins). It does nothing for write skew or for read-modify-write logic done in application code across different rows.
• "Higher isolation fixes my race condition." Often the real fix is a smaller change: an explicit SELECT ... FOR UPDATE, a unique constraint, or an atomic UPDATE ... SET x = x + 1 that pushes the read-modify-write into the engine. Raising the global level to paper over one query is a blunt instrument.
• "The level name is portable." It isn't. Oracle SERIALIZABLE ≠ PostgreSQL SERIALIZABLE. Port an app between engines without re-checking isolation semantics and you inherit anomalies the previous engine forbade.
• "READ UNCOMMITTED is faster, so use it for reports." In PostgreSQL, READ UNCOMMITTED is silently promoted to READ COMMITTED — it has no dirty-read mode at all. Elsewhere, dirty reads on a report can show numbers that were rolled back and never existed.