50. Isolation levels

Why isolation needs levels, not just on/off

The ACID "I" seems simple — concurrent transactions shouldn't interfere with each other. In practice, "shouldn't interfere" has several genuinely different, independently useful meanings, each with a different performance cost. SQL defines four standard isolation levels; Postgres implements three of them distinctly (its READ UNCOMMITTED behaves identically to READ COMMITTED — Postgres never allows dirty reads at any level, stronger than the standard strictly requires).

The anomalies, defined precisely

• Dirty read — seeing another transaction's uncommitted changes. Postgres never allows this, at any isolation level — worth stating plainly since some other engines do allow it at their weakest level.
• Non-repeatable read — reading the same row twice in one transaction and getting different values, because another transaction committed a change in between.
• Phantom read — re-running the same filtered query twice in one transaction and getting a different set of rows (not just different values in existing rows), because another transaction committed an insert/delete matching that filter in between.
• Serialization anomaly — the result of running transactions concurrently is not equivalent to any possible serial (one-at-a-time) ordering of those same transactions — a subtler, more general anomaly than the three above.

Read Committed — Postgres's default

Each statement within a transaction sees a fresh snapshot of committed data, taken at the moment that statement starts. This means two SELECTs of the same row, run back-to-back in the same transaction, can return different values if another transaction committed a change in between — a non-repeatable read, allowed at this level.

BEGIN;   -- isolation level defaults to READ COMMITTED
SELECT balance FROM balance_demo WHERE id = 1;   -- sees committed value as of THIS statement
-- ... time passes, another session commits a change ...
SELECT balance FROM balance_demo WHERE id = 1;   -- may show the NEW value now
COMMIT;

This is a reasonable, cheap default for most application code — each statement gets an internally consistent view, and the common case (a short transaction, one logical operation) rarely notices or cares that a different statement later in the same transaction might see newer data.

Repeatable Read — one snapshot for the whole transaction

The entire transaction sees one consistent snapshot, taken at the transaction's first statement — every subsequent read within that transaction sees data exactly as of that moment, regardless of what other transactions commit in the meantime. This eliminates non-repeatable reads and phantom reads entirely, by construction:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM balance_demo WHERE id = 1;   -- sees value as of transaction START
-- ... another session commits a change in the meantime ...
SELECT balance FROM balance_demo WHERE id = 1;   -- STILL shows the ORIGINAL value
COMMIT;
-- only a NEW transaction after this COMMIT would see the other session's change

The cost: if this transaction later tries to write something that conflicts with a change committed by another transaction during its snapshot window, Postgres raises a serialization failure at commit time rather than silently allowing an inconsistent write — the application must be prepared to retry.

Serializable — the strongest guarantee

All the guarantees of Repeatable Read, plus protection against serialization anomalies — Postgres actively tracks read/write dependencies between concurrent SERIALIZABLE transactions and aborts one (with a serialization failure) if letting both commit would produce a result impossible under any serial ordering. This is strictly the safest option and also the one most likely to require your application to retry a transaction — a real, direct tradeoff between correctness guarantees and how often a transaction has to be retried under contention.

Choosing a level in practice

The practical rule: default to Read Committed (Postgres's default, and correct for the overwhelming majority of application code). Reach for Repeatable Read specifically when a transaction reads a value, computes something from it, then writes based on that computation — a classic case being "check inventory count, then decrement it," where acting on stale data would be a real bug. Reach for Serializable only when you've identified a genuine multi-row, multi-transaction correctness requirement that weaker levels can't guarantee, since it carries the highest retry burden.

Try it

This lesson needs two concurrent sessions to demonstrate for real — a single query can't show one transaction observing another's in-flight commit. To try it yourself, open two psql sessions side by side and step each transaction through in turn; a pg_sleep in each keeps them deterministically interleaved even without perfect manual timing. (These are the transaction concepts; the in-browser runner here executes one statement stream at a time, so it can't stage two live sessions.)

Check yourself

1. Does Postgres allow dirty reads at any isolation level? What about the SQL standard in general?
2. Under Read Committed, can two SELECTs of the same row in one transaction return different values? Under Repeatable Read?
3. What's the practical cost of choosing Serializable over Repeatable Read for a given piece of application logic?