Two users try to update the same account balance at the same time. What concurrency problem can occur?
Interview preparation resource from Gate Smashers.
Lost update — it occurs when two transactions read the same old value and both write new values, so one transaction's result overwrites the other.
What the problem is
The main problem is a lost update. It happens when two transactions read the same old value and then both write new values, causing one transaction's result to overwrite the other.
Concrete example (account balance)
Suppose an account balance is 100. Transaction T1 reads 100 and plans to subtract 10, while T2 also reads 100 and plans to subtract 20. T1 writes 90. Then T2, still using the old value 100, writes 80. The final value becomes 80 even though both changes together should have produced 70. The update performed by T1 has effectively disappeared.
- Step 1: T1 reads balance = 100
- Step 2: T2 reads balance = 100
- Step 3: T1 writes 90
- Step 4: T2 writes 80
- Result: Expected combined result = 70; Actual final value = 80
How a DBMS can prevent or detect it
A DBMS can prevent or detect lost updates using suitable isolation, locking, MVCC conflict checks, or atomic update statements that modify the value without relying on a stale application-side copy.
- Isolation: Use appropriate transaction isolation to avoid concurrent reads/writes causing lost updates.
- Locking: Acquire locks so one transaction updates the value exclusively while others wait.
- MVCC conflict checks: Detect conflicting concurrent writes via multiversion concurrency control checks.
- Atomic updates: Use atomic update statements that modify the value on the server side rather than relying on a stale client-side copy.