Fixing lost updates
Chapter 2's scariest bug: two read-modify-write transactions, one deposit silently gone. This lesson is the toolbox — three fixes, in the order you should reach for them. All three run at plain READ COMMITTED; none of them needs a higher isolation level.
Fix #1: compute in SQL, not in the app
If the new value can be expressed in SQL, the whole bug class evaporates — there is no stale read to write back:
Same two +10 deposits that lost an update in chapter 2 — but the math moved into the UPDATE itself.
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = balance + 10 WHERE id = 1 RETURNING balance;
balance
---------
110
(1 row)
B> BEGIN;
BEGIN
B> UPDATE accounts SET balance = balance + 10 WHERE id = 1 RETURNING balance;
⏳ B is waiting for a lock…No stale read exists to write back: B waits for A's row lock, then re-reads the committed 110.
A> COMMIT;
COMMIT
⏵ B resumes:
balance
---------
120
(1 row)
B> COMMIT;
COMMIT
A> SELECT balance FROM accounts WHERE id = 1; -- both deposits survived
balance
---------
120
(1 row)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
B's UPDATE waits for A's row lock, then — this is the update-recheck behavior from chapter 2, now working for you — re-reads the committed 110 and applies + 10 on top of it. The recheck that made read-modify-write dangerous makes single-statement math safe.
Fix #2: SELECT ... FOR UPDATE (pessimistic)
Sometimes the new value genuinely needs application code — business rules, an external rate lookup. Then lock the row at the read, so the read-modify-write becomes a queue:
The app must apply business rules to the balance in code — so it locks the row while it reads.
A> BEGIN;
BEGIN
A> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
balance
---------
100
(1 row)
B> BEGIN;
BEGIN
B> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
⏳ B is waiting for a lock…
A> UPDATE accounts SET balance = 100 + 10 WHERE id = 1;
UPDATE 1
A> COMMIT;
COMMITB's locked read waited out A's transaction — and returns the fresh 110, not the 100 it would have seen.
⏵ B resumes:
balance
---------
110
(1 row)
B> UPDATE accounts SET balance = 110 + 10 WHERE id = 1;
UPDATE 1
B> COMMIT;
COMMIT
A> SELECT balance FROM accounts WHERE id = 1; -- both deposits survived
balance
---------
120
(1 row)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
The manual's definition is exactly the guarantee we need: "FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends." B cannot even read (with intent to write) until A is done — and its read then returns 110, not the stale 100. The price: B waits, and waiting transactions are lock queues with everything chapter 3 said about them.
Fix #3: a version column (optimistic)
Pessimistic locking holds a lock while the app thinks. If "thinking" includes a user staring at an edit form, that's unacceptable — you can't hold a row lock across HTTP requests. Optimistic locking holds nothing and instead detects the conflict at write time:
Both app servers read the row — including its version — with no locks held.
A> BEGIN;
BEGIN
A> SELECT balance, version FROM accounts WHERE id = 1;
balance | version
---------+---------
100 | 1
(1 row)
B> BEGIN;
BEGIN
B> SELECT balance, version FROM accounts WHERE id = 1;
balance | version
---------+---------
100 | 1
(1 row)Every write bumps the version and only matches the version it read.
A> UPDATE accounts SET balance = 100 + 10, version = version + 1
WHERE id = 1 AND version = 1;
UPDATE 1
A> COMMIT;
COMMIT
B> UPDATE accounts SET balance = 100 + 10, version = version + 1
WHERE id = 1 AND version = 1; -- UPDATE 0 — the row moved on; B's deposit did NOT silently vanish
UPDATE 0UPDATE 0 is the signal to retry: roll back, re-read, write against the new version.
B> ROLLBACK;
ROLLBACK
B> BEGIN;
BEGIN
B> SELECT balance, version FROM accounts WHERE id = 1;
balance | version
---------+---------
110 | 2
(1 row)
B> UPDATE accounts SET balance = 110 + 10, version = version + 1
WHERE id = 1 AND version = 2;
UPDATE 1
B> COMMIT;
COMMIT
A> SELECT balance, version FROM accounts WHERE id = 1; -- both deposits survived
balance | version
---------+---------
120 | 3
(1 row)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
UPDATE 0 is the entire mechanism: the write names the version it read, and if the row has moved on, it matches nothing. The lost update didn't become impossible — it became detectable, and the retry (re-read, recompute, write against the new version) lands safely. Every ORM's "optimistic concurrency" feature is this one WHERE clause.
The three fixes sort by how much of the change you can push into SQL. Prefer fix #1 whenever SQL can express it: SET balance = balance + 10 is race-free at any isolation level and never waits longer than the lock itself. Reach for FOR UPDATE when application code must compute the value inside one short transaction — it trades throughput for simplicity. Use a version column when the read and the write are split by something you can't hold a lock across, like user think-time or an HTTP round-trip, and handle UPDATE 0 everywhere you write. The fourth option is the next lesson's: REPEATABLE READ + retry, where PostgreSQL flags the conflict as a 40001 and you rerun the whole transaction.