Transaction Isolation Levels, and What Postgres Actually Does

Two customers click "buy" on the last concert ticket at the same instant. Both transactions check inventory, both see one ticket left, both proceed, and you've sold a seat that doesn't exist. That is a concurrency anomaly, and the setting that governs whether your database lets it happen is the transaction isolation level. It's one of the most consequential knobs in any database, and also one of the most misunderstood — partly because what your database actually does often differs from what the SQL standard says.

The three anomalies

The SQL standard defines isolation levels not by how they work, but by which read anomalies they forbid. There are three classic ones, and the PostgreSQL documentation defines them crisply:

• Dirty read: "A transaction reads data written by a concurrent uncommitted transaction." You act on a value that may be rolled back and never actually existed.
• Non-repeatable read: "A transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the initial read)." Read the same row twice, get two different values.
• Phantom read: "A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed." Run SELECT ... WHERE status='pending' twice and new matching rows appear.

There's also the broader serialization anomaly: the committed result of a group of transactions is inconsistent with any order of running them one at a time. The ticket oversell is this kind of problem.

The four standard levels

The SQL standard defines a ladder of four levels, each forbidding more anomalies than the last. This is Table 13.1 from the Postgres docs, which lists what the standard permits:

Each step up the ladder buys you stronger consistency at the cost of concurrency — higher levels do more locking or more conflict-checking, so they can reduce throughput and introduce transaction failures. The rule of thumb is to use the lowest level that still protects the invariant you care about.

What PostgreSQL actually does

Here is where reality diverges from the standard, and the asterisks above come in. Per the docs: "In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented" — PostgreSQL's Read Uncommitted mode behaves like Read Committed. Because Postgres uses MVCC (multi-version concurrency control), a transaction never sees uncommitted data, so dirty reads simply cannot occur, even if you ask for the weakest level.

Three more things worth knowing:

• Read Committed is the default. Every statement sees a fresh snapshot taken at the start of that statement, so two statements in one transaction can see different committed data.
• Repeatable Read is stricter than the standard requires. The docs note "PostgreSQL's Repeatable Read implementation does not allow phantom reads" — its MVCC snapshot is taken once at the start of the transaction, so the whole transaction sees a single frozen view of the database. That's why the phantom-read cell is marked "not possible in PG." It can still hit serialization anomalies like write skew, though.
• Serializable uses Serializable Snapshot Isolation (SSI) — it builds on snapshot isolation by adding runtime checks for serialization anomalies, giving you true serial-equivalent correctness without heavy locking.

-- Illustrative: pin a consistent snapshot for a multi-statement report
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT sum(amount) FROM orders WHERE day = current_date;
-- ... more reads; all see ONE frozen snapshot, immune to concurrent commits ...
COMMIT;

Choosing a level in practice

For most workloads, the Read Committed default is the right answer: it prevents the truly dangerous dirty reads while keeping concurrency high. Reach higher deliberately:

• Use Repeatable Read when a single transaction runs several reads that must agree with each other — a financial report, a multi-query export, anything where a snapshot must stay still while you work.
• Use Serializable when correctness depends on an invariant across concurrent writers — inventory counts, double-booking, balance checks. But there's a catch: under Serializable (and Repeatable Read on write conflicts), Postgres may abort a transaction with a serialization failure (SQLSTATE 40001) rather than allow an anomaly. Your application must be prepared to catch that error and retry the transaction. That retry loop is the price of the strongest guarantee.

Key Takeaways

• Isolation levels are defined by which anomalies they forbid: dirty read, non-repeatable read, phantom read, and serialization anomaly.
• The standard ladder is Read Uncommitted → Read Committed → Repeatable Read → Serializable, each stricter and less concurrent.
• PostgreSQL implements only three distinct levels — Read Uncommitted acts like Read Committed (MVCC means no dirty reads ever), and Read Committed is the default.
• Postgres Repeatable Read is stricter than the standard and prevents phantom reads via a transaction-wide snapshot; Serializable uses SSI.
• At Serializable, be ready to catch serialization failures and retry — that's the cost of serial-equivalent correctness.

References

• PostgreSQL Documentation — Transaction Isolation
• Transaction Isolation in Postgres, explained — Nile
• PostgreSQL Transaction Isolation Levels & MVCC — Mydbops

DatabasesPostgreSQLTransactionsConcurrencyMVCC