The lost update, demonstrated and then fixed

This lesson is one bug, run against a real server, and the four ways to fix it. It is the most important thirty-five minutes in the course, because the bug is invisible in testing and appears exactly when the system starts being used.

The setup

One seat left. Two people book it at the same moment.

CREATE TABLE seats (
  id   int PRIMARY KEY,
  free int NOT NULL CHECK (free >= 0)
);
INSERT INTO seats VALUES (1, 1);

The obvious code: read how many are free, check it is more than zero, write the new number.

The bug

Two sessions, both at the default isolation level.

-- session A                              -- session B
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT free FROM seats WHERE id=1;        --  1
                                          BEGIN ISOLATION LEVEL READ COMMITTED;
                                          SELECT free FROM seats WHERE id=1;   -- 1
                                          UPDATE seats SET free = 0 WHERE id=1;
                                          COMMIT;
UPDATE seats SET free = 0 WHERE id=1;
COMMIT;

SELECT free FROM seats WHERE id=1;

 free
------
    0

Both sessions read 1. Both decided a seat was available. Both sold it. The seat count is 0, which looks correct, and two people are holding a booking for one seat.

No error. No warning. Both transactions committed successfully. CHECK (free >= 0) did not help, because 0 is a legal value.

This is the lost update: B's write was overwritten by A's, which was computed from a value that was already stale.

It is allowed at read committed, which is the default, which is what you are using.

And note it happened inside transactions. Atomicity did not prevent it, because each transaction was individually atomic — the problem is between them, and that is isolation, not atomicity.

Why testing does not find it

The two sessions have to overlap within milliseconds. Click the button twice by hand and the first finishes before the second starts. Run your test suite and it passes.

Then you launch, two people book at once, and you have oversold.

It scales with traffic, so it appears exactly when things are going well.

Fix 1: put the condition in the write

The best fix for this shape of problem.

UPDATE seats SET free = free - 1 WHERE id = 1 AND free >= 1;

Read nothing first. The database evaluates the condition and performs the arithmetic in one atomic statement, on whatever the value is at that instant.

Run the same race:

session that got there first:      UPDATE 1
session that arrived second:       UPDATE 0
final free:                        0

The second session's UPDATE matched zero rows.

What happened underneath is the read-committed behaviour from the previous lesson: the second UPDATE found the row locked, waited for the first to commit, then re-evaluated its WHERE against the new version — where free was 0, so free >= 1 was false and it matched nothing.

The row count is the answer. UPDATE 0 means somebody got there first, and your application must check it:

cur.execute("UPDATE seats SET free = free - 1 WHERE id = %s AND free >= 1", (seat_id,))
if cur.rowcount == 0:
    raise SoldOut()

This is compare-and-swap, and it is the same pattern as the Full-Stack course's stock decrement. Two properties make it good: no separate read, so no window; and one round trip.

Use this whenever the update can be expressed as a condition on the current value.

Fix 2: SELECT ... FOR UPDATE

When you genuinely must read, think, and then write:

BEGIN;
SELECT free FROM seats WHERE id = 1 FOR UPDATE;   -- locks the row
-- … decide …
UPDATE seats SET free = free - 1 WHERE id = 1;
COMMIT;                                            -- lock released

FOR UPDATE takes a row-level lock. A second session running the same SELECT ... FOR UPDATE blocks until the first commits, then reads the new value.

Running the race with FOR UPDATE, the second session blocked, then read free as 0, then its free - 1 produced −1:

ERROR:  new row for relation "seats" violates check constraint "seats_free_check"
DETAIL:  Failing row contains (1, -1).

The constraint caught it — because the lock forced the second session to see reality, the bad value became visible instead of being silently overwritten. An error is enormously better than an oversold seat, and in real code you would check free after the locked read rather than relying on the constraint.

Variants worth knowing:

SELECT ... FOR UPDATE NOWAIT;         -- error immediately rather than waiting
SELECT ... FOR UPDATE SKIP LOCKED;    -- skip rows somebody else has locked
SELECT ... FOR SHARE;                 -- others may read, nobody may write

SKIP LOCKED is how you build a work queue: several workers each take the next unlocked job, and none of them wait for each other.

Always lock rows in a consistent order — by primary key, ascending. Two transactions locking the same rows in different orders is a deadlock, which is the next lesson.

Fix 3: a stricter isolation level

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT free FROM seats WHERE id = 1;
UPDATE seats SET free = 0 WHERE id = 1;
COMMIT;

Running the same race:

ERROR:  could not serialize access due to concurrent update

