The lost update anomaly explained

WHAT THIS TESTS The interviewer wants to confirm you understand race conditions on shared mutable state and how databases prevent them.

A GOOD ANSWER COVERS A lost update happens when two transactions read the same value, each modifies it based on that stale read, and both write back, so the later write clobbers the earlier one. With a counter at 10, transaction A reads 10, transaction B reads 10, A writes 11, B writes 11; the result is 11 when it should be 12, and one increment is silently lost. The danger is that each transaction looks correct in isolation. Fixes include performing the update atomically in the database with UPDATE counters SET value = value + 1, which reads and writes under a single lock; pessimistic locking via SELECT ... FOR UPDATE to block the second reader until the first commits; or optimistic concurrency, where each write checks a version or expected value and retries if it changed.

COMMON WRONG ANSWERS Assuming the database automatically serializes ordinary reads so the race cannot occur. Believing application-level read-then-write is safe without a lock or atomic operation. Thinking a higher isolation level alone fixes it under all engines, when the read-modify-write pattern in application code can still lose updates unless it uses locking or atomic statements.

LIKELY FOLLOW-UPS How does SELECT FOR UPDATE prevent it? What is optimistic versus pessimistic locking? Does Serializable isolation catch lost updates, and how?

ONE CONCRETE EXAMPLE Two users like a post at once. App code reads like_count 10, adds one, writes 11 from both requests, leaving 11 instead of 12. Replacing it with UPDATE posts SET like_count = like_count + 1 makes each increment atomic, so both likes count correctly.