Lost updates
Try two-writer practice before reading the results below.
Two clients can read the same value, compute a replacement, and overwrite one another. The following InnoDB schedules run on MySQL 8.4.11 with reads inside the updating transactions. Each reads 100 and writes that saved value plus 10. Both commit without error, but the final balance is 110 rather than 120.
At READ COMMITTED
Two app servers process a +10 deposit each: read the balance, add 10 in code, write it back.
A> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
B> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK
B> BEGIN;
Query OK
B> SELECT balance FROM accounts WHERE id = 1; -- B reads the same 100; A hasn't committed
balance
---------
100
(1 row)
A> UPDATE accounts SET balance = 100 + 10 WHERE id = 1;
Query OK, 1 row affected
A> COMMIT;
Query OKB uses its captured 100 plus 10 in this SQL UPDATE. A has committed; this stale replacement succeeds in the displayed schedule.
B> UPDATE accounts SET balance = 100 + 10 WHERE id = 1;
Query OK, 0 rows affected
B> COMMIT;
Query OK
A> SELECT balance FROM accounts WHERE id = 1; -- two +10 deposits, but only one survived
balance
---------
110
(1 row)A's deposit vanished without any error. Fixes: atomic UPDATE or SELECT FOR UPDATE; see the lesson.
Verified against MySQL 8.4.11 · Run it yourself · Scenario source
REPEATABLE READ does not save you
InnoDB UPDATE uses the current target row. It does not reject this stale literal replacement merely because B's earlier consistent read saw an older version. PostgreSQL's REPEATABLE READ example rejects that post-snapshot target change with 40001. Neither example establishes detection of every stale value read outside the updating transaction.
Protect the read-modify-write operation
Moving this unprotected schedule from READ COMMITTED to REPEATABLE READ does not repair it. An ORM operation that uses locks, a version check, or atomic arithmetic has different protection; not every ORM save has this race.
The same two +10 deposits; this time at REPEATABLE READ, MySQL's default.
A> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK
B> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
B> BEGIN;
Query OK
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
A> UPDATE accounts SET balance = 100 + 10 WHERE id = 1;
Query OK, 1 row affected
A> COMMIT;
Query OKB's snapshot predates A's commit; but MySQL's UPDATE acts on the CURRENT row and raises nothing.
B> UPDATE accounts SET balance = 100 + 10 WHERE id = 1;
Query OK, 0 rows affected
B> COMMIT;
Query OK
A> SELECT balance FROM accounts WHERE id = 1; -- A's deposit is gone; silently, even at REPEATABLE READ
balance
---------
110
(1 row)PostgreSQL refuses B's write here (SQLSTATE 40001). MySQL does not; don't port that assumption.
Verified against MySQL 8.4.11 · Run it yourself · Scenario source
The fixes
The existing fixing lost updates Scenarios execute atomic SQL arithmetic, a locking read before the decision, and a checked version-column UPDATE. Their assumptions and conflict responses belong to those examples. Atomic arithmetic fits increments; it is not a general repair for every replacement or business rule. All participating writers must respect the chosen protection, and an unmet version check needs explicit conflict handling.
SERIALIZABLE explicit transactions also change this race by locking the reads, with possible deadlocks or waits. That is a Documented contract and an inference from the locking-read rules and SERIALIZABLE contract, not a SERIALIZABLE execution of these two lost-update Scenarios. A standalone autocommit SELECT followed by a separate UPDATE does not retain that read lock.