PHASE 12Advanced ~7 min· topic 38 of 39Level 6

System 12.38 — Hotel Reservation System

In one line

Book rooms without double-booking under concurrency and overbooking policies: inventory per room type per date, strong consistency on the booking path, idempotent reservation requests, and caching for search.

0/39 · 0%

Think of it like this

A hotel front desk with a ledger of rooms per night. Two agents on the phone must not both sell the last deluxe room for 12 October. The ledger entry is checked and updated in one careful step.

Key ideas

01
Requirements: 5,000 hotels, 1 M rooms, search availability by city and dates (read-heavy, can be slightly stale), reserve and pay (write path must be correct), 10% overbooking allowed per policy, cancellations.
02
DATA MODEL: room_type_inventory(hotel_id, room_type_id, date, total, reserved); a reservation for 3 nights touches 3 rows. The rule: reserved + requested <= total * 1.1 for every date in the stay.
03
CONCURRENCY: do it in one DB transaction with either PESSIMISTIC locking (SELECT ... FOR UPDATE on the inventory rows) or OPTIMISTIC concurrency (version column, retry on conflict), or a database CHECK constraint so the update fails when over capacity. Low contention per row makes optimistic locking efficient.
04
IDEMPOTENCY: the client gets a reservation ID before submitting; submitting twice returns the same reservation (Phase 6B, idempotency keys). PAYMENT happens in a saga (reserve → pay → confirm, or release on failure/timeout) (Phase 11, saga). SEARCH reads from caches/replicas; the final check always hits the primary.

Code & diagrams

reserve without double-bookingsql

BEGIN;
UPDATE room_type_inventory
   SET reserved = reserved + 1, version = version + 1
 WHERE hotel_id = 7 AND room_type_id = 3
   AND date BETWEEN '2026-10-12' AND '2026-10-14'
   AND reserved + 1 <= total * 1.10;          -- overbooking policy
-- if updated rows != 3 nights → ROLLBACK (sold out on some date)
INSERT INTO reservation (id, hotel_id, room_type_id, check_in, check_out, status)
VALUES ('r-5f1c...', 7, 3, '2026-10-12', '2026-10-15', 'PENDING_PAYMENT')
ON CONFLICT (id) DO NOTHING;                 -- idempotent resubmission
COMMIT;

Explain without notes

Why check availability again on the primary even if search showed the room as available?

Practice

Payment fails after inventory was reserved. What happens?

Trade-offs

↔
Pessimistic locks are simple but serialise hot inventory; optimistic concurrency scales better under low contention; conditional updates are simplest where the rule fits in SQL.

Run it in production

You've designed it. Now build, operate, and break the same idea hands-on in the DevOps courses:

Stateful · Connections, pooling & query performance

EXPLAIN ANALYZE, indexes, PgBouncer, and lock contention on a live database.

Stateful · Replication, failover & migrations

Streaming replication, lag, failover, and zero-downtime schema changes.

Completion checklist

I can prevent double-booking under concurrency and handle payment failure

Back to phase

Command Palette