Lost Update Problem
You'll learn how two concurrent writes can accidentally overwrite each other, losing data even when using repeatable reads.
What Is the Lost Update Problem?
Imagine two bank clerks editing the same customer's address simultaneously. Both read "123 Old St," both change it (one to "456 New Ave," another to "789 Elm Rd"), and both save. Whoever saves last wins—the other update is lost forever.
The lost update problem occurs when two transactions read the same data, modify it independently, then write it back—each unaware of the other's changes. Even with repeatable read isolation, this can happen because repeatable reads only prevent reading changed data, not concurrent writes.
Why Repeatable Reads Aren't Enough
Repeatable read ensures you see a consistent snapshot while reading, but it doesn't lock the row for writing. Both transactions can:
- Read the same value (e.g., account balance = 100)
- Calculate new values independently (transaction A: +50, transaction B: +30)
- Write back their results (150 and 130)
- One overwrites the other—losing an update
Solutions
Explicit Locking: Use SELECT ... FOR UPDATE to lock rows during the read phase, preventing others from reading until you're done writing.
Atomic Operations: Use database-level operations like UPDATE accounts SET balance = balance + 50 instead of read-modify-write sequences. The database handles concurrency internally.
Optimistic Locking: Add a version column; only update if the version hasn't changed since you read it.
Key Takeaway: Lost updates happen when concurrent transactions blindly overwrite each other's changes. Prevent them with explicit locks or atomic operations that let the database coordinate writes.