The Classic Race
A lost update happens when two transactions read the same value, each modifies it based on what they read, and one write silently clobbers the other. The classic case is two clients each incrementing a counter from 10 to 11, leaving 11 when the correct answer is 12.
The Sequence
- Transaction A reads balance 100.
- Transaction B reads balance 100.
- A writes 100 plus 50 equals 150.
- B writes 100 minus 30 equals 70, erasing A's deposit.
The update from A is lost because B based its write on a stale read.
How To Prevent It
- Atomic operations: write the update as a single statement like SET balance equals balance plus 50 so the database reads and writes together.
- Explicit locking: use SELECT FOR UPDATE to lock the row during the read so the second reader waits.
- Optimistic checks: compare a version column on write and retry if it changed.
- Higher isolation: Repeatable Read or Serializable detects and aborts the conflict.
Key idea
A lost update occurs when concurrent read modify write cycles overwrite each other, fixed by atomic updates, row locks, version checks, or higher isolation.