Data consistency
Lost update problem: interview questions and how to answer them
A lost update occurs when two writers read the same old value and the later write silently overwrites the earlier decision.
Written and reviewed by Sahil Srivastav
What it actually is
The classic sequence is read value V, read value V, write V plus change A, write V plus change B. The final row contains B while A disappears, even though both requests reported success.
The fix depends on the operation: an atomic relative update for counters, a version predicate for detached edits, or a lock when a short transaction must own several rows.
Why it matters in production
Lost updates corrupt balances, inventory, settings, and collaborative edits without raising an exception. They are especially likely when a UI reads in one request and writes later.
Adding retries to an unconditional write makes the race repeat faster. The database must reject or combine stale work, not merely run it again.
How it works
Read-modify-write
The application computes from a value that may already be stale by the time it writes.
Atomic predicate
A single `UPDATE ... SET value = value + 1` lets the database serialise a counter without exposing the intermediate read.
Optimistic version
Include the version read by the client in the WHERE clause and treat zero affected rows as a conflict.
Merge policy
For independent fields or collaborative documents, merge operations deliberately; “last write wins” is a policy that discards data.
Detailed boundary
compare-and-swap writes
Operational consequence
a version predicate in the UPDATE
Implementing it
Use database constraints and affected-row checks as the source of truth.
Return 409 with current state for a human edit conflict; retry only a reproducible operation.
Test two concurrent writers with a barrier so the bug cannot hide behind timing.
Use a two-sided test for this boundary: drive the normal path and the failure path concurrently, then inspect the state that survives the race. For lost update problem, the useful assertion is the invariant after recovery, not merely a successful response from one caller.
Document the limit and the signal that tells an operator to change it. A production review of lost update problem should name the protected resource, the caller deadline, the expected overload decision, and the evidence that would distinguish a local bug from downstream saturation.
A focused review of lost update problem should separate the mechanism from its policy. Reproduce one normal request, one boundary case, and one concurrent failure; record the state transition, the resource consumed, and the signal an operator would see. Then state what the caller is allowed to retry and what must be reconciled manually. This makes lost update problem testable in a repository rather than a vocabulary answer.
Interview questions and how to answer them
How do you fix a counter?
Use an atomic relative UPDATE with a guard, such as decrement where quantity is sufficient, and inspect affected rows.
How do you fix a form edit?
Add a version or ETag to the write, reject stale versions, and let the caller merge or reload.
Does repeatable read solve a multi-request edit?
No. The read and write occur in separate transactions, so no isolation level spans the user’s think time.
When is a lock appropriate?
For short, server-side read-modify-write transactions where conflicts are common and the set of rows is known.
What evidence would you inspect for lost update problem?
Measure the boundary named in the design, compare it with the caller deadline and resource budget, and reproduce the contention or failure with more than one concurrent worker.
What is the tempting fix for this problem?
Changing a timeout, pool, or retry count alone usually moves the queue. First establish the invariant, then make the bounded mechanism and its failure outcome explicit.
Answers that lose the round
- Reading then issuing an unconditional UPDATE
- Ignoring affected-row count
- Using timestamps with insufficient precision as versions
- Retrying stale user input blindly
- Locking only in application memory while other processes write the database
- Calling last-write-wins conflict resolution
- Treating the local mechanism as a complete production guarantee
- Changing the limit without measuring the resource it protects
FAQ
Is a lost update a dirty read?
No. Both reads can be committed and clean; the later write simply overwrites an earlier committed write.
Can a unique constraint prevent it?
Only for uniqueness invariants. It does not merge two valid updates to the same row.
Should every conflict be retried?
Only if recomputing from fresh state preserves the caller’s intent.