Lost updates
Two clients read a value, compute a new one in application code, and write it back — one update silently erases the other. Why the read-modify-write pattern loses data is Concepts: the lost update problem; this page is about what makes MySQL's version of it uniquely dangerous.
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 computed 100 + 10 from its stale read. Nothing stops the write — A's transaction is long gone.
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.10 · Run it yourself · Scenario source
REPEATABLE READ does not save you
This is the sharpest MySQL/PostgreSQL divergence in the whole chapter. PostgreSQL's REPEATABLE READ detects the stale write and aborts it with 40001. MySQL's UPDATE is a current read — it applies your stale arithmetic to the newest row version and raises nothing:
The isolation knob will not fix this
Every ORM save() that reads, computes, and writes back has this bug at MySQL's default level. The fix is structural — atomic SQL, a locking read, or a version column — not a SET TRANSACTION away.
The same two +10 deposits — this time at REPEATABLE READ, MySQL's default.
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.10 · Run it yourself · Scenario source
The fixes
Raising the isolation level is not one of them (short of SERIALIZABLE). On MySQL you fix lost updates structurally — atomic UPDATEs, SELECT … FOR UPDATE (chapter 3), or an optimistic version column checked via affectedRows. The three patterns are defined in the concept page and each proven with a transcript in fixing lost updates.
A lost update is silent: the transcript above ends with 110 where two +10 deposits on 100 should have produced 120, and nothing errored. Nothing short of SERIALIZABLE stops it on MySQL — if your app reads a value, computes from it, and writes it back, the bug is there until you apply one of the three fixes. If you're porting from PostgreSQL, code that leaned on its REPEATABLE READ conflict detection loses that protection the moment it runs here.