Postgres Won't Dirty-Read Even When You Beg It To

The SQL standard's weakest level, READ UNCOMMITTED, is defined by permitting dirty reads. Ask Postgres for it explicitly, have one session issue an uncommitted UPDATE, and read it from another — and you still get the old committed value. Postgres honors the level by name (SHOW transaction_isolation answers read uncommitted) but runs READ COMMITTED underneath. The one anomaly the level is named for is un-reproducible here.

Concept

The SQL standard defines a weakest isolation level, READ UNCOMMITTED, whose defining property is that it permits dirty reads: a transaction may see another transaction's writes before they commit. So ask Postgres for it explicitly, have one session issue an uncommitted UPDATE, and a second session — also at READ UNCOMMITTED — should be able to read the uncommitted value. It can't. Postgres accepts the level by name (SHOW transaction_isolation even answers read uncommitted), yet the second session still reads the old committed value. The one anomaly the SQL standard's weakest level is named for is un-reproducible on Postgres: READ UNCOMMITTED behaves exactly like READ COMMITTED.

Provenance

Designing Data-Intensive Applications, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 8 — Transactions, §"Read Committed" (the read-uncommitted paragraph) and §"Implementing read committed":

There are some databases that don't even implement read committed... some that do implement "read uncommitted" but which actually behave like read committed.

The book notes that the standard's isolation levels are defined by which anomalies they forbid, and that real databases diverge from those names. Postgres is the clean example: it offers READ UNCOMMITTED for standards compatibility but never actually exposes uncommitted data. Here you try to force the dirty read and watch it refuse.

Prerequisites

The surprise is about the gap between the named isolation levels and what a given engine actually implements. These help; each line notes where it shows up.

1. The SQL standard's four isolation levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE, each defined by which anomalies it forbids. READ UNCOMMITTED is the only one that permits dirty reads. It shows up as the level Session A and B both request.
2. What Postgres actually implements — only three distinct behaviors: READ COMMITTED (the default), REPEATABLE READ (snapshot isolation), and SERIALIZABLE (SSI). READ UNCOMMITTED is accepted but silently mapped to READ COMMITTED. It shows up as the dirty read never happening.
3. Dirty read — reading a row another transaction has written but not yet committed. It shows up as the value Session B is trying (and failing) to see: A's uncommitted 400.
4. MVCC (multi-version concurrency control) — Postgres gives each statement a snapshot of rows as of the last commit; an uncommitted UPDATE writes a new row version that is simply invisible to other snapshots. It shows up as why the dirty read is impossible, not merely disallowed.

Mental model

Two psql sessions against one database, one row: accounts(id=1, balance=500), committed. Session A is the writer: it opens a READ UNCOMMITTED transaction and runs UPDATE accounts SET balance = balance - 100 — but does not commit, so 400 exists only inside A's transaction. Session B is the reader: it also opens a READ UNCOMMITTED transaction and SELECTs the balance while A's write is still pending. Under the SQL standard's definition of READ UNCOMMITTED, B is allowed to see A's uncommitted 400. The question is whether Postgres lets it.

The friction is the point: you type this in two terminals by hand and watch one session's uncommitted write stay invisible to the other. The Python file code/dirty_read_impossible.py is the capture harness the maintainer re-runs to verify the transcripts — it is not the path you take here.

Setup

Start a throwaway Postgres and seed one committed row:

docker run -d --name ddia-ch08 \
  -e POSTGRES_PASSWORD=study -e POSTGRES_HOST_AUTH_METHOD=trust \
  -p 5432:5432 postgres:16

# seed accounts(id=1, balance=500), committed:
psql -h localhost -U postgres -d postgres \
  -c "CREATE TABLE accounts (id int PRIMARY KEY, balance int);" \
  -c "INSERT INTO accounts VALUES (1, 500);"

Now open two terminals:

# terminal 1 -- Session A (the writer):
psql -h localhost -U postgres -d postgres

# terminal 2 -- Session B (the reader):
psql -h localhost -U postgres -d postgres

Do not read ahead — make each prediction before you run the step.

BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SHOW transaction_isolation;
SELECT balance FROM accounts WHERE id = 1;

COMMIT;

SELECT balance FROM accounts WHERE id = 1;

What you should see

• Session A's BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is accepted (no error, no warning); the UPDATE reports UPDATE 1.
• In Session B, SHOW transaction_isolation returns read uncommitted — the level is honored by name.
• Yet B's read while A is pending returns 500, not 400: no dirty read.
• After A commits, B's next read returns 400.

Why

The SQL standard defines its four isolation levels phenomenologically — by which of three anomalies (dirty read, non-repeatable read, phantom) each one is allowed to exhibit. READ UNCOMMITTED sits at the bottom: it is the level defined by being permitted to dirty-read. But "permitted" is not "required." A database is free to forbid more anomalies than the level demands, and if forbidding them is cheaper or simpler than allowing them, it will.

That is exactly Postgres's situation, and it falls out of MVCC. Under multi-version concurrency control, an UPDATE never overwrites a row in place; it writes a new version of the row stamped with the writing transaction's id, leaving the old version intact. Every statement reads against a snapshot that includes only versions from transactions that had committed as of when the snapshot was taken. A's uncommitted 400 is a row version whose creating transaction hasn't committed, so it fails the snapshot's visibility test for every other transaction — there is no code path that would return it. To support dirty reads, Postgres would have to add machinery to deliberately reach past the snapshot and expose uncommitted versions. Nobody wants that, so it was never built: Postgres implements only three distinct behaviors (read committed, repeatable read, serializable), and maps the fourth name onto the nearest one it has. SHOW still echoes read uncommitted because Postgres faithfully records the level you asked for — it just runs read-committed semantics underneath.

So the "downgrade" isn't a policy decision bolted on top; it's that the weakest standard level asks for less isolation than Postgres's cheapest implementation already provides. You cannot buy a weaker guarantee here because there is no weaker product on the shelf.

Go deeper

• Prove it's really read-committed, not repeatable-read. Keep B's transaction open across two of A's committed writes: SELECT in B, have A UPDATE ... ; COMMIT, then SELECT in B again in the same transaction. Under read committed B's second read changes; under repeatable read it wouldn't. Confirm READ UNCOMMITTED gave you read-committed, not something stronger.
• Ask for the level Postgres does pin. Rerun Step 2 with BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ in B and repeat the go-deeper #1 interleaving. Predict which read now stops changing — and connect it to the snapshot being taken once per transaction instead of once per statement.
• Cross-engine. Start mysql:8 with the same table and run the identical A/B sessions at READ UNCOMMITTED. Predict B's reading of A's pending 400 before you run it, then watch InnoDB actually dirty-read — the same name, the opposite behavior.

Sources: DDIA 2e, Ch. 8, §"Read Committed", §"Implementing read committed" · PostgreSQL 16 manual, Transaction Isolation and Concurrency Control.