Questions: Lost Update Problem: Overwriting Concurrent Writes

Question 1

A bank account holds $500. Transaction A reads $500 and plans to write $600 (adding $100). Transaction B reads $500 and plans to write $700 (adding $200). A commits first, then B commits. What is the final balance?

• A. $800 — both deposits are applied
• B. $700 — B's write silently overwrites A's $600, losing A's $100 deposit
• C. $600 — A's write is preserved and B's is rejected because A committed first
• D. $500 — both writes are rolled back because a conflict was detected

Answer: (B)

This is a classic lost update. B read $500 before A committed, so B's write is based on the original value. When B writes $700, it overwrites A's $600 — A's $100 deposit is permanently lost. The correct balance should be $800. Option C (B rejected) would require pessimistic locking that was never acquired. Option D (both rolled back) would require a detection mechanism like optimistic concurrency control with versioning.

Question 2

Which of the following correctly prevents the lost update problem in a concurrent database system?

• A. Using the READ COMMITTED isolation level, which is the default in many databases
• B. Wrapping each transaction in a BEGIN/COMMIT block
• C. Using SELECT ... FOR UPDATE to lock the row at read time, held until COMMIT
• D. Using READ UNCOMMITTED isolation for faster performance

Answer: (C)

SELECT ... FOR UPDATE acquires a row-level exclusive lock when reading, preventing any other transaction from reading or modifying the row until the lock is released at COMMIT. This serializes access to the row, eliminating the race condition. Option A is the key misconception: READ COMMITTED prevents dirty reads but does NOT prevent lost updates — both transactions can still read the same committed value and race to write. Options B and D provide no protection against lost updates.

Question 3

READ COMMITTED isolation level prevents the lost update problem in most database systems.

• T. True
• F. False

Answer: False

READ COMMITTED only guarantees that you read committed (not dirty) data — it does not prevent two transactions from reading the same committed value and then both writing over it. In the classic scenario, both transactions commit their reads before either writes, so both reads are of committed data, yet the lost update still occurs. Preventing lost updates requires either explicit row locking (SELECT ... FOR UPDATE), optimistic concurrency control with version checking, or a higher isolation level like REPEATABLE READ in databases that detect write conflicts.

Question 4

Optimistic concurrency control prevents lost updates by rejecting a write if the underlying row has changed since it was read, typically using a version number or timestamp.

• T. True
• F. False

Answer: True

Optimistic concurrency control (OCC) proceeds without locks: each transaction reads freely, but at write time compares the current row version to the version it read. If they differ, another transaction has intervened and the write is rejected (the transaction must retry). This prevents the lost update without blocking concurrent reads, making it efficient when conflicts are rare. The tradeoff: under high conflict rates, many retries occur, making pessimistic locking more practical.

Question 5

Why is the lost update problem particularly hard to detect through testing individual transactions? Why does it only emerge from concurrent execution?

Model answer: Each transaction in isolation behaves correctly: read the balance, add the deposit, write the new total. There is no bug within any single transaction's logic. The error only emerges from the interleaving of two transactions: both read the same value before either writes, so each overwrites the other's result without any violation of internal logic. Unit tests of individual transactions will always pass. The bug is a property of the schedule (the ordering of operations across transactions), not of any single transaction.

This is what makes concurrency bugs in databases especially dangerous: they are invisible to standard testing but can cause silent data corruption at scale. The lost update is detectable only through integration tests that run multiple concurrent sessions simultaneously, or through careful reasoning about transaction schedules. Systems under low load may never trigger the race condition, then it appears suddenly under production traffic.