Seat booking on release day: same seat, same second, who gets it?

FDFS booking opens. Two people open the same show, tap the same seat, and hit Proceed in the same second. Who gets it?

The answer has to be: exactly one of them. Not both (double booking), and not neither (a seat lost to someone who never paid). Here’s a design that gets there with a normal relational database. The SQL is Postgres; MySQL’s InnoDB behaves the same way for everything below.

This is one way to design it, not a claim about how any particular booking app is built.

The idea: don’t book the seat, hold it

Booking happens in two phases:

1. Hold. When you tap Proceed, the seat is reserved for you for 10 minutes. That’s the timer on your screen.
2. Confirm. When payment succeeds, the hold becomes a booking, but only if it’s still yours and still valid.

Everything else is about making those two steps safe when thousands of people hit them at once.

The tables

CREATE TABLE show_seats (
  show_id     bigint,
  seat_id     text,                               -- 'A12'
  status      text NOT NULL DEFAULT 'AVAILABLE',  -- AVAILABLE | HELD | BOOKED
  hold_id     uuid,                               -- who holds it
  held_until  timestamptz,                        -- till when
  PRIMARY KEY (show_id, seat_id)
);

CREATE TABLE booking_seats (
  booking_id  bigint,
  show_id     bigint,
  seat_id     text,
  UNIQUE (show_id, seat_id)                       -- the final safety net
);

• show_seats has one row per seat per show. That row is what everyone fights over, and the database’s row lock is what referees the fight.
• booking_seats holds confirmed seats. UNIQUE (show_id, seat_id) means that even if a future bug gets past every other check, the database refuses to sell the same seat twice.

Step 1: hold the seat with one UPDATE

The tempting version is read, then write:

SELECT status FROM show_seats WHERE show_id = 501 AND seat_id = 'A12';   -- both requests see AVAILABLE
UPDATE show_seats SET status = 'HELD' ... ;                              -- both requests write

Both requests run the SELECT before either one writes, so both see AVAILABLE and both “win”. Wrapping it in a transaction doesn’t help at the default Read Committed level.

Instead, put the check inside the write, as one statement:

UPDATE show_seats
SET status = 'HELD', hold_id = :h,
    held_until = now() + interval '10 min'
WHERE show_id = :s AND seat_id = 'A12'
  AND (status = 'AVAILABLE'
       OR (status = 'HELD' AND held_until < now()));

1 row updated means the seat is yours. 0 rows means it isn’t.

What happens in the same second

1. Request 1 locks the A12 row and updates it to HELD.
2. Request 2 wants the same row, so it waits on the row lock.
3. Request 1 commits. In Postgres at Read Committed, request 2 then re-checks its WHERE against the new version of the row. It sees HELD and not expired, so it skips the row and updates 0 rows. (Postgres docs: Read Committed.) InnoDB gets the same result because an UPDATE reads the latest committed version and locks it.

No application-level lock, no Redis, no distributed anything. The row lock does the work.

Why the expiry lives in the WHERE

The second half of the condition, status = 'HELD' AND held_until < now(), means an expired hold is free again immediately. The next person who taps the seat simply takes it.

So correctness never depends on a cleanup job. You may still run a sweeper that resets expired holds to AVAILABLE, but only so the seat map looks tidy. If the sweeper is late or down, nobody gets double-booked and no seat is stuck.

Step 2: pay, with the hold as the idempotency key

When you call the payment service, send the hold_id as the idempotency key (or the gateway’s reference field). If your call times out and you retry, the gateway sees the same key and returns the original result instead of charging again. (More on this in Idempotency and Timeouts.)

One practical detail: show the user 10:00, but set held_until a little later, say 12 minutes. Payment callbacks can arrive slowly, and you don’t want a user who paid at 9:58 to lose the seat because the gateway’s webhook took a minute.

Step 3: confirm only if the hold is still yours and still valid

BEGIN;

UPDATE show_seats
SET status = 'BOOKED'
WHERE hold_id = :h AND status = 'HELD'
  AND held_until > now()
RETURNING show_id, seat_id;

-- if a row came back:
INSERT INTO booking_seats (booking_id, show_id, seat_id) VALUES (:b, :s, 'A12');

COMMIT;

• hold_id = :h: the hold is still yours.
• held_until > now(): the hold is still valid.

