Fixing lost updates

Three READ COMMITTED schedules repair the single-row lost update: relative SQL arithmetic, a locking read, and a checked version column. Each asserts a final balance of 120 from two deposits of 10. These are Demonstrated behaviors under the shown writer protocols, not protection for every business rule.

Fix #1: compute in SQL, not in the app

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.6 · Run it yourself · Scenario source

B's relative UPDATE waits, then applies its increment to A's committed 110. The PostgreSQL 18 READ COMMITTED contract explains the updated-row recheck. No stale application value is written back in this operation. Other predicates, constraints and cross-row rules require their own analysis. At stronger isolation levels a conflicting update can instead raise 40001.

Fix #2: SELECT ... FOR UPDATE (pessimistic)

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

B's locking read waited for A's transaction. The next assertion checks that it returns 110.

⏵ 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.6 · Run it yourself · Scenario source

B's locking read waits and returns 110 after A commits. Ordinary SELECT has different visibility and does not take this row lock. The row-lock contract protects selected rows against conflicting changes until transaction end. Every participating read-modify-write writer must lock before reading and write within that same transaction. Keep it short; do not hold it open while a user thinks or a slow external call runs.

Fix #3: a version column (optimistic)

Both writers read the row and version without taking a FOR UPDATE row lock. Their transactions still acquire table locks.

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 0

For this additive deposit, B retries after UPDATE 0. Other applications may need to report or reconsider a stale edit; deletion can also yield zero rows.

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.6 · Run it yourself · Scenario source

B's stale predicate matches zero rows. In this additive-deposit example, B rolls back, rereads 110/version 2, and successfully writes 120/version 3. Every writer must check and advance the version. UPDATE 0 can also mean a deleted row; it is not a complete diagnosis. For a stale user edit, report the conflict or reconsider the edit instead of automatically replacing newer work. The UPDATE still takes a row lock and can wait; optimistic locking avoids a long-held lock during the earlier read-to-edit interval.

Prefer relative SQL for this increment, a locking read for a short transaction that computes a new value, or a checked version for a long read-to-write interval. These are recommendations for the stated shapes, not a universal selector. Handle errors and zero-row outcomes. None of these repairs alone enforces a cross-row invariant or protects external effects. Whole-transaction retry is needed when stronger isolation rejects the transaction.

Further reading

• Choose a protection: stock and stale edits
• Checked-write evidence
• PostgreSQL 18: Transaction Isolation
• PostgreSQL 18: Row-Level Locks
• The same lesson on MySQL

The reconstructed support assessment records the exact manual support and execution limits for this lesson.