Booking a hotel room looks like a shopping cart with dates. The traffic is modest; the difficulty is correctness. Two guests click "Book" on the last deluxe room for the same weekend; a nervous guest double-clicks; a payment succeeds just after the hold expired. Interviewers use this question to see whether you can protect a small, contended inventory with the right transaction, and keep money and rooms in agreement.
The hotel-reservation interview end to end: guests book a room type for a range of nights, never beyond inventory plus the hotel's overbooking limit, about 30 bookings and 3,000 availability reads a second at peak. Search reads a cached snapshot; the booking decision is one PostgreSQL transaction that inserts a PENDING reservation and reserves every night. Deep dives show the check-then-act race, the conditional update that fixes it, all nights or none, and the hold, pay, confirm saga with expiry, idempotency keys for double clicks and refunds for late payments.
Phase 1: Requirements (5 minutes)#
"Do guests book a specific room number, or a room type?"
"A room type: 'deluxe king, non-smoking'. The hotel assigns the actual room at check-in."
"That simplifies inventory a lot: I only need a count per room type per night. And overbooking: does the hotel allow it?"
"Some hotels sell up to 10% more than they have, because a few guests always cancel. Make it a per-hotel setting."
- Search hotels by city, dates and guests; see available room types and prices
- Reserve a room type for a check-in and check-out date
- Pay to confirm the reservation; get a confirmation number
- Cancel (with the hotel's refund policy)
- Hotel staff set inventory, prices and an overbooking limit
- Out of scope: loyalty points, reviews, room assignment at check-in
- Correctness first: never reserve more than
inventory × (1 + overbooking)for any night - Idempotent booking: retries and double clicks create exactly one reservation
- Money matches rooms: no confirmed reservation without payment, no payment without a reservation (or a refund)
- Latency: search in under 500 ms; a reservation in about a second
- Availability: search can be slightly stale; the booking decision cannot
Phase 2: Estimation (5 minutes)#
- Rooms: 1M across 5,000 hotels, ~70% occupied, average stay 3 nights
- New reservations: 1M × 0.7 ÷ 3 ≈ 233,000/day ≈ 3/s on average, ~10x at peak ≈ 30/s
- Reads: a typical funnel has many more views than bookings; assume ~100x → ~3,000 availability reads/s at peak
- Inventory rows, one per (hotel, room type, night): 5,000 × 20 room types × 365 nights ≈ 36.5M rows, a few GB
- Reservations: 233,000/day × ~1 KB ≈ 85 GB/year
"30 writes a second fits one relational database with room to spare, so I'll use one, with real transactions, rather than reaching for anything exotic. The hard part is two of those 30 writes wanting the same last room."
Phase 3: API (3 minutes)#
GET /v1/hotels/search?city=goa&check_in=2026-12-24&check_out=2026-12-27&guests=2
→ 200 [ { "hotel_id": "h_12", "room_types": [ { "id": "deluxe_king", "price": 9800, "available": true } ] } ]
POST /v1/reservations
Idempotency-Key: 7d0c3a1e-… (generated when the booking page loads)
{ "hotel_id": "h_12", "room_type_id": "deluxe_king", "check_in": "2026-12-24", "check_out": "2026-12-27", "guests": 2 }
→ 201 { "reservation_id": "r_9001", "status": "PENDING", "hold_expires_at": "...+10 min", "amount": 29400 }
POST /v1/reservations/r_9001/payment { "payment_method": "..." } → 200 { "status": "CONFIRMED" }
DELETE /v1/reservations/r_9001 → 200 { "status": "CANCELLED", "refund": ... }Phase 4: High-level design (10 minutes)#
%% title: Hotel reservations: a cacheable read side, one transactional database for the booking decision
flowchart TD
G@{ icon: "sd:browser", label: "Guests · web / app" }
GW@{ icon: "sd:gateway", label: "API gateway" }
SR["Search service<br/>hotels + cached availability"]:::service
ES@{ icon: "tech:elasticsearch", label: "Elasticsearch<br/>hotels by city, amenities" }
RC@{ icon: "tech:redis", label: "Redis<br/>availability snapshot · 1 min" }
RS["Reservation service<br/>hold · confirm · cancel"]:::core
DB@{ icon: "tech:postgresql", label: "PostgreSQL<br/>inventory + reservations" }
PSP@{ icon: "sd:card", label: "Payment provider" }
HS@{ icon: "sd:building", label: "Hotel staff portal" }
G --> GW
GW -->|"browse"| SR
SR --> ES
SR --> RC
GW -->|"book"| RS
RS --> DB
RS --> PSP
HS -->|"inventory, prices"| DB
DB -.->|"changes refresh snapshot"| RC
class G client
class GW edge
class ES store
class RC cache
class DB store
class PSP external
class HS clientThe data model is three small tables:
room_type_inventory (hotel_id, room_type_id, date, -- one row per night
total_inventory, total_reserved,
PRIMARY KEY (hotel_id, room_type_id, date))
reservations (reservation_id, idempotency_key UNIQUE, guest_id, hotel_id, room_type_id,
check_in, check_out, status, hold_expires_at, amount)
hotels (hotel_id, ..., overbooking_pct)- Browse. Search reads hotels from Elasticsearch and availability from a Redis snapshot that may be a minute old. That's fine: it's a hint, not a promise.
- Hold. The reservation service, in one transaction, inserts the
PENDINGreservation (the unique idempotency key stops duplicates) and reserves one unit on each of the three nights. - Pay. The guest pays through the payment provider within the 10-minute hold.
- Confirm. On success the reservation becomes
CONFIRMED. If the hold expires first, a sweeper releases the three nights.
Phase 5: Deep dives (15 minutes)#
Deep dive 1: The last room, wanted by two guests#
Check, then book?
The service runs SELECT total_reserved, total_inventory … for each night, sees one room left, and then runs UPDATE … SET total_reserved = total_reserved + 1. Two guests do this at the same moment. What happens, and how do you fix it?
One strong answer
- Both see one room left. Both reads happen before either write, both updates succeed, and the hotel is oversold by one. This is the classic check-then-act race; the default isolation level of most databases (read committed) doesn't prevent it.
- Fix it inside the write. Make the check part of the update:
… SET total_reserved = total_reserved + 1 WHERE … AND total_reserved + 1 <= total_inventory * (1 + overbooking). The database evaluates the condition on the row as it locks it, so the second guest's update matches zero rows. - Multi-night: run it for all nights in one transaction and require that every night's row was updated. If any night fails, roll back all of them.
Three standard ways to protect the inventory row, and when each fits:
Pessimistic lock
SELECT … FOR UPDATEon the nights, then check and update- Simple to reason about
- Holds row locks for the transaction; lock order matters to avoid deadlocks
- Fine at low contention, slow at high
Optimistic (version)
- Read with a
version, updateWHERE version = $read_version - No locks held while the user thinks
- Losers retry; many retries under heavy contention
- Good when conflicts are rare
Conditional update + constraint
- The check lives in the
UPDATE'sWHERE - Plus a
CHECKconstraint as a safety net - One round trip, no read-then-write window
- Our choice: 30 bookings/s, rare conflicts
BEGIN;
INSERT INTO reservations (reservation_id, idempotency_key, …, status, hold_expires_at)
VALUES ('r_9001', '7d0c3a1e-…', …, 'PENDING', now() + interval '10 minutes');
UPDATE room_type_inventory i
SET total_reserved = total_reserved + 1
FROM hotels h
WHERE i.hotel_id = 'h_12' AND h.hotel_id = i.hotel_id
AND i.room_type_id = 'deluxe_king'
AND i.date >= '2026-12-24' AND i.date < '2026-12-27'
AND i.total_reserved + 1 <= floor(i.total_inventory * (1 + h.overbooking_pct / 100.0));
-- the service checks: rows updated = 3 nights, otherwise ROLLBACK → "sold out"
COMMIT;When would this change? A hotel that opens New Year's Eve rooms at a fixed time, with thousands of people clicking at once, is a different problem: a high-contention flash sale, which needs admission control in front of the database (see the IRCTC side quest).
Deep dive 2: Holds, payment and the double click#
Money and rooms live in different systems: our database and the payment provider. No single transaction can span both, so the reservation is a small saga with explicit states:
PENDING (hold)
Inventory is reserved and hold_expires_at is set. Nothing has been charged yet.
CONFIRMED
Payment succeeded. The payment call carries the reservation ID as its idempotency key, so a retried charge can't bill twice.
EXPIRED
No payment within 10 minutes. A sweeper (running every minute, safe to run twice) flips the status and releases the nights in one transaction.
CANCELLED
The guest cancels: release the nights, then refund according to the policy. Releasing and refunding are each idempotent.
The double click. The booking page generates an idempotency key when it loads. The UNIQUE constraint on reservations.idempotency_key makes the second INSERT fail; the service catches that and returns the first reservation instead of creating a second hold.
The late payment. The provider confirms a payment 30 seconds after the hold expired and the room was released. Two compensations, decided by policy: if the nights are still available, re-reserve them and confirm; if not, refund automatically and tell the guest. A webhook from the provider plus a periodic reconciliation against its records makes sure no payment is left unmatched.
Why one database? Keeping reservations and inventory in the same PostgreSQL database lets the hold be one local transaction. Splitting them into two services with two databases would turn every booking into a distributed transaction for no benefit at 30 writes a second. If the chain grows, shard by hotel_id: a reservation only ever touches one hotel.
Phase 6: Trade-offs and failure modes (5 minutes)#
Search shows a room that's gone
The snapshot is up to a minute old. The booking transaction is the real check; the guest sees "just sold out" and alternatives. Shorten the TTL for nearly full hotels.
Payment provider is slow or down
Holds absorb short outages (10 minutes). Beyond that, holds expire and are released; the provider's later webhooks are handled by the late-payment path.
The sweeper stops running
Expired holds keep inventory blocked: rooms look sold out when they aren't. Alert on the oldest expired-but-pending hold, and make the sweeper's run idempotent so restarts are safe.
Database primary fails
Fail over to a synchronous standby so no committed booking is lost. Bookings pause for the failover; search continues from the cache.
A popular event weekend
Contention rises on a few rows. Conditional updates still serialize correctly; if it becomes a flash sale, add a queue or admission gate in front.
| Decision | We chose | We gave up |
|---|---|---|
| Inventory model | Count per room type per night | Picking a specific room number at booking |
| Concurrency | Conditional update + CHECK constraint | Simplicity of read-then-write code |
| Money + rooms | Hold → pay → confirm saga with expiry | One atomic transaction (impossible across a PSP) |
| Storage | One PostgreSQL, shard by hotel later | Independent databases per microservice |
Go deeper in the course#
- Week 5, Day 1: Consistency models: isolation levels, and why read committed allows the last-room race
- Week 2, Day 2: Idempotency in practice: the double-click problem and idempotency keys
- Week 5, Day 2: The saga pattern: hold, pay, confirm, with compensations
- Week 5, Day 3: Saga orchestration: persisting saga state and handling timeouts
- Week 2 capstone: flash sale: inventory under heavy contention
- Week 2, Day 5: Distributed cron: the hold-expiry sweeper
- Week 4, Day 2: Invalidation strategies: keeping the availability snapshot fresh enough
- Week 2, Day 4: Webhook delivery: receiving the payment provider's callbacks reliably
- Side quest: IRCTC Tatkal booking: the same problem when millions want 500 seats in the same second
Check yourself#
Hotel reservation
Q1/4 Two guests read "1 room left" and both run UPDATE … SET total_reserved = total_reserved + 1. What prevents an oversell?
Answer & explanation
Answer: C
The conditional update re-checks the row as it locks it, so the second update matches zero rows. A CHECK constraint backs it up.
Q2/4 A three-night booking succeeds for two nights but the third is sold out. What should happen?
Answer & explanation
Answer: B
All nights succeed together or none do: require rows updated = number of nights, otherwise ROLLBACK.
Q3/4 A guest double-clicks "Book". What stops two reservations being created?
Answer & explanation
Answer: A
Client-side tricks fail on retries and flaky networks. The UNIQUE constraint makes the second insert fail, and the service returns the first reservation.
Q4/4 Payment succeeds 30 seconds after the hold expired and the room was given to someone else. What's the right outcome?
Answer & explanation
Answer: D
Money and rooms must agree. The saga's compensation is a refund when the room can't be re-reserved.
New lessons by email
New lessons, side quests and case files as they ship, at most once a week. No spam, unsubscribe anytime.
Discussion · …
Public: everyone reading this lesson sees comments. Be kind and keep it on the material.