# DDIA Ch. 8: the lost update — two increments, final value off by one ## Concept A counter row holds `value = 42`. Two sessions each want to add one, and each does it the obvious application way: `SELECT` the value, add one *in your own code*, `UPDATE` it back. Run them concurrently and the final value is **43, not 44** — one increment silently vanishes. Nothing errors; no constraint is violated; the row just quietly ends up one short. Then you close the anomaly three ways and predict which actually work: an **atomic** in-DB `UPDATE … SET value = value + 1` (safe even at READ COMMITTED → 44); **REPEATABLE READ**, where Postgres *detects* the write-write conflict and **aborts** the second transaction with SQLSTATE `40001` (you must catch it and retry); and `SELECT … FOR UPDATE`, which locks the row on read so the two sessions serialize (→ 44). Two of the three reach 44 without you thinking; the third reaches 44 only if you handle an error you might not expect. ## Provenance *Designing Data-Intensive Applications*, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 8 — *Transactions*, §"Preventing Lost Updates" and §"Automatically detecting lost updates": > The lost update problem occurs if an application reads some value from the > database, modifies it, and writes back the modified value (a read-modify-write > cycle). If two transactions do this concurrently, one of the modifications can > be lost, because the second write does not include the first modification. The book lays out the fixes in order: atomic write operations, explicit locking, and automatic lost-update detection (which snapshot-isolation databases like Postgres provide by aborting the loser). Here you trigger the loss on real Postgres, then watch each fix close it — including the one that closes it by *throwing an error you have to retry*. ## Prerequisites The surprise is about a window between a read and a write, and three different ways to eliminate it. Each line notes where it shows up. 1. **The read-modify-write cycle** — read a value, compute a new one in application code, write it back. The gap between the read and the write is where a concurrent transaction slips in. It shows up as Scenario 0's `SELECT` … (app adds 1) … `UPDATE`. 2. **Atomic write operations** — a single `UPDATE … SET value = value + 1` reads and writes inside one indivisible statement, so there is no application-visible gap to race in. It shows up as Fix 1. 3. **Explicit locking (`SELECT … FOR UPDATE`)** — take a row lock at read time; any other transaction that wants the same row must wait until you commit, which forces the two read-modify-writes to run one after another. It shows up as Fix 3. 4. **Snapshot-isolation conflict detection** — under REPEATABLE READ each transaction reads from a consistent snapshot; if it then tries to write a row a concurrent transaction already committed, Postgres aborts it with SQLSTATE `40001` rather than let it clobber. It shows up as Fix 2. ### Where to learn the prerequisites - **The lost update problem and its fixes (#1–#4):** DDIA §"Preventing Lost Updates" and §"Automatically detecting lost updates". - **How Postgres implements the fixes (#2–#4):** the PostgreSQL manual, ch. 13 "Concurrency Control" — §13.2 "Transaction Isolation" (what REPEATABLE READ guarantees and when it raises `40001`) and §13.3 "Explicit Locking" (`SELECT … FOR UPDATE`). If only one is new, make it #4 — the counter-intuitive fix is that REPEATABLE READ does **not** silently make the read-modify-write safe; it makes it *fail loudly* so you retry. ## Environment The lost update and all three fixes are 100% reproducible — the workload is fixed and each scenario reseeds `value = 42`: - PostgreSQL 16.14 (Debian, aarch64) in Docker on macOS 26.5.2 (Darwin 25.5.0), arm64 - Reader path: two manual `psql` sessions against one database (`ex_ch08_lostupdate`), `postgres` user, trust auth - Capture/verification path: `code/lost_update.py` via psycopg[binary] on Python 3.12 (uv); deterministic. It reproduces the identical interleavings the two psql sessions produce, so the maintainer can re-verify every number. ## Mental model Same counter, same two `+1` increments from a starting `value = 42`. Only *how* the two sessions coordinate differs. The true answer is always **44**. | Approach | What each session does | Coordination | Final value | | --- | --- | --- | --- | | **Scenario 0** — app read-modify-write, READ COMMITTED | `SELECT` → add 1 in app → `UPDATE value = 43` | none: both read 42, both write 43 | **43** — one increment lost | | **Fix 1** — atomic in-DB write | `UPDATE value = value + 1` | the DB reads+writes in one statement; no app gap | **44** | | **Fix 2** — REPEATABLE READ + retry | `SELECT` → add 1 in app → `UPDATE` | snapshot isolation aborts the loser (`40001`); you retry | **44** | | **Fix 3** — explicit lock | `SELECT … FOR UPDATE` → add 1 in app → `UPDATE` | the row lock blocks the 2nd session until the 1st commits | **44** | The trap sits in two of the rows. Scenario 0 *looks* correct — each session did read, add one, write — yet loses a write. And Fix 2 does **not** silently repair the same read-modify-write; it aborts one transaction, and you reach 44 only because you catch the error and run it again. The capture harness that verifies all four is `code/lost_update.py`. ## Setup The shared Postgres 16 container is already running for the Chapter 8 exercises. If you are standing it up yourself: ``` docker run -d --name ddia-ch08-pg -p 5432:5432 \ -e POSTGRES_HOST_AUTH_METHOD=trust postgres:16 ``` Create your own database and seed the counter (each statement separately — `CREATE DATABASE` cannot run inside a transaction block): ``` docker exec ddia-ch08-pg psql -U postgres -c "CREATE DATABASE ex_ch08_lostupdate;" docker exec ddia-ch08-pg psql -U postgres -d ex_ch08_lostupdate \ -c "CREATE TABLE counters (id int PRIMARY KEY, value int);" docker exec ddia-ch08-pg psql -U postgres -d ex_ch08_lostupdate \ -c "INSERT INTO counters VALUES (1, 42);" ``` Open **two** psql sessions side by side — these are sessions **A** and **B**: ``` docker exec -it ddia-ch08-pg psql -U postgres -d ex_ch08_lostupdate # session A docker exec -it ddia-ch08-pg psql -U postgres -d ex_ch08_lostupdate # session B ``` Before each step below, reset the counter: ``` UPDATE counters SET value = 42 WHERE id = 1; ``` Type the statements by hand, interleaving the two sessions exactly as shown. The blocking (Fix 3) and the abort (Fix 2) are the lesson — watching one session hang or error is what you can't get from reading. `code/lost_update.py` is the capture harness that verified every transcript; do **not** read its output before predicting. ## Steps ### Step 1 — the lost update (READ COMMITTED, the default) **Predict.** Both sessions run the obvious read-modify-write: `SELECT value` (each sees 42), add one in application code, then `UPDATE counters SET value = 43`. A commits, then B commits. What is the final `value` — and how many of the two increments survive? ``` -- SESSION A -- SESSION B BEGIN; SELECT value FROM counters WHERE id = 1; -- 42 BEGIN; SELECT value FROM counters WHERE id = 1; -- 42 UPDATE counters SET value = 43 -- app computed 42 + 1 WHERE id = 1; COMMIT; UPDATE counters SET value = 43 -- app computed 42 + 1 WHERE id = 1; COMMIT; SELECT value FROM counters WHERE id = 1; ``` **Observe.** (Each session's real psql output, in execution order.) ``` -- SESSION A: BEGIN; SELECT (reads 42); UPDATE SET value = 43; COMMIT BEGIN value ------- 42 (1 row) UPDATE 1 COMMIT -- SESSION B: BEGIN; SELECT (also reads 42); UPDATE SET value = 43; COMMIT BEGIN value ------- 42 (1 row) UPDATE 1 COMMIT -- FINAL: SELECT value FROM counters WHERE id = 1; value ------- 43 (1 row) ``` **43, not 44 — one increment gone.** Both sessions read the same 42, both computed 43, both wrote 43. B's write didn't build on A's; it overwrote it with a value derived from a *stale* read. Nothing errored. READ COMMITTED only promises B won't see A's *uncommitted* data — it says nothing about B's write being based on a value A has since changed. ### Step 2 — Fix 1: the atomic in-DB increment **Predict.** Drop the application read entirely. Each session runs a single `UPDATE counters SET value = value + 1 WHERE id = 1`. What is the final value now — and does it still matter that they run concurrently under READ COMMITTED? ``` -- SESSION A -- SESSION B BEGIN; UPDATE counters SET value = value + 1 WHERE id = 1; COMMIT; BEGIN; UPDATE counters SET value = value + 1 WHERE id = 1; COMMIT; ``` **Observe.** ``` -- SESSION A: BEGIN; UPDATE SET value = value + 1; COMMIT BEGIN UPDATE 1 COMMIT -- SESSION B: BEGIN; UPDATE SET value = value + 1; COMMIT BEGIN UPDATE 1 COMMIT -- FINAL: SELECT value FROM counters WHERE id = 1; -> 44 ``` **44 — both increments survive.** There is no application-visible gap between reading and writing: the database reads the current `value` and writes `value + 1` inside one indivisible statement, taking a row lock for its duration. If the two overlap, the second `UPDATE` simply reads the *already-incremented* value. Safe even at READ COMMITTED, because the read that feeds the write is the database's own, not your stale copy from seconds ago. ### Step 3 — Fix 2: REPEATABLE READ detects the conflict and aborts **Predict.** Keep the app-level read-modify-write from Step 1, but start each transaction with `ISOLATION LEVEL REPEATABLE READ`. Both `SELECT` 42; A updates to 43 and commits; then B tries to update to 43. Does REPEATABLE READ silently make this safe (final 44), or does B's `UPDATE` do something else? ``` -- SESSION A -- SESSION B BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT value FROM counters WHERE id = 1; -- 42 BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT value FROM counters WHERE id = 1; -- 42 UPDATE counters SET value = 43 WHERE id = 1; COMMIT; UPDATE counters SET value = 43 WHERE id = 1; -- ? ``` **Observe.** (Session B runs `\set VERBOSITY verbose` to reveal the SQLSTATE.) ``` -- SESSION A: BEGIN RR; SELECT (42); UPDATE SET value = 43; COMMIT BEGIN value ------- 42 (1 row) UPDATE 1 COMMIT ``` ``` -- SESSION B: BEGIN RR; SELECT (42); UPDATE SET value = 43 -> aborts, then RETRY BEGIN value ------- 42 (1 row) ERROR: 40001: could not serialize access due to concurrent update LOCATION: ExecUpdate, nodeModifyTable.c:2418 ROLLBACK BEGIN value ------- 43 (1 row) UPDATE 1 COMMIT ``` B does **not** silently overwrite — it **aborts** with SQLSTATE `40001`, `could not serialize access due to concurrent update`. REPEATABLE READ gave B a snapshot in which `value` is still 42; when B tried to write a row that a concurrent transaction (A) had already committed, Postgres refused rather than let B clobber A's increment. **The fix is real, but you only reach 44 if you catch the error and retry** — the second half of B's transcript is the retry: a fresh REPEATABLE READ transaction now sees A's committed 43 and writes 44. ### Step 4 — Fix 3: `SELECT … FOR UPDATE` serializes the two **Predict.** Same read-modify-write, back at READ COMMITTED, but each session locks the row as it reads: `SELECT value … FOR UPDATE`. A reads (and locks), then B tries to read-and-lock the same row. What happens to B's `SELECT` while A's transaction is still open — and what value does B eventually read? ``` -- SESSION A -- SESSION B BEGIN; SELECT value FROM counters WHERE id = 1 FOR UPDATE; -- 42, row locked BEGIN; SELECT value FROM counters WHERE id = 1 FOR UPDATE; -- ? UPDATE counters SET value = 43 WHERE id = 1; COMMIT; -- releases the lock UPDATE counters SET value = 44 WHERE id = 1; COMMIT; ``` **Observe.** ``` -- SESSION A: BEGIN; SELECT ... FOR UPDATE (42, locks row); UPDATE SET value = 43; COMMIT BEGIN value ------- 42 (1 row) UPDATE 1 COMMIT -- SESSION B: BEGIN; SELECT ... FOR UPDATE <- BLOCKS until A commits, then returns 43 BEGIN value ------- 43 (1 row) UPDATE 1 COMMIT -- FINAL: SELECT value FROM counters WHERE id = 1; -> 44 ``` **44 — B blocked, then read A's committed 43.** `FOR UPDATE` took a row lock at read time, so B's `SELECT` could not proceed until A committed and released it. By the time B read, the row said 43, so B's app computed 44. The lock turned two concurrent read-modify-writes into two *sequential* ones — no stale read to overwrite from. ## What you should see - Scenario 0 (app read-modify-write, READ COMMITTED): final **43** — one of the two increments lost, silently. - Fix 1 (`value = value + 1`): final **44**. - Fix 2 (REPEATABLE READ): the loser **aborts** with `ERROR: 40001: could not serialize access due to concurrent update`; after a retry, final **44**. - Fix 3 (`SELECT … FOR UPDATE`): the second session **blocks** until the first commits, then reads 43; final **44**. ## Why The lost update is a **write-write race on a value derived from a stale read**. Scenario 0's cycle has three parts — read, compute, write — and only the read and the write touch the database; the "add one" happens in your process, where the database can't see it. So between B's read (42) and B's write (43), A commits a new value (43), and B has no idea: its `UPDATE value = 43` is a *blind* overwrite carrying a number computed from a value that is already out of date. Two correct-looking transactions, and the second one's write erases the first one's, because the second one never learned the first one happened. READ COMMITTED doesn't help: it governs what a transaction can *read* (only committed data), not whether a write is based on data that has since changed. Each fix removes the race in a different place. **Fix 1 removes the gap**: by writing `value = value + 1`, you push the read *into* the write, so the database does the read-modify-write atomically under its own row lock — there is no moment where your process holds a stale number the database doesn't know about. **Fix 3 serializes the sessions**: `FOR UPDATE` grabs the row lock at read time, so the second read-modify-write cannot even *begin* reading until the first commits; the two run in sequence and the second reads the already-updated value. **Fix 2 turns the race into a detected error**: snapshot isolation lets B read from a frozen snapshot, but tracks that B's target row was modified and committed by a concurrent transaction after that snapshot; rather than allow the lost update, it raises `40001` and makes B start over. This is the book's "automatically detecting lost updates" — the database catches what the application forgot to, but the application must be written to *retry* on `40001`, or the increment is lost to an unhandled exception instead of to a silent overwrite. **The boundary — blind writes and commutative in-DB updates are immune.** The anomaly needs a read-modify-write: a write whose value *depends on* a prior read. A write that doesn't read first (set the value to a constant, insert a new row) has nothing stale to carry, so there's no update to lose — that's why Fix 1 works by turning the increment into a single commutative in-DB operation the database can apply without an application round-trip. The moment the new value is a pure function the database can compute itself (`value + 1`, `array_append`, a counter bump), you don't need locking *or* retries; you need the read and the write to be the same statement. Locking (Fix 3) and detection (Fix 2) are what you reach for when the modify step genuinely must happen in application code — when it isn't something you can express as one SQL expression. ### Go deeper 1. **Make the abort a retry loop.** Wrap Fix 2's transaction in a loop that catches `40001` and re-runs it, then drive 100 concurrent increments through it and confirm the final value is exactly 142 — the production shape of optimistic concurrency control. 2. **Watch `FOR UPDATE` deadlock.** Give two rows and have session A lock row 1 then row 2 while B locks row 2 then row 1. Predict which session Postgres aborts, and with what SQLSTATE (`40P01`) — the cost that explicit locking adds back. 3. **Compare to SERIALIZABLE.** Re-run Step 1's exact read-modify-write under `ISOLATION LEVEL SERIALIZABLE` instead of REPEATABLE READ. Predict whether it also aborts the loser with `40001`, and whether the app-level code needs to change at all (it's the same retry contract).