What is the Lost Update Problem in Databases?
Learn what the lost update problem is, why it fails silently, and how locking or optimistic concurrency prevents it.
Expected Interview Answer
The lost update problem occurs when two transactions read the same row, both compute a new value based on that same original read, and the second transaction’s write overwrites the first transaction’s write without ever incorporating it, silently discarding one of the updates.
This typically happens with a read-modify-write pattern: Transaction A reads a value, Transaction B reads the same original value before A writes, then both compute their own new value and write it back — whichever commits last simply overwrites the other, and the first transaction’s change is lost as if it never happened, with no error raised. The fix is to make the read-and-write atomic, either through row-level locking (SELECT FOR UPDATE), an atomic UPDATE expression that recomputes from the current stored value rather than a previously-read value, or optimistic concurrency using a version column that rejects a write if the row changed since it was read.
- Explains a silent-failure bug that produces no error message
- Shows why read-modify-write patterns need atomic updates
- Motivates locking or optimistic concurrency as concrete fixes
- Distinguishes a same-row conflict from cross-row anomalies like write skew
AI Mentor Explanation
Imagine two scorers both looking at the scoreboard showing 150 runs, and both independently decide to add the 4 runs they each just witnessed, so Scorer A writes 154 and, moments later, Scorer B — who also started from 150 — writes 154 too, based on a different boundary that also happened. Only one boundary got permanently recorded even though two were hit; the other 4 runs vanish from the total because both scorers overwrote based on the same stale starting number. That silent overwrite of a valid update is the lost update problem.
Step-by-Step Explanation
Step 1
Two transactions read the same row
Both transactions read the identical original value before either has written anything back.
Step 2
Each computes a new value independently
Each transaction calculates its update (e.g. balance minus a withdrawal) based on that same stale reading.
Step 3
Both write back, one silently overwrites the other
Whichever transaction commits second overwrites the first, with no error and no merge of the two changes.
Step 4
Apply a fix
Use SELECT FOR UPDATE to lock the row, an atomic UPDATE expression, or optimistic concurrency with a version column.
What Interviewer Expects
- Clear description of the read-modify-write race condition
- Recognition that the failure is silent — no error is raised
- At least one concrete fix: pessimistic locking, atomic update, or optimistic versioning
- A simple concrete example (e.g. concurrent balance withdrawals)
Common Mistakes
- Confusing lost update with a dirty read
- Not explaining why the bug produces no visible error
- Forgetting to name a concrete prevention technique
- Assuming a transaction alone (without locking or atomic updates) prevents this
Best Answer (HR Friendly)
“The lost update problem happens when two processes read the same value, each calculates a new value from it, and then one write silently overwrites the other — so one of the two updates just disappears with no error at all. It usually shows up with something like two withdrawals hitting the same account balance at once. You prevent it by locking the row while updating, using an atomic update expression, or checking a version number before writing.”
Code Example
-- Both read balance = 1000 before either writes
-- Transaction A
SELECT balance FROM Accounts WHERE account_id = 1; -- reads 1000
-- application computes 1000 - 200 = 800
UPDATE Accounts SET balance = 800 WHERE account_id = 1;
-- Transaction B, started before A's write
SELECT balance FROM Accounts WHERE account_id = 1; -- also reads 1000
-- application computes 1000 - 200 = 800
UPDATE Accounts SET balance = 800 WHERE account_id = 1;
-- B's write overwrites A's; only one 200-dollar withdrawal is reflected
-- Fix 1: atomic update, no stale read involved
UPDATE Accounts SET balance = balance - 200 WHERE account_id = 1;
-- Fix 2: pessimistic locking
SELECT balance FROM Accounts WHERE account_id = 1 FOR UPDATE;Follow-up Questions
- How does SELECT FOR UPDATE prevent a lost update?
- What is optimistic concurrency control and how does a version column help?
- How does an atomic UPDATE expression avoid the read-modify-write race?
- How is the lost update problem different from write skew?
MCQ Practice
1. The lost update problem occurs when:
Lost update happens when both transactions base their write on the same stale read, so one write silently discards the other.
2. Which SQL technique directly prevents a lost update by locking the row during read?
SELECT FOR UPDATE locks the selected row until the transaction ends, preventing another transaction from reading the same stale value.
3. Which of the following avoids a lost update without explicit locking?
An atomic update expression recomputes from the current stored value at write time, sidestepping the stale-read race entirely.
Flash Cards
What is the lost update problem? — Two transactions read the same value; one write silently overwrites the other’s update.
Does the lost update problem raise an error? — No — it fails silently, which is what makes it dangerous.
How to prevent a lost update? — Use SELECT FOR UPDATE, an atomic UPDATE expression, or optimistic concurrency with a version column.
Lost update vs write skew? — Lost update overwrites the same row; write skew involves different rows and a shared invariant.