What is the lost update anomaly in a database, and what sequence of operations produces it?
answer
- read-modify-write race
- both read 100, one write survives
- silent — no error, no constraint violated
- last writer wins
- ORM full-row save clobbers untouched columns
basics
~20 sTwo transactions read the same row, each computes a new value from what it read, then both write. The second write overwrites the first, so one committed change silently disappears. No error is raised — only the final value is wrong.
solid answer
~50 sA lost update is a **read-modify-write race** on the same row. T1 reads `balance = 100`, T2 reads `balance = 100`, T1 writes `90` and commits, T2 writes `80` and commits. T1's withdrawal is gone: the correct answer was `70`, the stored answer is `80`. The key property is that both transactions computed their new value from a version of the row that was already stale by the time they wrote. The database never complains, because each individual statement is perfectly legal — the anomaly only exists relative to the application's intent. It shows up wherever code does *read row → compute in application → write row back*: counters, inventory decrements, retry counters, and ORM save operations that rewrite every column of an entity. The fixes are to do the arithmetic inside one SQL statement, to lock the row while you hold it, or to make the write conditional on the version you read.
code
text · 9 linesT1 T2
SELECT balance -> 100
SELECT balance -> 100
UPDATE ... SET balance = 90
COMMIT
UPDATE ... SET balance = 80
COMMIT
expected 70 actual 80 (T1's -10 is lost)go deeper
Be able to define it and walk the four-step interleaving with concrete numbers, and name at least one fix (do the arithmetic in the UPDATE itself).
Explain why no error is raised, connect it to read-modify-write code and full-row ORM saves, and contrast the atomic-update, locking, and version-column fixes.
Discuss which isolation levels do and do not stop it, how snapshot engines turn it into a serialization failure, and how you would audit an existing codebase for the pattern.
Frame it as an application-invariant problem: decide per write path which concurrency strategy applies, define the retry and conflict-surfacing policy, and treat silent data corruption as the risk being managed.
## The anomaly A **lost update** occurs when two concurrent transactions both read the same row, each derives a new value from the value it read, and both write their result. The second write was computed from data that became stale the moment the first transaction committed, so it overwrites the first change. The first update is *lost*: it committed successfully, and then vanished. ## The canonical interleaving Start with `accounts(id=42, balance=100)`. T1 withdraws 10, T2 withdraws 20. 1. T1: `SELECT balance FROM accounts WHERE id = 42` → 100 2. T2: `SELECT balance FROM accounts WHERE id = 42` → 100 3. T1: `UPDATE accounts SET balance = 90 WHERE id = 42` → COMMIT 4. T2: `UPDATE accounts SET balance = 80 WHERE id = 42` → COMMIT The correct final balance is 70. The stored balance is 80. Ten units were never withdrawn — the bank silently gave money away. Note that step 4 is not a dirty read: T2 read *committed* data. It just read it too early and held it too long. ## Why the database does not complain By the time T2's `UPDATE` reaches the engine, it is a statement that sets a column to the constant 80. Nothing about it is illegal, no constraint is violated, and no lock is contended for more than a moment. The engine has no way to know that 80 was derived from a stale read. The anomaly is defined relative to application intent, which is why it must be prevented by how you write the code, not detected after the fact. This is also what makes lost updates dangerous in production: they produce no exceptions, no log lines, and no failed requests. They surface days later as a stock count that does not match the warehouse, or a ledger that does not balance. ## Where it appears in real systems - **Counters and balances**: view counts, credit balances, remaining inventory, rate-limit tallies. - **Retry / attempt counters**: two workers both read `attempts = 2` and both store `3`. - **Whole-entity saves**: an object-relational mapper loads an entity, the user edits one field, and the framework issues an `UPDATE` that writes *every* column. Two users editing different fields of the same record still clobber each other, because each writes back the stale values of the fields they never touched. - **Read-check-then-write business rules**: read status, decide it is `PENDING`, write `SHIPPED` — two workers ship the same order. ## Why the obvious non-fixes do not work **"Make the transactions shorter."** Shrinking the window makes the race rarer, not impossible. Under load, rare means daily. **"Wrap it in a transaction."** Both transactions in the example were fully ACID. Atomicity and durability say nothing about interleaving; only isolation does, and the default isolation level in most engines (READ COMMITTED) does not stop this pattern. **"Retry on error."** There is no error to retry on unless you arrange for one — which is exactly what the version-column approach does. ## The three real fixes 1. **Atomic in-place update.** Express the change as arithmetic on the stored value inside one statement: `UPDATE accounts SET balance = balance - 10 WHERE id = 42`. The read and the write happen inside the same statement, under the engine's row-level write lock, so there is no window. 2. **Pessimistic locking.** Read the row with an explicit exclusive row lock (`SELECT ... FOR UPDATE`) inside the transaction that will write it. Other writers block until you commit, which serializes the read-modify-write. 3. **Optimistic concurrency control.** Read the row *and* its version number, then make the write conditional: `UPDATE ... SET value = ?, version = version + 1 WHERE id = ? AND version = ?`. If zero rows are affected, someone else changed it; reload and retry. ## Relation to isolation levels The original ANSI SQL-92 isolation definitions describe only dirty reads, non-repeatable reads, and phantoms — lost update is not among them, which is why it is often labelled **P4** from the later critique of that standard. Practically: under lock-based REPEATABLE READ the read lock is held to commit, so the pattern turns into a block or a deadlock instead of silent corruption; under snapshot-based engines a concurrent write to the same row is detected and one transaction is aborted with a serialization failure. Under READ COMMITTED — the default nearly everywhere — nothing stops it. Assume you must handle it yourself.
- Does the default READ COMMITTED isolation level prevent lost updates?No. READ COMMITTED only guarantees that you never read uncommitted data; it says nothing about a value staying stable between your read and your later write. Both transactions in the classic interleaving read committed data and both commit legally. Preventing the anomaly requires an atomic update, an explicit row lock, a version check, or an isolation level of snapshot/serializable strength with retry handling.
- An object-relational mapper writes every column on save. How does that make lost updates worse?Two users editing completely different fields of the same record now conflict, because each save writes back the stale values of the fields the user never touched. Editing only the phone number can silently revert someone else's address change. The usual mitigations are dynamic updates that write only dirty columns, plus a version column so the conflicting save fails loudly.
Two people copy the same spreadsheet to their laptops, each edits one cell, and both upload. The second upload replaces the whole file — the first person's edit is gone, and nothing warns anyone.
saying these in an interview costs you the question
- Confusing it with a dirty read — in a lost update both transactions read committed data
- Claiming that simply using a transaction, or ACID in general, prevents it
- Saying shorter transactions or a retry-on-error loop fix it; there is no error to catch
- Believing the database logs or raises something when an update is lost
- Assuming the default isolation level of the engine already prevents it