Two people, one row: lost updates and how the database prevents them

The counter that goes wrong under load

A course has 1 seat left. Two people click “enrol” at the same moment. Each request runs the same code: read seats_left, see 1, subtract one, write 0. Both read 1. Both write 0. Two students are enrolled in one seat, and the table says nothing is wrong.

This is a lost update, and it is the fundamental problem of concurrent writes: a read and a write that are two statements can have another transaction slip between them. Everything in this lesson is a way of closing that gap.

Make the read and the write one statement

UPDATE courses
SET    seats_left = seats_left - 1
WHERE  id = 42 AND seats_left > 0
RETURNING seats_left;

The database reads and writes the row under a lock it takes for the duration of the statement, so the second request cannot read between the first’s read and write. It sees the row after the first request’s write, seats_left is 0, the WHERE clause fails, and it updates nothing. RETURNING tells the application whether it won: a row means enrolled, no row means full. This is the first thing to reach for, and it handles most counters, balances and stock levels with no further machinery.

Lock the row for a read-then-write

When the logic between the read and the write is too complicated for one statement, lock the row explicitly:

BEGIN;
SELECT seats_left FROM courses WHERE id = 42 FOR UPDATE;
-- application decides what to do
UPDATE courses SET seats_left = seats_left - 1 WHERE id = 42;
COMMIT;

FOR UPDATE makes the second transaction’s SELECT ... FOR UPDATE wait until the first commits, and then it sees the updated value. The gap is closed by making the second reader queue. The cost is that the row is locked for the length of the transaction, which is why the previous lesson said to keep transactions short: a FOR UPDATE held while a human thinks is a course nobody can enrol in.

Or detect the collision instead of preventing it

Optimistic concurrency skips the lock and checks at write time whether anyone else got there first, using a version number:

UPDATE courses
SET    seats_left = 0, version = version + 1
WHERE  id = 42 AND version = 7;

The application read version 7. If another transaction has already updated the row, the version is 8, the WHERE fails, zero rows are updated, and the application re-reads and tries again. No lock is held between the read and the write, so it scales to many readers, at the cost of retries when collisions are common. Web applications use this pattern constantly, and the version column, or an updated_at timestamp used the same way, is the tell.

What a transaction can see

Postgres’s default isolation level is Read Committed: each statement sees the data as committed at the moment the statement started. Two SELECTs in one transaction can therefore see different data if someone committed in between. REPEATABLE READ freezes the view for the whole transaction, and SERIALIZABLE goes further and refuses to commit any transaction whose result could not have come from running the transactions one after another. The refusal appears as an error, could not serialize access, and the application must retry. Stricter levels trade retries for correctness; the atomic-update pattern above is correct at every level, which is another reason to prefer it.

Deadlock

Transaction A locks row 1 and then wants row 2. Transaction B locks row 2 and then wants row 1. Each waits for the other forever. Postgres notices within a second, kills one of them with deadlock detected, and the other proceeds. The killed one must retry. The prevention is to lock rows in a consistent order, for instance by id ascending, so that two transactions wanting the same rows always queue rather than cross. A transfer between two accounts that always locks the lower id first cannot deadlock with another transfer.

The engines differ, again

SQLite allows one writer at a time for the whole database file. A second writer gets database is locked and must wait or retry, which is fine for a phone app and is the reason SQLite is not the choice for a busy web service. DuckDB is similar, and built for a single analyst rather than a crowd. Postgres and MySQL lock rows, not files, so thousands of writers touching different rows proceed together, and the patterns in this lesson only matter when they touch the same one.