The lost update

A shop has 10 of something in stock. Two customers buy one each at almost the same moment. Each purchase is a transaction that reads the stock, subtracts one in the application, and writes the result back.

After both, the stock should be 8.

A reads the stock and gets 10. B reads it a moment later and also gets 10. Nothing has been written yet, so both are right.

A’s code computes 10 − 1 and writes 9. A commits.

From the database’s point of view, B did something perfectly normal: it wrote the number 9 to a row nobody else was using at that moment. It has no way of knowing that 9 came from a value B read earlier, which had since gone stale.

The mistake lives in the gap between the read and the write, and that gap is in your application.

Go back to read-then-write, under REPEATABLE READ. Postgres notices that B is about to overwrite a row that changed after B’s snapshot was taken. Instead of silently losing A’s work, it aborts B with a serialization error.

B runs again, reads 9, and writes 8. No data is lost, but your code needs a retry loop.

You can stop here and you’ll know how to avoid lost updates. The next six steps are what actually happens when two writers meet on one row.

Every UPDATE locks the row it changes until its transaction ends. Postgres keeps that lock in the row itself: the old version’s xmax holds the id of the transaction changing it.

In this order B’s update arrives while A is still open, so B waits. Readers ignore this lock completely. Only other writers queue behind it.

Under REPEATABLE READ the wait ends differently. If A commits, B gets the serialization error, because the newest version isn’t one B’s snapshot can see.

If A had rolled back instead, B would carry on as if nothing happened. Whether B fails depends on how A finished, not on anything B did.

Sometimes the decision needs application logic, like checking a credit limit before selling. Then lock the row as you read it: SELECT … FOR UPDATE. B’s read waits at the first line until A is done, then reads 9.

It’s correct under READ COMMITTED. The cost is that B sits idle while A holds the lock, including any slow work A does before committing.

Under REPEATABLE READ, the locked read itself fails with the serialization error.

Locking reads bring a new way to fail. If A locks row 1 and then row 2, while B locks row 2 and then row 1, each ends up waiting for the other forever.

Postgres checks for this after deadlock_timeout, one second by default, and aborts one of them. Lock rows in a consistent order, for example by id, and it can’t happen.

My rule of thumb. If the change fits in one UPDATE, write it that way. If the application has to decide, use FOR UPDATE and keep the transaction short. If many rows are involved and conflicts are rare, use REPEATABLE READ and retry on 40001.

A version column, UPDATE … WHERE version = 7, is the same idea done by hand. It also works across two HTTP requests, where no transaction can stay open.