Lost Updates
A lost update happens when two transactions read the same value and both write it back, so one change silently overwrites the other.
Definition
A lost update is a concurrency anomaly in which two transactions read the same row, each computes a new value from the old one, and the later write replaces the earlier write. The first transaction's change disappears even though it committed successfully. It is a read-modify-write race, not a duplicate write.
Context and scope
Lost updates appear in counters, balances, inventory, and any workflow that loads a record, edits it in application code, and saves the whole row. They are easy to miss because both transactions report success.
Common misconceptions
- Repeatable read prevents lost updates in every database.
- An ORM's save method is safe because it updates only changed columns.
Anti-patterns
- Reading a balance, adding an amount in application code, and writing the absolute result back.
- Using last-write-wins timestamps for values that require arithmetic accumulation.
Related concepts
Related examples
Authoritative source
https://www.postgresql.org/docs/current/transaction-iso.html
How it works
Transaction A reads balance = 100. Transaction B reads balance = 100. A writes 110 and commits. B writes 120 and commits. The correct balance after two +10 credits is 120; the result is 120, but only because both changes were the same amount. With +10 and +5, A writes 110, B writes 105, and the final 105 loses A’s credit entirely.
The anomaly is about the read-write sequence, not the row lock duration. If the read happens outside the write lock, both transactions can base their writes on the same old value. Databases differ in detection: PostgreSQL repeatable read raises a serialization error on the second update, while some engines under some isolation levels allow the overwrite.
Prevention
Use an atomic statement: UPDATE accounts SET balance = balance + 10 WHERE id = 7. The database serializes the increments. Alternatively, use optimistic concurrency: add a version column, update WHERE id = 7 AND version = 3, and retry if zero rows change. Pessimistic locking with SELECT ... FOR UPDATE works but holds locks across application logic and can deadlock. Serializable isolation plus retries is correct when several rows form one invariant.
When to use which
Use atomic SQL for simple arithmetic and counters. Use optimistic concurrency for forms where a human edits a record and a conflict should be shown rather than retried silently. Use pessimistic locks for short critical sections with high contention. Avoid read-modify-write across service boundaries unless the operation is idempotent and versioned.
Trade-offs and example
Atomic updates are simple and fast but cannot express arbitrary application logic. Optimistic concurrency detects conflicts but requires retry UX and may thrash under high contention. Pessimistic locking prevents conflicts but reduces throughput and risks deadlocks.
Example: two support agents open the same ticket and each adds a tag by replacing the whole tag list. The second save overwrites the first agent’s tag. Storing tags in a separate table with a unique constraint, or versioning the ticket, preserves both edits. The same pattern applies to JSON documents saved wholesale.