Booking: Availability, Holds and Double-Booking Prevention

In one line

Booking systems (hotels, flights, cinemas, events) model inventory per time slot or seat, place short holds during checkout, confirm on payment, and must never double-book. Seats use unique constraints per (event, seat); date ranges use exclusion constraints or per-night inventory rows with conditional decrements. Holds expire automatically.

0/8 · 0%

Think of it like this

A cinema seat map. When you select seats they turn grey for everyone else for a few minutes; if you don't pay, they turn white again. Two people can never both walk out with seat F7.

Key ideas

01
Seat-based (cinema, flight): seat_booking(show_id, seat_id) with a unique constraint or an inventory row per seat with a status and hold expiry. UPDATE seat SET status='held', hold_until=... WHERE show_id=? AND seat_id=? AND (status='free' OR hold_until < now()) is atomic.
02
Count-based (hotel room types, event tickets): room_inventory(hotel_id, room_type, night, total, booked) rows per night; booking a stay decrements each night conditionally in one transaction, locking rows in date order.
03
Specific units over ranges (a particular room or vehicle): EXCLUDE USING gist (unit_id WITH =, stay WITH &&).
04
Holds: separate hold records with expiry, or status + hold_until on the inventory row; a sweeper releases expired holds, and reads treat expired holds as free.
05
Search availability is read-heavy and served from caches or a search index (approximate); the booking transaction is the source of truth. Cancellations return inventory and are recorded in history.

Code & diagrams

hotel-inventory.sqlsql

CREATE TABLE room_inventory (
  hotel_id bigint, room_type text, night date,
  total int NOT NULL, booked int NOT NULL DEFAULT 0 CHECK (booked BETWEEN 0 AND total),
  PRIMARY KEY (hotel_id, room_type, night)
);

-- book 2 rooms for nights 10-12 Oct (check-out 13th): all nights or none
BEGIN;
UPDATE room_inventory SET booked = booked + 2
WHERE hotel_id = 5 AND room_type = 'deluxe'
  AND night >= '2026-10-10' AND night < '2026-10-13'
  AND total - booked >= 2;
-- application verifies "UPDATE 3" (3 nights); otherwise ROLLBACK -> not available
INSERT INTO reservation (hotel_id, room_type, check_in, check_out, rooms, status, hold_until)
VALUES (5, 'deluxe', '2026-10-10', '2026-10-13', 2, 'held', now() + interval '15 minutes');
COMMIT;

-- cinema seats: atomic hold
UPDATE show_seat SET status = 'held', held_by = 42, hold_until = now() + interval '8 minutes'
WHERE show_id = 901 AND seat_no IN ('F7','F8')
  AND (status = 'free' OR (status = 'held' AND hold_until < now()));
-- expect 2 rows; otherwise ROLLBACK and tell the user a seat was just taken

Interview problem

The problem

Ticketing for a stadium concert

50,000 seats go on sale at 10:00; 2 million users arrive at once. Users choose specific seats. Design data and flow to prevent double-booking and keep the database alive.

When it breaks

Availability check in cache, booking without database arbitration

What you see

Two users both see F7 free in the cache and both are confirmed, so the seat is double-booked.

Fix & prevent

The cache is only a hint; the conditional UPDATE or unique constraint in the database decides.

Explain it without notes

How do you book multiple nights atomically?

Practice

Model a car-rental system where a specific car can't have overlapping rentals.

Trade-offs

↔
Holds improve user experience but temporarily lock inventory; short expiries and a queue balance fairness and throughput.

Done when you can

I can design seat, count and range-based booking with atomic holds and expiry.

Command Palette