The Oversell — Concurrency at the Row

Two buyers clicked "hold 14C" within four milliseconds of each other. Both requests read status = 'available', both updated it to held, both inserted a holds row, and both buyers paid for the same seat. Nothing was slow, nothing errored, every line of hold_seat was correct in isolation, and the trace of either request on its own looks like a textbook. The bug was that a read and a write in two statements are not one operation, and Topic 32 explained the promise Read Committed makes: each statement sees what was committed before it began, and nothing about the gap between two of them.

The fixes are three, in order of strength, and Stagedoor uses all three. Lock the row before deciding, so the second request waits and then sees the truth. Or decide with a version, so the second write finds nothing to update. And in either case, make the database refuse the second hold outright, so that a bug in the first two cannot sell a seat twice. This topic is the first wound closed, and it is the shape of half the bugs the reader will ever meet.

The Race, Step by Step

Request A arrives on api-01 and request B on api-02, 4 milliseconds apart, both for seat 3117, which is 14C. A reads the seat: available. B reads the seat: available, because A has not written anything yet, and even if A had, A's transaction has not committed, so B could not see it. A updates the status to held and inserts its hold. B updates the status to held, which is a write of the same value on top of A's, and inserts its hold. A commits. B commits. The seat says held, which is true, and the holds table has two rows for it, which is the oversell.

The two reads were both correct: when each ran, the committed state of the row was available. The decision each request made, "this seat is free, hold it," was made on a value that was true at the moment of the read and false by the moment of the write, and nothing connected the two moments. Wrapping the steps in a transaction, which they already were, changes nothing, because the transaction is about atomicity and the problem is about time. The fix has to be one of: make B's read wait until A's write is visible; make B's write depend on B's read still being true; or make B's insert impossible.

Pessimistic: SELECT … FOR UPDATE

FOR UPDATE on the seat's SELECT takes a row lock as part of the read. Request A locks the row at its first statement. Request B's SELECT … FOR UPDATE for the same row blocks until A's transaction ends; when A commits, B's read proceeds, and it returns the row as A left it, held, because a locking read re-reads the row after the lock is granted. B sees held, decides not to hold, and returns 409. The read and the decision and the write are now one operation from every other transaction's point of view, which is what they always needed to be.

async with svc.pool.connection() as conn, conn.transaction():
    cur = await conn.execute(
        "SELECT status FROM seats WHERE id = %s FOR UPDATE", (seat_id,)   # add NOWAIT to fail at once instead of waiting
    )
    row = await cur.fetchone()                      # returns only after any in-flight hold on this row commits
    if row.status != "available":
        raise SeatUnavailable(seat_id)                 # the block rolls back; the transport answers 409
    await conn.execute("UPDATE seats SET status = 'held' WHERE id = %s", (seat_id,))
    await conn.execute(
        "INSERT INTO holds (seat_id, user_id, expires_at) VALUES (%s, %s, now() + interval '10 minutes')",
        (seat_id, buyer.id),
    )
# commit: the lock is released here, about 5 ms after it was taken

The lock lives exactly as long as the transaction, and the transaction is the unit of work from Topic 32: lock, decide, update, insert, commit, in about 5 milliseconds. Nothing leaves the process inside it. The two words at the end of the SELECT decide what B does while A holds the lock. With nothing, B waits, which is right when A will be done in milliseconds. With NOWAIT, B fails immediately with a lock-not-available error, which Stagedoor maps to 409 as well, because a buyer whose seat is being held by someone else at this instant does not benefit from waiting 5 milliseconds to be told so. SKIP LOCKED is the third option, for a query that wants any available row rather than this one; Chapter 7's relay uses it to claim a batch of outbox rows. What none of the three do is help if the lock is taken on the wrong row, or if the transaction then calls Payrail with the lock still held.

Optimistic: the Version Column

The seats table has a version column for the other case. The organizer's editing form reads a seat's price and status along with its version, shows a form, and the human thinks for a minute. A row lock held for a minute is out of the question. Instead the write carries the version it read, and the database checks it as part of the update.

UPDATE seats
   SET status = 'held', version = version + 1
 WHERE id = 3117 AND version = 17;     -- 17 is what this request read
-- UPDATE 1: this request won.  UPDATE 0: someone else wrote first; answer 409.

The update asks the database to change the row only if it is still the row that was read, and increments the version so that the next reader gets a new one. The request whose update reports one row won. The request whose update reports zero rows lost the race: the row's version is no longer 17, because the winner made it 18, and the loser's write touched nothing. No lock was held while the human thought; the cost is that the loser learns it lost only at write time and must retry or report. This is the lost-update check of Chapter 2 moved into the database: If-Match with an ETag is the same comparison made at the HTTP layer, and the 412 there is the zero-row update here.

The Constraint as the Last Line

Both mechanisms above are code, and code has bugs. The day someone adds a second code path that updates a seat without FOR UPDATE or without the version check, the oversell is back and no test notices, because both paths are correct alone. The only fix that survives a bug in the other two is one the database enforces regardless of how the statement was written: a unique constraint that makes a second hold on a seat impossible to insert.

The obvious form does not work. A partial unique index on holds (seat_id) WHERE expires_at > now() would allow one live hold per seat, but Postgres refuses it: an index predicate must be immutable, and now() is not. Stagedoor's canonical form is a plain unique index on holds (seat_id), with expired holds deleted by the worker's sweep in Chapter 8 rather than left in place, so that at any instant a seat has at most one hold row, live or expired-and-not-yet-swept. The alternative that needs no sweep is the conditional write on the seat itself, UPDATE seats SET status = 'held' WHERE id = $1 AND status = 'available', whose zero-row result is the refusal. Either way the second hold is rejected by the database, as a unique violation or as a zero-row update, and the application's only job is to map that rejection to 409 rather than to a 500.

Choosing

The on-sale hold path is the pessimistic case: contention is high, 3,000 requests a second for 2,000 seats, and the transaction is short, lock, decide, commit in 5 milliseconds. A lock held for 5 milliseconds under contention costs the loser 5 milliseconds of waiting, or nothing with NOWAIT, and the code is the simplest of the three. The organizer editing an event is the optimistic case: the read and the write are separated by a human, and no lock can span that; the version column costs nothing until two organizers edit the same event, and then one of them sees "this event changed while you were editing," which is the correct outcome.

Both are wrong without the constraint. That is not caution; it is the observation that the lock and the version are properties of one code path each, and the constraint is a property of the table. Marek's fix on the morning after the spring on-sale was all three, in one migration and one pull request, and the test for it starts two concurrent holds against a real Postgres and asserts that exactly one of them gets a 201.

The Same Shape Everywhere

The double-spend of a wallet balance, the double-booking of a meeting room, the inventory that sells 101 units of a stock of 100, the duplicate username registered twice in the same second: each is a read-then-write on a shared row, each has the same six-step timeline, and each has the same three fixes. The value of having lived through 14C is recognizing the shape before the incident, in the code review, when the diff shows a SELECT followed by a decision followed by an UPDATE and nothing binds them together. The question to ask is the one from Chapter 1: what if two of these run at once? If the answer is "the second one should see the first," it needs the lock, the version or the constraint before it ships.

The book returns to this shape twice more. Chapter 7's idempotency key is a unique constraint on idempotency_keys (user_id, key), and the second request with the same key loses the insert exactly as the second hold does. The same chapter's relay claims its batch of unpublished rows with FOR UPDATE SKIP LOCKED and marks each one published with a conditional update. Same seat, different table.