Preventing Lost Updates

In this lesson, you will learn how to bypass the dangerous "read-modify-write" cycle by using atomic operations to prevent lost updates.

The Problem: The Read-Modify-Write Trap

A "Lost Update" occurs when two transactions read the same data, calculate a new value locally, and then write that result back to the database. The last transaction to write essentially overwrites the changes made by the first, causing data to vanish silently.

For example, consider a bank balance. If your balance is $100 and two processes simultaneously try to add$10:

1. Process A reads $100.
2. Process B reads $100.
3. Process A calculates $110 and writes it to the table.
4. Process B calculates $110 (based on the original$100) and overwrites the database with $110. The$10 from Process A is now lost forever.

The Solution: Atomic Operations

Instead of pulling data into your application code to perform calculations, let the database engine perform the update in a single, atomic step. By using the database’s internal logic, you ensure the update is based on the most current state of the data.

Atomic Increments

Instead of SELECT balance FROM accounts... followed by an UPDATE, use a single atomic command:

UPDATE accounts 
SET balance = balance + 10 
WHERE account_id = 123;

Because the database handles the addition internally during the write lock, it doesn't matter if multiple transactions hit this row at once; the engine queues them and applies each increment sequentially.

Compare-And-Swap (CAS)

If you must use application logic for more complex calculations, use a Compare-And-Swap pattern. This involves adding a condition to your UPDATE statement that checks if the row has changed since you last read it:

UPDATE products 
SET stock = 5, version = version + 1 
WHERE product_id = 99 AND version = 10;

If another transaction modified the row, the version will no longer be 10, the WHERE clause will fail to match any rows, and you will know the update failed. You can then choose to retry the operation with the fresh data.

Key Takeaway: Avoid reading data into application memory to perform updates; use atomic SQL expressions or version-checked CAS operations to ensure your updates are always based on the latest database state.