study › DDIA › Chapter 8 · Transactions

Under MVCC a Writer Doesn't Wait for a Reader — Until You Add FOR UPDATE

Session A opens a long-lived REPEATABLE READ transaction and SELECTs row 1, fixing its snapshot. Session B UPDATEs the same row and commits. Under two-phase locking B would block; under MVCC B returns in ~1 ms and A keeps reading its old snapshot value. Flip one knob — SELECT … FOR UPDATE — and B's UPDATE now blocks ~1010 ms until A commits.

Concept

Two sessions, one row. Session A opens a long-lived REPEATABLE READ transaction and SELECTs row 1, fixing its snapshot. Session B then UPDATEs that same row and COMMITs. 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.

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.
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.
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.
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 these numbers came from 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.

Step 1 · A fixes a snapshot; B writes the same row

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;

Does B's UPDATE block, or return immediately?

-- 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;

Does A see 200 or 100?

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

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

Does B still return immediately, or block?

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?

How far apart are the two UPDATE latencies?

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

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

Sources: DDIA 2e, Ch. 8, §"Snapshot isolation and repeatable read", §"Multiversion concurrency control" · PostgreSQL 16 manual, "Concurrency Control" and the SELECT "Locking Clause".