ConcurrencyIntermediate
What is a lost update and how do you prevent it?
“Two requests increment a counter and one increment vanishes. Explain and fix.”
What this tests
- Read-modify-write races
- Locking strategies
Answers by level
Read the beginner answer first and notice what is missing.
Both transactions read the same value, compute a new one in application code, and write back — the second overwrites the first, so one update is lost with no error. The cause is a read-modify-write outside the database.
Fixes: do the arithmetic in the statement (SET n = n + 1); take a row lock at read time (SELECT … FOR UPDATE); or optimistic locking (write with WHERE version = ? and retry on zero rows).
Green flags · Red flags
Strong green flag · Prefers the single-statement atomic update as the simplest fix.
Green flags
- Identifies read-modify-write
- Offers atomic statement, FOR UPDATE, and optimistic
- Knows the level interaction
Red flags
- "Add a lock" with no specifics
- Keeps the arithmetic in application code
Follow-up questions
F1
How does optimistic locking detect the conflict?
Scenario
Two workers both read stock = 5, both sell one, both write 4. Stock should be 3. Fix without a global lock.