Fixing lost updates
Chapter 2 demonstrates a read-modify-write overwrite at READ COMMITTED and REPEATABLE READ. The three schedules below repair that single-row deposit example on InnoDB at the default REPEATABLE READ level. They do not demonstrate every isolation level or enforce arbitrary multi-row business rules.
Fix #1: compute in SQL, not in the app
Both writers use balance = balance + 10 inside UPDATE. B waits for A's lock, then computes from 110. The asserted final balance is 120:
Same two +10 deposits that lost an update in chapter 2 — but the math moved into the UPDATE itself.
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = balance + 10 WHERE id = 1;
Query OK, 1 row affected
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
110
(1 row)
B> BEGIN;
Query OK
B> UPDATE accounts SET balance = balance + 10 WHERE id = 1;
⏳ B is waiting for a lock…No stale read exists to write back: B waits for A's row lock, then computes from the committed 110.
A> COMMIT;
Query OK
⏵ B resumes:
Query OK, 1 row affected
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
120
(1 row)
B> COMMIT;
Query OK
A> SELECT balance FROM accounts WHERE id = 1; -- both deposits survived
balance
---------
120
(1 row)Verified against MySQL 8.4.11 · Run it yourself · Scenario source
Fix #2: SELECT ... FOR UPDATE
Both writers lock the row before computing the new balance. B's locking read waits for A's commit and returns 110; the final balance is 120. The MySQL 8.4 manual states that a locking read "reads the latest available data". Use the read and write in one explicit transaction.
The app must apply business rules to the balance in code — so it locks the row while it reads.
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
balance
---------
100
(1 row)
B> BEGIN;
Query OK
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;
Query OK, 1 row affected
A> COMMIT;
Query OKB's locking read waited for A's commit and returns 110. No prior plain-read snapshot is established for B here.
⏵ B resumes:
balance
---------
110
(1 row)
B> UPDATE accounts SET balance = 110 + 10 WHERE id = 1;
Query OK, 1 row affected
B> COMMIT;
Query OK
A> SELECT balance FROM accounts WHERE id = 1; -- both deposits survived
balance
---------
120
(1 row)Verified against MySQL 8.4.11 · Run it yourself · Scenario source
Fix #3: optimistic locking with a version column
Both writers read version 1 without a locking read. Each update requires the version it read and increments it. After A commits, B's stale update affects 0 rows. B rolls back, reads version 2 in a fresh transaction, and completes the second deposit:
Both sessions read balance and version without a locking read. The UPDATEs still acquire write locks.
A> BEGIN;
Query OK
A> SELECT balance, version FROM accounts WHERE id = 1;
balance | version
---------+---------
100 | 1
(1 row)
B> BEGIN;
Query OK
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;
Query OK, 1 row affected
A> COMMIT;
Query OK
B> UPDATE accounts SET balance = 100 + 10, version = version + 1
WHERE id = 1 AND version = 1; -- 0 rows matched — the row moved on; B's deposit did NOT silently vanish
Query OK, 0 rows affectedZero affected rows is the signal to retry: roll back, re-read, write against the new version.
B> ROLLBACK;
Query OK
B> BEGIN;
Query OK
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;
Query OK, 1 row affected
B> COMMIT;
Query OK
A> SELECT balance, version FROM accounts WHERE id = 1; -- both deposits survived
balance | version
---------+---------
120 | 3
(1 row)Verified against MySQL 8.4.11 · Run it yourself · Scenario source
MySQL affected-row settings matter
The UPDATE manual states: "UPDATE returns the number of rows that were actually changed." CLIENT_FOUND_ROWS instead requests matched-row reporting, as documented in mysql_affected_rows. Here a successful version increment changes the row, so both conventions distinguish it from a stale predicate matching no row.
Choosing between them
Use arithmetic in SQL when it expresses the complete single-row change. Use a locking read for a short transaction that must decide from that row's current state. Use a version check when an edit spans user think-time; on conflict, refresh or ask the user to reconcile the edit rather than replaying a stale overwrite automatically. Every relevant writer must follow the chosen protocol.
Entailed guarantee†: for these single-row updates, arithmetic under the write lock, a read/write pair protected by a retained exclusive lock, or a consistently incremented version predicate avoids the illustrated stale overwrite. † This follows from InnoDB exclusive locking and the predicate discipline explained above. The transcripts show particular successful schedules; errors and broader invariants still require separate handling.