Problem Statement
Explain the lost update problem and two strategies to prevent it without serializing the whole workload.
Explanation
Lost updates occur when two transactions read the same row, compute new values, and write back, with the second write overwriting the first. Each transaction appeared correct alone, but the combined effect drops one change.
Prevent it with optimistic concurrency: store a version or timestamp and add it to the WHERE of the UPDATE. If zero rows update, retry with fresh data. Or use SELECT FOR UPDATE to lock the row before computing, which is pessimistic but simple for hot records.
Code Solution
SolutionRead Only
UPDATE accounts SET balance = balance + :delta, version = version + 1 WHERE id = :id AND version = :version;
Practice Sets
This question appears in the following practice sets:
