Fixing lost updates
Chapter 2 proved the bug twice: the read-modify-write race loses a deposit at READ COMMITTED, and — unlike PostgreSQL — REPEATABLE READ doesn't save you. On MySQL there is no isolation level short of SERIALIZABLE that turns this bug into an error, so the fix has to be in your SQL. All three fixes below work at any isolation level.
Fix #1: compute in SQL, not in the app
If the new value is derivable in SQL, put the arithmetic inside the UPDATE. One statement = one atomic read-modify-write; the row lock does the serializing for you:
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.10 · Run it yourself · Scenario source
Fix #2: SELECT ... FOR UPDATE
When business rules must run in application code between the read and the write, lock the row at the read. On MySQL this has a second, PostgreSQL-less virtue: a locking read is a current read — it pierces the snapshot and returns the latest committed value, which is exactly what a read-modify-write needs:
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 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;
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.10 · Run it yourself · Scenario source
Fix #3: optimistic locking with a version column
Pessimistic locks hold everyone else out while the app thinks. The optimistic variant holds nothing: read the row's version, and make the write conditional on it. If someone got there first you affect 0 rows — a detectable outcome, unlike the silent overwrite:
Both app servers read the row — including its version — with no locks held.
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.10 · Run it yourself · Scenario source
MySQL counts changed rows, not matched rows
PostgreSQL's UPDATE n counts rows that matched the WHERE clause; MySQL's affected-rows count is rows actually changed (unless your driver sets CLIENT_FOUND_ROWS). The version-column pattern is safe either way — a version bump always changes the row — but don't build "did my WHERE match?" logic on affected rows without knowing which convention your driver uses.
Choosing between them
Same trade-offs as on PostgreSQL: #1 when SQL can express the change; #2 for short human-free transactions with real contention; #3 when the "transaction" spans user think-time or process boundaries — nothing can hold a row lock across an edit form. The difference is the stakes: on PostgreSQL, REPEATABLE READ is a safety net that turns the missed case into a 40001; on MySQL there is no net.
The through-line: no MySQL isolation level below SERIALIZABLE prevents a lost update, which leaves you three tools — compute atomically in SQL, take a locking read, or carry a version column. A FOR UPDATE read returns the latest committed data even at REPEATABLE READ, so the snapshot-piercing that's a trap elsewhere becomes the feature you want here. And a version-column write that matches 0 rows is handing you the retry signal, not a failure to swallow.