Databases · interactive

The lost update

Two requests add money to the same account at the same moment. Each reads the balance, adds in the application, and writes it back. The database did exactly what it was told, and one deposit vanished. Four ways to stop it.

balance

100

db balance = 100. T1 adds 30, T2 adds 50. It should end at 180.

balance = 100. T1 adds 30, T2 adds 50. It should end at 180.

How the code updates

SELECT balance …; UPDATE … SET balance = :new

step 1 of 6

Step 1 of 4 · read left to right

1
Read, change in code, write back
The common pattern: SELECT, compute in the app, UPDATE with the new number. Fine alone; broken when two run at once.
Read Committed (the default) allows this.
2
Lock what you read: FOR UPDATE
The second transaction waits at its read until the first commits, then reads the new value.
Costs some waiting; always correct.
3
Stronger isolation: SERIALIZABLE
The database spots the conflict and fails one transaction. The app must catch the error and retry.
Correct, but only if you write the retry.
4
Do the math in the database
balance = balance + 30 reads and writes under one row lock. Simplest fix when the change is a plain addition.
- Read, then write150
- SELECT … FOR UPDATE180
- SERIALIZABLE180
- Atomic update180

In production

Tools and technologies

The same bug appears in any database; the fixes are standard SQL.

Not tied to one language — use these names when you search docs or design a system.