The database refused rather than losing the write. One transaction committed; the other was aborted and must retry.

That is a correct outcome, and it is only useful if you wrote the retry loop. Without one, you have converted a silent data bug into a visible user-facing error — which is better, and still not right.

Use this when the logic is too complicated to express as a conditional update.

Fix 4: an application-level lock, for things not in a table

SELECT pg_advisory_xact_lock(hashtext('nightly-report'));

An advisory lock — named by a number you choose, released at commit, and not attached to any row. It is the tool for "only one worker should run this at a time" where there is no row to lock.

Not for row contention; use fixes 1 to 3 for that.

Choosing

Can the change be a condition on the current value?   Fix 1 — conditional UPDATE. Best.
Must you read, decide, then write?                    Fix 2 — SELECT ... FOR UPDATE
Is the logic too complex for either?                  Fix 3 — repeatable read + retry
Is there no row to lock?                              Fix 4 — advisory lock

Reach for fix 1 first, every time. It is the shortest, the fastest, and it has no window between the read and the write because there is no read.

The shapes to recognise

You are at risk whenever you see this in code:

row = db.query("SELECT ... WHERE id = ?")     # read
if row.something:                              # decide
    db.execute("UPDATE ... SET ... WHERE id = ?")   # write

The gap between the read and the write is where another transaction fits. Specific cases that come up constantly:

• Stock and seats. The example above.
• Balances. Read balance, check it covers the amount, subtract.
• Counters. SET views = :value from a previous read, instead of views = views + 1.
• "Only one can be primary." Read to check none is, then set one.
• Uniqueness checked in code. SELECT to see if the email exists, then INSERT — two requests both find nothing and both insert. Use a UNIQUE constraint; it is the only thing that actually works.
• Status transitions. Read status, check it is pending, set it to paid. Do it as UPDATE ... WHERE status = 'pending' and check the row count.

That last rewrite is fix 1 applied to a status, and it is the single most useful instance of the pattern.

Check your work

What the lost update is. Two transactions read the same value, both write, and one write disappears.

Whether transactions prevent it. No — it is an isolation problem, not an atomicity one.

At which level it is allowed. Read committed, which is the default.

Why tests do not find it. The sessions must overlap within milliseconds, and it scales with traffic.

What fix 1 is. Put the condition in the UPDATE and check the row count.

Why the second UPDATE matched zero rows. Read committed waits for the other transaction, then re-evaluates the WHERE against the new version.

What UPDATE 0 means to your application. Somebody got there first — and it must be checked.

What FOR UPDATE does. Locks the row so a second reader blocks until commit.

What SKIP LOCKED is for. A work queue where workers do not wait for each other.

In what order to lock rows. A consistent one — by primary key.

What repeatable read does instead of losing the write. Aborts with could not serialize access due to concurrent update.

What that fix requires. A retry loop.

What an advisory lock is for. Mutual exclusion with no row to lock.

The one to reach for first. Fix 1 — no read, so no window.

The correct fix for "check the email is unique, then insert". A UNIQUE constraint.

Practice

1. Create the seats table and reproduce the lost update in two psql windows. Use pg_sleep to widen the window.
2. Confirm both transactions committed without error.
3. Rewrite it as a conditional UPDATE and run the race again. Record both row counts.
4. Add the row-count check in application code and make it raise.
5. Run the race with SELECT ... FOR UPDATE and watch the second session block.
6. Do the same with NOWAIT and read the error.
7. Build a three-row job queue and take jobs from two sessions with FOR UPDATE SKIP LOCKED.
8. Run the race at REPEATABLE READ and read the serialization error.
9. Write the retry loop and confirm the retried transaction succeeds.
10. Deliberately lock two rows in opposite orders from two sessions. Note what happens — the next lesson names it.
11. Take a counter and update it as SET views = :value from a prior read, race it, and lose counts. Then use views = views + 1.
12. Implement "check the email does not exist, then insert" in application code, race it, and create two members with the same email. Then add a UNIQUE constraint and race it again.
13. Find a read-decide-write sequence in a project of yours and rewrite it as fix 1.

Official documentation

• PostgreSQL — Transaction isolation — Including the explicit description of read committed re-evaluating the WHERE after waiting.
• PostgreSQL — Explicit locking — FOR UPDATE, FOR SHARE, NOWAIT, SKIP LOCKED and the row-level lock modes.
• PostgreSQL — SELECT ... FOR UPDATE — The locking clause in full.
• PostgreSQL — Advisory locks — Session and transaction scoped, with the function list.

Next: locks, deadlocks, and reading a deadlock report.