Section 7: One Statement Decides Who Gets Seat 8 - Deep Dive: Seat Holds & Contention

Executive Summary: You choose the hold, because the invariant "a seat is sold at most once" lives there and nowhere else. The hold is one transaction: a locked check of the fan's 4-ticket limit, then one conditional update that locks seats in sorted order, checks expiry lazily in the predicate, and rolls back unless every seat changed. It beats Redis locks (a failover can lose a hold and double-sell) and SELECT ... FOR UPDATE (locks held across client round trips). One show's peak fits a single-leader store easily; contention shows up as 409s, not load, and the server turns each lost race into an alternative.

About twenty-five minutes in, the interviewer puts the marker down. "Good overview. Where do you want to go deep?" The waiting room is the flashy answer, and Section 8 gets it, but it only decides who gets to try. Something else decides who wins.

You: "The hold. The correctness of the whole business lives there. If it's right, the worst case is a fan who sees a 409. If it's wrong, we sell seat 8 twice and two people meet at the gate."

The Hold as One Transaction

Section 5 gave the ShowSeat row. Here is the statement that changes it, for Jordan's first try:

BEGIN;
SELECT tickets_owned FROM account_show
WHERE  account_id = :acct AND show_id = 'E-7781'
FOR UPDATE;
-- release the fan's previous live hold, if any (a new hold replaces it);
-- then tickets_owned + 2 must be <= 4, else ROLLBACK and 409 ticket_limit

INSERT INTO holds (hold_id, account_id, show_id, seats, expires_at, idempotency_key)
VALUES (:hold_id, :acct, 'E-7781', :seats, now() + interval '8 minutes', :idem);

UPDATE show_seat s
SET    status = 'held', hold_id = :hold_id,
       hold_expires_at = now() + interval '8 minutes',
       version = s.version + 1
FROM  (SELECT seat_id FROM show_seat
       WHERE  show_id = 'E-7781'
         AND  seat_id = ANY(:sorted_ids)  -- 112-F-7, 112-F-8
       ORDER  BY seat_id
       FOR UPDATE) l
WHERE  s.show_id = 'E-7781'
  AND  s.seat_id = l.seat_id
  AND  (s.status = 'available'
        OR (s.status = 'held' AND s.hold_expires_at < now()));
-- rows affected must equal 2; otherwise ROLLBACK and return 409
COMMIT;

Five things are doing the work:

• The account row comes first. Locking it serialises one fan's concurrent holds, so two tabs can't both slip under the 4-ticket limit.
• The predicate is the check. There is no separate read. The WHERE clause only matches seats that are free right now, so check and write are one step.
• Lazy expiry. "Held but expired" counts as available inside the predicate. Expiry needs no timer to be correct: the next hold attempt simply wins. A background sweeper later resets expired rows to available and emits change events so the bitmap shows them free. For reserved seats, if the sweeper stops, nothing double-sells; the map just shows some seats held for too long. Sweeper-only release would leave seats stuck until the job ran, and client-driven release fails because abandoned tabs never send it. Section 2 defends the 8 minutes.
• The row count. Two seats requested, two rows changed, or nothing.
• The version column increments on every transition, so the availability service can drop out-of-order events and checkout can confirm the row it held.

Interviewer: "What isolation level does this need? Serializable?"

You: "Read committed is enough. Each row's check-and-set is atomic: the statement locks every seat it touches. If another transaction changed seat 8 first, mine waits for it to commit, then re-evaluates the WHERE against the committed version, as PostgreSQL and InnoDB both do under read committed. Seat 8 is now held and unexpired, so it doesn't match. Under PostgreSQL's repeatable read or serializable, mine aborts with a serialization error instead, and the service retries or returns 409. InnoDB's UPDATE always reads the latest committed row, so it behaves the same as read committed. Either way only one transaction flips seat 8. What row-level atomicity doesn't give me is all-or-nothing across seats 7 and 8. That comes from the transaction plus the row-count check: one row of two means rollback, and seat 7 is untouched."

Interviewer: "So what would break under read committed?"

You: "Reading status in the app, then writing by id without the predicate. Two fans both read 'available' and both write 'held'. The bug is the gap between the check and the write, not the isolation level."

That subquery is about lock order. PostgreSQL's UPDATE has no ORDER BY: it locks rows in whatever order the plan visits them. If fan A (7, 8) locks 7 while fan B (8, 7) locks 8, each waits on the other and the database kills one. So the service sorts seat ids, and ORDER BY seat_id FOR UPDATE takes the locks in that order inside the same single statement. With one order there is no cycle: B waits a few milliseconds on seat 7, then finds both seats held and rolls back. The locks never span a client round trip, which is the real objection to SELECT ... FOR UPDATE in the app, so a wait lasts only as long as another hold's commit.

Key Insight: The predicate makes each seat's decision atomic; the transaction makes the order's decision atomic; sorted seat ids keep the brief lock waits from ever forming a cycle. Three separate guarantees, and a strong answer names all three.

Quick check before moving on: