Design a Ticket Booking System

-- "Give me the four cheapest seats still free." Without SKIP LOCKED every
-- concurrent shopper queues behind the same first candidate row and the onsale
-- serialises. SKIP LOCKED is PostgreSQL 9.5 and later.
BEGIN;

WITH candidate AS (
  SELECT id
    FROM seats
   WHERE event_id = $1
     AND sold_at IS NULL
     AND (held_by IS NULL OR hold_expires_at < now())
   ORDER BY price_cents, row_label, seat_number
   LIMIT 4
     FOR UPDATE SKIP LOCKED   -- FOR UPDATE on its own would block instead
)
UPDATE seats s
   SET held_by = $2,
       hold_expires_at = now() + interval '10 minutes'
  FROM candidate c
 WHERE s.id = c.id
RETURNING s.id, s.row_label, s.seat_number, s.price_cents;

COMMIT;

-- The longhand this replaces, and why it is worse under load:
--   SELECT id FROM seats WHERE ... LIMIT 4 FOR UPDATE;  -- blocks on row 1
--   UPDATE seats SET held_by = ... WHERE id IN (...);   -- second round trip

// No seat identity, just capacity. The entire correctness argument is one
// conditional expression: DynamoDB rejects the write rather than letting
// remaining go below zero.
import { DynamoDBClient } from '@aws-sdk/client-dynamodb';
import { DynamoDBDocumentClient, UpdateCommand } from '@aws-sdk/lib-dynamodb';

const ddb = DynamoDBDocumentClient.from(new DynamoDBClient({}));

// One hot item is capped at 1,000 WCU/s in DynamoDB, so a big onsale shards the
// counter: N stripes, each holding capacity/N, picked at random by the writer.
const STRIPES = 20;

export async function reserveGA(eventId, qty) {
  const stripe = Math.floor(Math.random() * STRIPES);
  try {
    const out = await ddb.send(new UpdateCommand({
      TableName: 'event_inventory',
      Key: { pk: `event#${eventId}`, sk: `stripe#${stripe}` },
      UpdateExpression: 'SET remaining = remaining - :q',
      ConditionExpression: 'remaining >= :q', // the whole guarantee, server side
      ExpressionAttributeValues: { ':q': qty },
      ReturnValues: 'UPDATED_NEW',
    }));
    return { ok: true, remainingInStripe: out.Attributes.remaining };
  } catch (err) {
    // Checking .name avoids depending on which package exports the error class.
    if (err.name === 'ConditionalCheckFailedException') {
      return { ok: false, reason: 'stripe_empty' }; // try another stripe
    }
    // A timeout is NOT a failure: the decrement may have applied. Retry with
    // the same request id and let the reservation record deduplicate.
    throw err;
  }
}

// 10:00 onsale: 400k shoppers, one event's rows. The waiting room exists so the
// strict store only ever sees the rate it can actually serve.
const CHECKOUT_SLOTS = 2_000; // concurrent shoppers allowed into seat selection
const SLOT_TTL_S = 900;       // a slot not used within 15 minutes is reclaimed

// Join: one sorted-set entry per shopper, scored by arrival time.
export async function joinQueue(eventId, shopperId) {
  const key = `queue:${eventId}`;
  await redis.zAdd(key, { score: Date.now(), value: shopperId }, { NX: true });
  const rank = await redis.zRank(key, shopperId); // 0-based position
  return { position: rank + 1 };
}

// Admit: runs on a timer, not per request, so the admission rate is a knob you
// control rather than a function of incoming traffic.
export async function admitBatch(eventId) {
  const active = await redis.sCard(`admitted:${eventId}`);
  const free = CHECKOUT_SLOTS - active;
  if (free <= 0) return 0;

  // Pop the oldest `free` shoppers off the front of the queue. In node-redis the
  // counted form is its own method: zPopMin(key) takes no count and returns one
  // member, so popping a batch is zPopMinCount(key, count) -> [{ value, score }].
  const next = await redis.zPopMinCount(`queue:${eventId}`, free);
  for (const { value: shopperId } of next) {
    await redis.sAdd(`admitted:${eventId}`, shopperId);
    // The token is what the seat-selection endpoints check. Short TTL: an
    // abandoned tab gives its slot back without anyone intervening.
    await redis.set(`token:${eventId}:${shopperId}`, '1', { EX: SLOT_TTL_S });
  }
  return next.length;
}