Lost Update

What It Is

A lost update happens when two transactions both read the same data item, both compute a new value based on that (now possibly stale) read, and both write back their result — with the second write silently overwriting and erasing the effect of the first, as if the first transaction's update never happened at all. No error is raised; the database simply ends up with a wrong final value.

Concrete Example — Bank Balance

Account A starts at ₹1000. T1 withdraws ₹100. T2 deposits ₹500. Run correctly (serially, in either order), the final balance should be ₹1400 (1000 − 100 + 500). Now interleave them:

Time  Operation                          A's value / effect
1     R1(A)                              T1 reads A = 1000
2     R2(A)                              T2 reads A = 1000  (before T1 writes anything!)
3     T1 computes: A_new = 1000-100=900
4     W1(A)                              A is now written to 900
5     T2 computes: A_new = 1000+500=1500  <- based on T2's STALE read from step 2
6     W2(A)                              A is now written to 1500, OVERWRITING T1's 900
7     COMMIT1
8     COMMIT2

Final A = 1500, not the correct 1400. T1's ₹100 withdrawal was completely erased — as far as the database is concerned, it's as if T1 never ran at all, even though T1 committed successfully. This is the lost update: T2 read A before T1's write landed, computed its result from that stale value, and its write blindly clobbered T1's update.

Why This Slips Past Naive Checks

Both T1 and T2 individually did everything "correctly" — each read A, computed a valid new value, wrote it back, and committed without error. The bug is entirely in the interleaving: neither transaction was aware the other had also read (and was about to write) the same item. No constraint violation, no exception — just a silently wrong final balance that could easily go unnoticed until an audit reveals ₹100 unaccounted for.

How It's Prevented

Lost updates are prevented by ensuring T2's read-then-write on A cannot be interrupted by another transaction's write in between — i.e., by making the read+write into A an atomic unit with respect to other transactions. Locking protocols achieve this by having T1 hold an exclusive (write) lock on A from its first touch until commit, forcing T2's read to block until T1 finishes (and vice versa) — this is exactly the concurrency control problem introduced in 23.1, and lost update is one of the concrete anomalies serializability (and stricter locking) protects against.

Edge Cases

• A lost update can also occur between two writes with no intervening read by the second transaction (a "blind write" scenario) — e.g., two UPDATE stock SET qty = 50 statements from two different sessions, where the second simply overwrites the first's target value regardless of the current row state; this is arguably an even more silent variant.
• Lost update is a genuine serializability violation — the precedence graph for the schedule above would show a cycle (T2's read precedes T1's write giving one edge, T1's read precedes T2's write giving the opposite edge), confirming it is not conflict-serializable.
• This is different from a dirty read (23.11) — here, both T1's and T2's writes are eventually committed; the issue is that T2's write silently overwrites T1's, not that T2 read something that got rolled back.

Key Takeaways / Interview Angle

• Q: What is the root cause of a lost update? Two transactions performing a "read, compute, write" cycle on the same item concurrently, where the second transaction's read happens before the first transaction's write, so the second write is based on stale data and destroys the first update.
• Q: How would you explain lost update to a non-technical stakeholder? "Two people updated the same account balance at almost the same time; whichever save happened last won, and the other person's change silently vanished — even though both operations reported success."