Notestransactions🌱 seedling
Lost Update
Also: Lost Update Problem · Read-Modify-Write Race · P4 · Compare-and-Set · SELECT FOR UPDATE
Two transactions each read a value, modify it in application code and write it back; the second write silently erases the first. It's the classic read-modify-write race behind broken counters, balances and inventory.
Why it exists
Read Committed controls what you see, not what happens when two transactions overwrite the same row based on stale reads. ORMs make the load-modify-save pattern the default.
How it works
Fixes, in rough order of preference:
- Atomic update:
UPDATE t SET n = n + 1(or$inc, RedisINCR). The row lock serialises the increments. - Explicit lock:
SELECT … FOR UPDATEbefore the read-modify-write; the second transaction waits. - Compare-and-set:
UPDATE … WHERE id = 1 AND version = 7; zero rows means retry. This is Optimistic Concurrency Control in miniature. - Automatic detection: under Snapshot Isolation, the second writer is aborted (Postgres Repeatable Read, Oracle Serializable, SQL Server
SNAPSHOT).
Trade-offs
| Fix | Cost |
|---|---|
| atomic update | only works when the change fits one statement |
FOR UPDATE |
blocking; easy to forget in one code path |
| compare-and-set | retries under contention |
| detection | aborts and retries; not available in every engine |
In practice
- MySQL InnoDB Repeatable Read does not detect lost updates. Writes go to the latest row version, so a stale read is silently built on.
- Flexcoin (2014): thousands of simultaneous transfer requests overdrew accounts "before balances were updated"; 896 BTC were lost and the exchange closed.
Pitfalls
- Compare-and-set against a snapshot read can be unsafe in some engines; check that the
WHEREis evaluated against the latest committed row. - Retrying a detected conflict must rerun the whole transaction, including the read.
Connections
- Part of
- Isolation Level
- Learn first
- Read Committed
- In real systems
- PostgreSQLMySQL
Recall
These cards are also in your review deck, scheduled with spaced repetition.