# DDIA Ch. 8: read skew makes $100 vanish — until REPEATABLE READ ## Concept Aaliyah has $1000 across two bank accounts, $500 in checking and $500 in savings. She opens a transaction and reads checking — $500. Meanwhile another transaction moves $100 from savings to checking and commits. She then reads savings — $400. Her two reads sum to **$900**: $100 has apparently *vanished*. Nothing is corrupt and nobody lost money — she read checking *before* the transfer and savings *after* it, and under PostgreSQL's default **READ COMMITTED** each statement gets a fresh snapshot, so her single transaction saw two different points in time. This is **read skew**, a *non-repeatable read*. Re-run the identical interleaving under **REPEATABLE READ** and both reads come from the snapshot taken at her transaction's start: $500 + $500 = **$1000**, internally consistent. You will reproduce both on a real PostgreSQL 16 server, in two hand-typed `psql` sessions. ## Provenance *Designing Data-Intensive Applications*, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 8 — *Transactions*, §"Snapshot Isolation and Repeatable Read": > Aaliyah is not doing anything wrong here [...] the difference between the two > queries is only a fraction of a second. This anomaly is called a > *nonrepeatable read* or *read skew*. The book names the anomaly and its fix: snapshot isolation (which PostgreSQL calls REPEATABLE READ) gives each transaction a consistent view of the database as of the moment it started. Here you trigger the anomaly under the default level, then close it by raising the isolation level one notch. ## Prerequisites The surprise is entirely about *when* a read's snapshot is taken. These help; each line notes where it shows up. 1. **Isolation levels** — the SQL standard's dial (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) trading anomalies for cost; PostgreSQL defaults to READ COMMITTED. It shows up as the `BEGIN ISOLATION LEVEL …` you type in Session A. 2. **Snapshot isolation** — a transaction reads from a consistent snapshot of all committed data as of some instant, ignoring writes committed later. It shows up as REPEATABLE READ giving $1000 even after the transfer commits. 3. **Non-repeatable read (read skew)** — reading the same logical data twice in one transaction and getting two different answers because something committed in between. It shows up as $500 + $400 = $900 under READ COMMITTED. ### Where to learn the prerequisites - **Isolation levels and snapshot isolation (#1–#2):** DDIA §"Weak Isolation Levels" and §"Snapshot Isolation and Repeatable Read"; the PostgreSQL manual, ch. 13 "Concurrency Control" → "Transaction Isolation". - **What each level actually forbids (#3):** the PostgreSQL manual's isolation table (READ COMMITTED permits non-repeatable and phantom reads; REPEATABLE READ forbids both in PostgreSQL's implementation). If only one is new, make it #2 — a per-transaction snapshot is the whole mechanism that turns $900 back into $1000. ## Environment The anomaly is 100% reproducible — the interleaving is fixed, not a race: PostgreSQL 16.14 (Debian, aarch64) in Docker on macOS 26.5.2 (Darwin 25.5.0), arm64; captured via psycopg[binary] on Python 3.12 (uv); deterministic. The reader-facing steps use two manual `psql` sessions against the same server; `code/read_skew.py` is the capture harness that drives the identical two-session interleaving programmatically and prints the numbers quoted below. ## Mental model One server, two accounts, two concurrent transactions. Only Session A's isolation level changes between the two runs. - **READ COMMITTED = a fresh snapshot per statement.** Each `SELECT` sees everything committed *as of the moment that statement began*. So if a transaction reads checking, waits while another transaction commits a transfer, then reads savings, the second read sees the *post-transfer* world while the first saw the *pre-transfer* one. The two reads are consistent with no single moment — that gap is read skew. - **REPEATABLE READ = one snapshot per transaction.** The snapshot is frozen at the transaction's first query, and every later read in that transaction is answered from it. A transfer that commits afterward is simply invisible, so the two reads always agree with one instant — the start of the transaction. Both runs use the same tables and the same interleaving; the capture harness is one script, `code/read_skew.py`. The Steps you run by hand in two `psql` sessions. ## Setup The server is already running (a shared `postgres:16` container). Give yourself your own database and two accounts, then open **two** `psql` sessions side by side — call them **Session A** (the reader) and **Session B** (the transfer). ``` # one-time: the shared server, if you are standing your own up docker run -d --name ddia-ch08-pg -e POSTGRES_HOST_AUTH_METHOD=trust \ -p 5432:5432 postgres:16 # your own database + seed data psql -h localhost -U postgres -d postgres \ -c "DROP DATABASE IF EXISTS ex_ch08_readskew; CREATE DATABASE ex_ch08_readskew;" psql -h localhost -U postgres -d ex_ch08_readskew <<'SQL' CREATE TABLE accounts (id int PRIMARY KEY, name text, balance int); INSERT INTO accounts VALUES (1, 'checking', 500), (2, 'savings', 500); SQL # two sessions, one per terminal: # Session A (reader): psql -h localhost -U postgres -d ex_ch08_readskew # Session B (transfer): psql -h localhost -U postgres -d ex_ch08_readskew ``` The capture harness `code/read_skew.py` does all of this itself (drop + recreate + seed + both interleavings) and is what produced the numbers below: ``` uv run --python 3.12 --with "psycopg[binary]" python3 code/read_skew.py ``` Do **not** read ahead — make each prediction first. ## Steps ### Step 1 — read skew under READ COMMITTED (the surprise) **Predict.** In **Session A**, open a READ COMMITTED transaction and read checking. Then, in **Session B**, transfer $100 from savings to checking and commit. Back in **Session A** (still the same transaction), read savings. What does each read return, and what do Aaliyah's two reads *sum* to? ``` -- Session A: open the transaction, read checking BEGIN ISOLATION LEVEL READ COMMITTED; SELECT balance FROM accounts WHERE id = 1; -- checking ``` ``` -- Session B: move $100 savings -> checking, commit BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 2; UPDATE accounts SET balance = balance + 100 WHERE id = 1; COMMIT; ``` ``` -- Session A: same transaction, now read savings SELECT balance FROM accounts WHERE id = 2; -- savings ``` **Observe.** Session A prints: ``` balance --------- 500 (1 row) balance --------- 400 (1 row) ``` **$500 + $400 = $900 — $100 has apparently vanished.** Session A never left its transaction, yet its two reads disagree about a moment in time: it read checking *before* the transfer landed and savings *after*. Each `SELECT` under READ COMMITTED took a fresh snapshot, and Session B's commit slipped into the gap between them. ### Step 2 — prove the snapshot is per-statement **Predict.** Session A is *still* inside the same READ COMMITTED transaction. Re-read checking. Will it show the $500 it read in Step 1, or the $600 that Session B committed? ``` -- Session A: still the same transaction, re-read checking SELECT balance FROM accounts WHERE id = 1; -- checking again ``` **Observe.** ``` balance --------- 600 (1 row) ``` **$600 — the committed value, not the $500 it saw seconds ago.** This is the mechanism laid bare: under READ COMMITTED the *same query* in the *same transaction* returns a different answer because each statement re-snapshots. The first read wasn't wrong and the third isn't either; there simply is no single snapshot tying them together. `ROLLBACK;` when you're done — Session A only read. ### Step 3 — the fix: REPEATABLE READ **Predict.** Reset both accounts to $500. Now run the *identical* interleaving, but open Session A with `ISOLATION LEVEL REPEATABLE READ`. After Session B's transfer commits, what does Session A read for savings — and what do its two reads sum to? ``` -- (reset) any session: UPDATE accounts SET balance = 500; -- Session A: REPEATABLE READ this time BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT balance FROM accounts WHERE id = 1; -- checking -> ? -- Session B: same transfer as before, commit -- Session A: read savings, then re-read checking SELECT balance FROM accounts WHERE id = 2; -- savings -> ? SELECT balance FROM accounts WHERE id = 1; -- checking -> ? ``` **Observe.** Session A prints, for checking, savings, checking: ``` balance --------- 500 (1 row) balance --------- 500 (1 row) balance --------- 500 (1 row) ``` **$500 + $500 = $1000, and checking is still $500 on the re-read.** The transfer *did* commit — but Session A's snapshot was frozen at its first query, so Session B's later commit is invisible to it. Every read in the transaction agrees with a single instant: the start. The total is consistent again. ## What you should see - **READ COMMITTED:** Session A reads checking **500**, then savings **400** → sum **900**; a re-read of checking then shows **600**. Read skew. - **REPEATABLE READ:** Session A reads checking **500**, savings **500**, and checking again **500** → sum **1000**. No skew. - The harness summary line: `READ COMMITTED … 900 (read skew)` / `REPEATABLE READ … 1000 (consistent)`. ## Why Read skew is a timing anomaly, not a corruption. The database is always internally consistent — the transfer debits savings and credits checking as one atomic unit, so the true total is $1000 at every committed instant. What breaks is a *reader* that takes more than one snapshot inside a single logical operation. Under READ COMMITTED, PostgreSQL takes a **new snapshot at the start of every statement**: `SELECT balance WHERE id=1` sees the world as of its own start, and the *next* `SELECT balance WHERE id=2`, milliseconds later, sees a *different, newer* world. If a transfer commits in that sub-millisecond gap, the first read reflects the pre-transfer database and the second the post-transfer one. Add them and you are summing two different points in time — $500 (before) + $400 (after) = $900 — which corresponds to no real state the accounts ever held. Step 2 makes the cause undeniable: re-reading checking in the *same* transaction returns $600, because that third statement snapshots again and this time the commit is already there. REPEATABLE READ fixes it by taking **one snapshot per transaction**, fixed at the first query, and answering every subsequent read from it. This is snapshot isolation: the transaction reads a consistent, frozen view of all data committed as of that instant, and ignores anything committed later. Session B's transfer still happens and still commits — but it committed *after* Session A's snapshot, so it is simply not in Session A's view. Both reads therefore describe the same instant (the start), and $500 + $500 = $1000 every time. PostgreSQL implements this with MVCC: each row version is tagged with the transaction that created it, and a transaction's snapshot determines which versions are visible — READ COMMITTED recomputes visibility per statement, REPEATABLE READ computes it once. **The boundary — read skew needs *multiple* reads in *one* transaction.** A single statement can never see skew: even under READ COMMITTED it runs against one snapshot, so `SELECT sum(balance) FROM accounts` always returns $1000 regardless of isolation level. The anomaly only appears when a transaction reads, pauses, and reads again, and a concurrent commit lands in the pause. That is why the fix is either "raise the isolation level so all your reads share one snapshot" or "do the read in a single statement." It also bounds the danger: purely single-statement reads (most OLTP point lookups) are immune; long-running, multi-read transactions (analytics, backups, consistency checks) are exactly the ones that need REPEATABLE READ. ### Go deeper 1. **Single-statement immunity.** Under READ COMMITTED, run the whole read as one query — `SELECT sum(balance) FROM accounts` — during a concurrent transfer. Predict whether it can ever return 900 (it cannot), and confirm read skew requires two separate reads. 2. **Write skew needs SERIALIZABLE.** REPEATABLE READ stops read skew but *not* write skew (two transactions each read a consistent snapshot, then each write based on what the other was about to change). Reproduce the on-call doctors example and watch it survive REPEATABLE READ but fail under SERIALIZABLE. 3. **See the row versions.** Add `SELECT xmin, balance FROM accounts` to each read and watch the `xmin` (creating transaction id) change on checking after the transfer under READ COMMITTED but stay fixed under REPEATABLE READ — MVCC visibility in the raw.