# DDIA Ch. 8: under MVCC, a writer doesn't wait for a reader — until you add FOR UPDATE ## Concept Two sessions, one row. Session A opens a long-lived `REPEATABLE READ` transaction and `SELECT`s row 1, fixing its snapshot. Session B then `UPDATE`s that same row and `COMMIT`s. Under lock-based (two-phase-locking) isolation you'd expect B to **block** until A finishes — A is "holding" the row. Under **multiversion concurrency control** it doesn't: B's `UPDATE` returns **immediately**, and A, still inside its transaction, keeps reading the **old** value from its snapshot. Readers never block writers and writers never block readers. Then flip **one** knob — change A's read to `SELECT … FOR UPDATE` — and B's `UPDATE` now **blocks** until A commits. Same two statements; the only difference is whether A took an explicit row lock or just a snapshot. ## Provenance *Designing Data-Intensive Applications*, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 8 — *Transactions*, §"Multiversion concurrency control": > readers never block writers, and writers never block readers. The book presents snapshot isolation as implemented by MVCC: the database keeps several committed versions of a row side by side, so a long-running read and a concurrent write touch *different* versions and never contend for the same lock. Here you watch exactly that — and then watch `FOR UPDATE` opt back into locking. ## Prerequisites The surprise is about which of two operations needs a lock the other is holding. These help; each line notes where it shows up. 1. **MVCC and row versions** — an `UPDATE` doesn't overwrite a row in place; it writes a *new* version and leaves the old one visible to transactions that started earlier. It shows up as A still seeing 100 after B wrote 200. 2. **Snapshots** — under `REPEATABLE READ`, a transaction reads the database as of a single point in time, fixed at its first query. It shows up as A's re-read returning the same value inside the transaction. 3. **Row locks vs snapshots** — a snapshot read acquires *no* row lock; a write locks only the row it modifies. Reads and writes therefore ask for different things. It shows up as B's `UPDATE` never waiting on A's `SELECT`. 4. **`SELECT … FOR UPDATE`** — an explicit read that *does* take the row's write lock, as if you were about to update it. It shows up as B's `UPDATE` blocking in Case 2. ### Where to learn the prerequisites - **MVCC, snapshots, and non-blocking reads (#1–#3):** DDIA §"Snapshot isolation and repeatable read" and §"Multiversion concurrency control"; the PostgreSQL manual, ch. "Concurrency Control" → "Transaction Isolation" and "13.3. Explicit Locking". - **`FOR UPDATE` (#4):** the PostgreSQL `SELECT` reference, "The Locking Clause". If only one is new, make it #3 — that a snapshot read takes no lock is the whole reason a reader and a writer never collide. ## Environment The blocking / non-blocking *behavior* is deterministic; the millisecond timings are wall-clock and illustrative: 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); timings are wall-clock (illustrative), the blocking/non-blocking behavior is deterministic. ## Mental model One row, `items(id=1, v=100)`, and two concurrent sessions. - **A plain snapshot read** sees a consistent view of the *committed* versions as of when its transaction began. It takes **no** row lock — it just picks the right version to look at. A concurrent writer is free to add a newer version next to it. - **A writer** never edits a row in place; it appends a **new** version and marks the old one superseded. Because the reader is looking at the old version and the writer is creating a new one, the two operations touch different data and never need the same lock. That is why neither blocks the other. - **`SELECT … FOR UPDATE`** is the opt-out: it takes the row's **write lock** even though it's "just" a read, announcing "I intend to update this." Now a second writer must wait for the lock — you've traded the snapshot's freedom for explicit serialization on that row. The exercise is two hand-typed `psql` sessions. The capture harness `code/mvcc_no_blocking.py` drives the same two sessions programmatically — Case 2 needs real threads and wall-clock timing to *prove* the block — so every number below is verbatim from a real run. ## Setup The shared Postgres 16 container is already running. Create a scratch database, seed one row, then open **two** `psql` sessions side by side. ``` # the substrate (already up; shown for completeness): # docker run -d --name ddia-ch08-pg -p 5432:5432 \ # -e POSTGRES_HOST_AUTH_METHOD=trust postgres:16 # create our own database and seed one row: docker exec ddia-ch08-pg psql -U postgres -c "CREATE DATABASE ex_ch08_mvcc;" docker exec ddia-ch08-pg psql -U postgres -d ex_ch08_mvcc -c \ "CREATE TABLE items (id int PRIMARY KEY, v int); INSERT INTO items VALUES (1, 100);" # Session A (leave this psql open): docker exec -it ddia-ch08-pg psql -U postgres -d ex_ch08_mvcc # Session B (a second terminal, same database): docker exec -it ddia-ch08-pg psql -U postgres -d ex_ch08_mvcc ``` The capture/verify path is `code/mvcc_no_blocking.py`: ``` cd study/ddia/ch08 uv run --python 3.12 --with "psycopg[binary]" python3 code/mvcc_no_blocking.py ``` Do **not** read the output yet — make each prediction first. ## Steps ### Step 1 — A fixes a snapshot; B writes the same row (the surprise) **Predict.** In **Session A**, open a `REPEATABLE READ` transaction and `SELECT` row 1 (this fixes A's snapshot). Then in **Session B**, `UPDATE` that same row and let it commit. Under two-phase locking B would wait for A. Under MVCC — does B's `UPDATE` block until A finishes, or return right away? ``` -- Session A: BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT v FROM items WHERE id = 1; -- Session B (autocommit: the statement commits on its own): UPDATE items SET v = 200 WHERE id = 1; ``` **Observe.** ``` -- Session A: v ----- 100 (1 row) -- Session B: UPDATE 1 -- returned in ~1.2 ms; did NOT wait for A ``` **B's `UPDATE` returns immediately.** It never asked for a lock A was holding — A holds no lock, only a snapshot. B appended a new version (v=200) right next to the old one (v=100) that A is still looking at. ### Step 2 — A re-reads inside the same transaction **Predict.** Still inside A's open transaction (B has already committed v=200), run the *same* `SELECT` again. Does A see B's new 200, or its snapshot's 100? ``` -- Session A (same transaction, after B committed): SELECT v FROM items WHERE id = 1; COMMIT; ``` **Observe.** ``` -- Session A: v ----- 100 (1 row) COMMIT ``` A fresh session, though, sees the committed value: ``` -- any new session: SELECT v FROM items WHERE id = 1; -> 200 ``` **A still sees 100.** Its snapshot was fixed at the first query, so it keeps reading the version that was committed then — B's newer version is simply not in A's view. B was never blocked *and* A's read stayed stable: both properties fall out of keeping multiple versions. ### Step 3 — flip one knob: `SELECT … FOR UPDATE` (now B blocks) **Predict.** Reset the row to 100. Redo Step 1 with a **single change**: A reads with `FOR UPDATE` instead of a plain `SELECT`. B then tries to `UPDATE` the same row. Does B still return immediately — or does it block now? ``` -- Session A: BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT v FROM items WHERE id = 1 FOR UPDATE; -- takes the row's write lock -- Session B: UPDATE items SET v = 300 WHERE id = 1; -- ... hangs, no output ``` **Observe.** B's prompt hangs — no `UPDATE 1`, nothing — for as long as A holds the transaction open. Only when A commits does B unblock: ``` -- Session A, after holding ~1.0 s: COMMIT -- Session B unblocks the instant A commits: UPDATE 1 -- finally returned in ~1010 ms ``` **Now B blocks.** `FOR UPDATE` made A's "read" acquire the row's write lock, so B's `UPDATE` has to wait for the same lock. One added clause turned a non-contending pair into a serialized one. ### Step 4 — measure the block **Predict.** The harness measures B's `UPDATE` latency in both cases (A holds the `FOR UPDATE` lock for a fixed 1.0 s before committing). How far apart are the two numbers? **Observe.** ``` === Case 1: plain read (REPEATABLE READ snapshot) -- non-blocking === Session A BEGIN (REPEATABLE READ); SELECT v WHERE id=1 -> 100 Session B UPDATE v=200; COMMIT -> returned in 1.2 ms (did NOT wait for A) Session A re-SELECT v WHERE id=1 (same txn) -> 100 (snapshot: still the old version) committed value now = 200 (B's write is durable) === Case 2: SELECT ... FOR UPDATE -- explicit row lock, writer blocks === Session A BEGIN; SELECT v WHERE id=1 FOR UPDATE -> 100 (row now locked) Session B UPDATE v=300 -> BLOCKED (thread alive = True while A holds the lock) Session A COMMIT -> lock released after ~1.0s hold Session B UPDATE v=300; COMMIT -> finally returned in 1010 ms (waited > the 1.0s hold) committed value now = 300 (B's write applied after A) ``` **~1 ms vs ~1010 ms** — three orders of magnitude. In Case 1 B never waited; in Case 2 B waited essentially the entire time A held the lock (`> the 1.0 s hold`), because it *was* waiting for that lock. ## What you should see - Case 1: B's `UPDATE` returns immediately (**~1 ms**); A's re-read inside its transaction still shows **100** while the committed value is **200**. - Case 3/flip: with `FOR UPDATE`, B's `UPDATE` **blocks** and returns only after A commits — **~1010 ms**, i.e. `> the ~1.0 s` A held the lock. - The behavior (non-blocking vs blocking) is deterministic; only the millisecond figures vary run to run. ## Why An `UPDATE` in an MVCC database does not mutate a row in place. It writes a **new version** of the row, stamped with the writing transaction's id, and leaves the previous version physically present and marked as superseded as of that commit. Every transaction carries a snapshot — under `REPEATABLE READ`, fixed at its first statement — and when it reads a row it walks the version chain and picks the newest version that was committed *before* its snapshot. So in Case 1 there are, for a moment, two versions of row 1: the old `v=100` that A's snapshot selects, and the new `v=200` that B created. A reads one; B wrote the other. They never reference the same bytes, so there is nothing to lock against each other — that is the mechanical reason "readers never block writers, and writers never block readers." The reader isn't granted priority over the writer or vice versa; the two operations were simply arranged never to need the same resource. `SELECT … FOR UPDATE` deliberately gives that up. It is a read that acquires the row's **write lock** — the same lock an `UPDATE` takes — announcing an intent to modify. Now A's "read" and B's `UPDATE` *do* want the same lock, so PostgreSQL serializes them: B waits until A's transaction ends and releases it. You reach for `FOR UPDATE` precisely when snapshot freedom is the *wrong* thing — e.g. read a balance you're about to decrement and you need no one else changing it in between. The knob you flipped is exactly the boundary between snapshot isolation's optimism and explicit pessimistic locking. **The boundary — MVCC removes reader/writer contention, not writer/writer.** Two concurrent *writers* to the same row still block each other, and must: if both tried to append a new version of row 1 at once, one has to win and the other wait (then either see the new version or abort on a conflict). MVCC lets a reader and a writer proceed in parallel because they can be handed different versions; it cannot do the same for two writers, because a row can only have one *next* committed version. `SELECT … FOR UPDATE` blocking a writer is really this same writer/writer rule — `FOR UPDATE` makes A a writer, for locking purposes, before it has written anything. So the guarantee is precise: snapshots dissolve the read-vs-write conflict; they never dissolve the write-vs-write one. ### Go deeper 1. **Watch the wait in the catalog.** While B is blocked in Case 2, run `SELECT pid, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state='active';` in a third session — B shows `Lock` / `transactionid`. Then `SELECT * FROM pg_locks WHERE NOT granted;` to see the ungranted lock. 2. **Make A abort instead of commit.** In Case 2, `ROLLBACK` A instead of `COMMIT`. Predict whether B's `UPDATE` still applies (it does — B just waited for the lock, not for A's *outcome*), and what final value the row holds. 3. **Two plain writers.** Drop `FOR UPDATE` and instead have *both* sessions `UPDATE` row 1 inside open transactions. Predict that the second `UPDATE` blocks — confirming MVCC only spares the reader/writer pair, not writer/writer. 4. **`FOR UPDATE` vs `FOR SHARE`.** Change A's lock to `FOR SHARE` and have B run another `FOR SHARE` read, then a real `UPDATE`. Predict which combination blocks — shared read locks coexist, but a write still waits.