A counter holds 42. Two sessions each add one the obvious application way — SELECT, add one in code, UPDATE — and run concurrently. The final value is 43, not 44: one increment silently vanishes. Then three fixes, and a prediction of which actually work — the atomic value = value + 1, REPEATABLE READ (which aborts the loser with SQLSTATE 40001), and SELECT … FOR UPDATE.
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.
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.
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.
SELECT … (app adds 1) … UPDATE.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.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.40001 rather than let it clobber. It shows up as Fix 2.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.
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.
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.
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:16Create 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 BBefore 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.
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;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.
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;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.
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; -- ?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.
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;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.
value = value + 1): final 44.ERROR: 40001: could not serialize access due to concurrent update; after a retry, final 44.SELECT … FOR UPDATE): the second session blocks until the first commits, then reads 43; final 44.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.
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.
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.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.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).Sources: DDIA 2e, Ch. 8, §"Preventing Lost Updates", §"Automatically detecting lost updates" · PostgreSQL 16 manual, ch. 13 "Concurrency Control", §13.2 "Transaction Isolation", §13.3 "Explicit Locking".