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, Redis INCR). The row lock serialises the increments.
  • Explicit lock: SELECT … FOR UPDATE before 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 WHERE is evaluated against the latest committed row.
  • Retrying a detected conflict must rerun the whole transaction, including the read.

Recall

These cards are also in your review deck, scheduled with spaced repetition.