# DDIA Ch. 8: Postgres won't dirty-read even when you beg it to

## 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.

### Where to learn the prerequisites

- **The four standard levels and the anomalies (#1, #3):** DDIA §"Weak
  Isolation Levels" and §"Read Committed" — and free, the PostgreSQL manual
  chapter *Transaction Isolation*
  (https://www.postgresql.org/docs/16/transaction-iso.html), whose first table
  maps each requested level to the behavior Postgres gives it.
- **MVCC (#4):** DDIA §"Implementing read committed" / §"Snapshot Isolation and
  Repeatable Read"; PostgreSQL manual *Concurrency Control*
  (https://www.postgresql.org/docs/16/mvcc.html).

If only one is new, make it #2 — the whole aha is that the *name* is honored
while the *behavior* is a stronger level's.

## Environment

The anomaly's *absence* is 100% reproducible; there is no timing to catch.

PostgreSQL 16.14 (Debian, aarch64) in Docker on macOS 26.5.2 (Darwin 25.5.0),
arm64; transcripts captured via psycopg[binary] on Python 3.12 (uv), two
sessions against one database; deterministic.

## 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 `SELECT`s 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.

## Steps

### Step 1 — Session A writes, without committing

**Predict.** In Session A, open a READ UNCOMMITTED transaction and subtract 100,
but do not commit. Does Postgres accept the `READ UNCOMMITTED` level, or reject
it / warn?

**Do (Session A):**

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

**Observe (Session A).**

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

Postgres accepts `READ UNCOMMITTED` without complaint. A now privately holds
`balance = 400`; nothing is committed. Leave this transaction **open** and
switch to terminal 2.

### Step 2 — Session B reads while A's write is pending (the surprise)

**Predict.** In Session B, also at READ UNCOMMITTED, first confirm the level
with `SHOW transaction_isolation`, then read the balance *while A's UPDATE is
still uncommitted*. What does `SHOW` report — and does B see **400** (A's
uncommitted write — the dirty read) or **500** (the old committed value)?

**Do (Session B):**

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

**Observe (Session B).**

```
postgres=# BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN
postgres=*# SHOW transaction_isolation;
 transaction_isolation 
-----------------------
 read uncommitted
(1 row)

postgres=*# SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     500
(1 row)
```

**500, not 400 — no dirty read.** And the twist: `SHOW transaction_isolation`
cheerfully answers `read uncommitted`, so Postgres isn't rejecting or rewriting
your request — it accepted the weakest level *by name*. Yet A's uncommitted
write is still completely invisible. The level you asked for is the one thing
that *should* let this dirty read through, and it doesn't.

### Step 3 — A commits; B reads again

**Predict.** Back in Session A, `COMMIT`. Then in Session B (a new statement),
`SELECT` the balance again. Now what does B see?

**Do (Session A):**

```sql
COMMIT;
```

**Do (Session B):**

```sql
SELECT balance FROM accounts WHERE id = 1;
```

**Observe.**

```
-- Session A:
postgres=*# COMMIT;
COMMIT

-- Session B:
postgres=*# SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     400
(1 row)
```

**Now B sees 400.** The write was never lost or hidden by a bug — it was simply
*uncommitted*, and Postgres only ever shows other transactions **committed**
data. The moment A commits, B's next statement sees it. That is precisely READ
COMMITTED behavior — which is what you got the whole time, under the name READ
UNCOMMITTED.

## 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.

**The boundary — this is a Postgres fact, not a SQL fact.** On engines whose
storage *does* read live/in-place rows, READ UNCOMMITTED means what the standard
says. Older MySQL/InnoDB is the classic counter-demo: run the identical two
sessions with `SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED` and Session B
*will* read Session A's uncommitted 400 — a real dirty read. Same SQL, same
level name, opposite result. The isolation-level *name* is a contract about
anomalies; the *behavior* is whatever the engine's concurrency-control
implementation happens to deliver, and only the manual's own mapping table tells
you which. Never reason about isolation from the level name alone.

### Go deeper

1. **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.
2. **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.
3. **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.