In code: three cases

def on_payment_success(hold_id):
    if confirm(hold_id):            # rows updated
        return issue_ticket(hold_id)
    if already_booked(hold_id):     # webhook came twice
        return existing_ticket(hold_id)
    refund(hold_id)                 # hold expired, seat gone

Result	What it means	What to do
Rows updated	The hold was valid	Issue the ticket
0 rows, but the seat is BOOKED with this hold_id	The payment webhook came twice. Gateways retry deliveries, so duplicates are normal (Stripe’s docs say to expect them)	Return the same ticket. Don’t refund
0 rows otherwise	The hold expired, and someone else took the seat	Refund automatically and tell the user

already_booked is a simple check: SELECT 1 FROM show_seats WHERE hold_id = :h AND status = 'BOOKED'.

The second case is the one people miss. Without it, a duplicate webhook finds 0 rows and refunds a customer who has a valid ticket.

There’s one more edge in the third case: the hold expired, but nobody took the seat yet. You can try to re-hold it for the same user and confirm, and refund only if that fails too. Whether that’s worth the complexity is a product call. Refunding is always safe.

Multi-seat: all or nothing

A family books A12, A13 and A14. They want all three or none. Lock them in one transaction:

BEGIN;

SELECT seat_id FROM show_seats
WHERE show_id = 501 AND seat_id = ANY('{A12,A13,A14}')
  AND (status = 'AVAILABLE' OR (status = 'HELD' AND held_until < now()))
FOR UPDATE NOWAIT;
-- fewer than 3 rows → ROLLBACK → "some of these seats were just taken"

UPDATE show_seats
SET status = 'HELD', hold_id = :h, held_until = now() + interval '12 min'
WHERE show_id = 501 AND seat_id = ANY('{A12,A13,A14}');

COMMIT;

NOWAIT makes the SELECT fail immediately (Postgres error 55P03, lock_not_available) if another transaction has any of those rows locked, instead of queueing behind it. Two things follow:

• Hot seats don’t build a queue of blocked connections on release day.
• No deadlocks. Two users picking overlapping seats (A12–A14 and A13–A15) can’t end up waiting on each other, because nobody waits.

On a lock error, tell the user to try again or pick other seats. (Postgres docs: FOR UPDATE / NOWAIT.)

Confirm works the same way: the confirm UPDATE must return all the held seats, or you roll back and refund.

Release-day scale

The design above is correct at any traffic level. At FDFS-level traffic, you also need it to be fast:

• Serve the seat map from a cache, refreshed every second or two. A slightly stale map is fine, because the hold UPDATE is the source of truth. The worst case is a user taps a seat that just went, and gets “Seat not available” a second earlier than they’d like.
• Keep the transactions tiny. Hold and confirm are single-row (or few-row) updates on a primary key. Never call the payment gateway while holding a database transaction open.
• Partition by show. Different shows never touch the same rows, so show_id is the natural key to split writes across databases if one isn’t enough.
• Use a waiting room for the biggest releases. Let users in batches, so the database sees a steady stream instead of a wall.

What about Redis?

A common alternative is holding seats in Redis: SET hold:501:A12 <hold_id> NX PX 720000 (set only if missing, with a 12-minute expiry), with a Lua script for multi-seat holds. It’s faster, and the expiry is built in.

The cost is that two systems now have to agree about who holds a seat. You still need the database confirm with UNIQUE (show_id, seat_id) as the final word. Start with the database alone; it handles far more than people expect, and you add Redis when measurements say you need it.

Checklist

• ✅ Hold, then confirm. Never book before payment.
• ✅ Hold with one conditional UPDATE, never SELECT then UPDATE.
• ✅ Put the expiry in the WHERE, so expired holds free themselves, without a cron.
• ✅ Send the hold_id as the idempotency key to the payment gateway, and give held_until a small buffer.
• ✅ Confirm only if the hold is still yours and still valid.
• ✅ Handle all three outcomes: issue, same ticket for a duplicate webhook, refund.
• ✅ Multi-seat: FOR UPDATE NOWAIT, all or nothing.
• ✅ Keep UNIQUE (show_id, seat_id) as the safety net under everything.

Same seat, same second: one gets the row and a 10-minute timer to pay. The other gets zero rows, and “Seat not available”.