CSE Roadmap

Core tier · about 3 hours · 7 parts

Part 5 of 7 · 2 min read

The deep dive

SELECT FOR UPDATE SKIP LOCKED and a short lock window as the concrete fix.

Think of the fix like giving each person at the door a numbered ticket the instant they arrive. That replaces everyone physically shoving against the same handle at once. The handle still only turns once per person, but nobody wastes time standing in someone else's way.

In plain words: Rather than many customers directly contending for an update on the same row, the application can have a small pool of workers claim available seats using SELECT ... FOR UPDATE SKIP LOCKED. This lets each worker grab one of the currently unlocked available seats without piling up behind a seat another worker already has locked. Keeping the hold's write transaction short, touching only the one seat row, keeps lock hold time minimal and avoids the traditional reservation-queue pile-up. The short hold window itself, a few minutes, also matters: it bounds how long an abandoned selection keeps a seat locked away from other customers, directly limiting how bad contention gets during the worst moment of the sale.

The key lines.

-- A worker claims one available seat, skipping any seat already locked by another worker
BEGIN;
SELECT seat_id FROM seats
 WHERE event_id = 501 AND status = 'available'
 ORDER BY seat_id
 FOR UPDATE SKIP LOCKED
 LIMIT 1;

UPDATE seats
   SET status = 'held', held_until = now() + interval '5 minutes'
 WHERE seat_id = :claimed_seat_id;
COMMIT;

SKIP LOCKED (seat claiming)

SKIP LOCKED, used here inside a SELECT FOR UPDATE, lets a transaction claiming one of several candidate rows skip past any row another transaction already has locked, rather than waiting for it, so many workers can claim distinct seats in parallel with minimal blocking.

Example: the claim query above picks whichever available seat is not already locked by a concurrent worker, instead of queueing behind one specific seat.