Check-then-act races

Claiming a seat looks like two steps: check that it is free, then mark it taken. If two buyers run both steps at the same moment, both can pass the check. The fix is to make the check and the write a single statement, which the database applies as one step.

The big idea

Two people reach for the last parking spot at once. Each one looks, sees it empty, and parks. Both are now wrong.

The fix is a gate that lets exactly one car into the spot, and decides at the gate. The database's conditional update is that gate. The picture breaks if the gate itself is not atomic, which is why the check and the write must be one statement.

Walking through it

The broken version looks like this. Both buyers read before either writes:

Buyer A reads seat 14C: available
Buyer B reads seat 14C: available
Buyer A writes: held by A
Buyer B writes: held by B    ← both believe they won

The fixed version is one UPDATE that includes the check in its WHERE clause. The row count says who won:

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE seats (seat TEXT PRIMARY KEY, status TEXT, holder TEXT)")
db.execute("INSERT INTO seats VALUES ('14C', 'available', NULL)")

def claim(user):
    cur = db.execute(
        "UPDATE seats SET status = 'held', holder = ? "
        "WHERE seat = '14C' AND status = 'available'",
        (user,),
    )
    return cur.rowcount

print("A claims:", claim("alice"))
print("B claims:", claim("bob"))
print(db.execute("SELECT * FROM seats").fetchall())

Output:

A claims: 1
B claims: 0
[('14C', 'held', 'alice')]

This script runs the two claims one after the other, so it shows the row count, not the timing. In a real database, the second UPDATE waits for the first to finish. It then rechecks the WHERE clause against the new state, finds the seat taken, and matches zero rows. That is the default behaviour in Postgres.

Three approaches

Common mistakes

• Reading in the app, then writing. This is the classic race, and it passes tests because it rarely fails in a quiet system.
• Ignoring the row count. An UPDATE that changed zero rows is a lost race, not a success.
• Holding a lock across a slow call. Keep the transaction to the claim itself, not the payment that follows.

Check yourself

1. The second UPDATE returns 0 rows. What should the code do?

Answer: Report the seat as taken and let the user choose another, because someone else claimed it first.

2. Why is SELECT … FOR UPDATE a weak choice at onsale scale?

Answer: Lock waits queue behind one row, and transactions stay open for the whole check, so timeouts cascade.

Key takeaways

• A check followed by a separate write can lose races.
• One conditional UPDATE checks and claims in a single step.
• Always read the row count. Zero means you lost.
• Keep transactions short, so waiting claimants do not pile up.

Further learning

Next, see how claims expire on their own in timed holds and lazy expiry.