Hotel Reservation System Design Interview

room_night_inventory(hotel_id, room_type_id, stay_date, sellable, held, confirmed) with primary key on the first three columns and CHECK (held + confirmed <= sellable). Beside it: GET /search (returns availability_as_of), POST /quotes, POST /holds, POST /bookings with an Idempotency-Key, and the errors 409 INVENTORY_UNAVAILABLE and 422 RESTRICTION_VIOLATED.

"The room-night is the unit of scarcity: available is sellable minus held minus confirmed. A 2-night stay touches two rows. Prices live in a separate DailyRate table, so a rate edit never touches a hot inventory row."

Per-night rows versus a precomputed availability calendar: rows keep every write a simple conditional update; a calendar speeds range reads but must be kept in sync, which the search projection does instead.

"Why not one row per stay?" — Nights are independently scarce; two stays that overlap on one night compete for that night only.

Traveler → one App Service → one database. Search is SQL over the inventory; booking reads each night, checks it in code, then writes.

"This is right for a handful of hotels. It breaks in three places: search load competes with bookings on one primary, check-then-act loses updates, and nights are written independently, so a stay can be half-booked."

Simplicity against the three limits — and a promise to remove them one at a time.

"Where does it break first?" — Name all three limits and the order you will fix them in.

API Gateway; a Search Service reading an Availability Read Model (index + cache, ~1.2 GB); an Outbox Relay and projector fed by outbox rows written in the same transaction as each inventory change. The booking path still writes the primary and never reads the projection.

"Search reads a projection that may lag about 60 seconds. A stale positive costs one failed hold, a 409; a stale negative costs a missed sale until the projection catches up. Neither is a correctness problem, because every hold re-checks the authoritative rows."

Read replicas (same slow query shape, unbounded lag) versus a TTL cache (low hit rate on date × occupancy combinations) versus a periodic full rebuild (60 M rows for a tiny change set) versus an incremental, outbox-fed projection — chosen because it updates only what changed and its lag is a measurable SLO.

"Search showed a room that is gone — is that a bug?" — No: the response states its freshness, and the hold returns 409 naming the night.

A two-column interleaving: Request A and Request B both read available = 1 on 11-15, both write confirmed = 5, both insert a reservation.

"Two reservations, one room, and the counter says 5 of 5 — a lost update the ledger cannot even see. Writing confirmed + 1 fixes the arithmetic, not the decision. The root cause is deciding in application memory between two statements."

Candidate fixes: SELECT … FOR UPDATE then check (correct, more round trips under lock), SERIALIZABLE (correct, aborts and retries under contention, engine-specific), an optimistic version column (retry storms on a hot row), a Redis lock (not the system of record), or a conditional UPDATE (one statement, the database decides under the row lock).

"Do idempotency keys fix this?" — No. They stop the same request applying twice; A and B are different travelers with different keys.

An Inventory Service as the only writer of counts; one transaction with one conditional UPDATE per night in ascending stay_date order; summed rows_affected must equal the number of nights.

"Each UPDATE takes the row lock and evaluates the condition on the latest committed row, so B waits for A and then matches 0 rows on 11-15. Any 0 rolls back every night. Ascending order means two overlapping stays can never deadlock. The CHECK constraint makes an oversell uncommittable even if a code path forgets the condition."

Conditional update versus lock-then-check: same correctness, fewer round trips under lock. A Redis lock is at best an optimization in front of this transaction: a lease can expire during a GC pause while its holder keeps writing.

"Why not one UPDATE … WHERE stay_date IN (…)?" — The database promises no lock order for it, so it can deadlock, and you cannot tell which night failed.

A Pricing/Quote Service (quote expires in 10 minutes), an inventory_hold row (ACTIVE, TTL 600 s) created by the same ordered transaction with held + 1, and an Expiry Worker.

"Booking at click time leaves unpaid reservations; charging first can take money for a room that sells out meanwhile. A hold reserves the nights for one traveler for ten minutes. The Expiry Worker flips ACTIVE to EXPIRED with a compare-and-set on the hold's state and only then releases the nights, so it can never release a hold that was just confirmed."

TTL length: long enough for card entry and 3-D Secure, short enough that abandoned holds do not hide scarce inventory. Holds cost availability: one abandoned hold hides the last room for up to ten minutes.

"Bots hoard holds — now what?" — One active hold per traveler or session, rate limits, explicit release when checkout is abandoned, shorter TTLs on very scarce nights.

QUOTED → HELD → PENDING_PAYMENT → CONFIRMED, with EXPIRED, PAYMENT_FAILED, CANCELLED, and the crash branch PAYMENT_CAPTURED_BOOKING_FAILED → RECONCILING → CONFIRMED | REFUNDED.

"A booking is a lifecycle, not a boolean. Every transition is UPDATE … WHERE state = :from; zero rows means someone else moved first, and the caller stops. A modification is a transition that keeps the reservation CONFIRMED with version + 1, so cancelling a modified booking just works."

An is_confirmed flag versus explicit states: the flag cannot represent "paid but not confirmed", which is exactly what a crash produces.

"Where do these states live?" — Spread over existing rows: the hold's state, the booking attempt's state, and the reservation's state and version.

Booking Orchestrator, Payment Service, the external Payment Provider, a booking_attempt table, the outbox → Notifications. Number the order: hold → attempt → authorize → capture → confirm.

"Three short transactions — T1 hold, T2 attempt, T3 confirmation — and the provider calls happen between them, so no lock is ever held across the network. The attempt is unique on principal, endpoint and Idempotency-Key, carries a fingerprint of hold, amount and payment token, and a partial unique index allows one pending attempt per hold. The Expiry Worker defers holds whose attempt is younger than the 60-second auth_timeout. T3 fences the hold ACTIVE to CONSUMED, moves each night from held to confirmed, writes the reservation and an outbox event in one commit."

Capture-then-confirm (default for prepaid: a confirmed booking always has money behind it, at the cost of a capture-then-crash window) versus authorize → confirm → capture (smaller crash window; the natural fit for pay-at-hotel).

"Two retries land on two servers in the same millisecond?" — Insert first and let the unique constraint pick the winner; the loser loads the existing attempt and returns its result, or 202 while it is pending.

A Reconciliation Worker that sweeps attempts stuck in PENDING_PAYMENT and queries the provider by attempt reference.

"Payment success is not booking success. If the provider captured and we crashed before T3, the worker marks the attempt PAYMENT_CAPTURED_BOOKING_FAILED, then RECONCILING, and runs the same T3. If the hold is still ACTIVE, the booking is confirmed; if it expired, we refund. We never oversell to honour a payment."

Sweep cadence (minutes) against support load; whether policy tries a fresh allocation before refunding.

"Why not one transaction around the payment call?" — A database transaction cannot span an external call, and holding row locks across it would serialize every traveler behind one slow card.

No new boxes: a guarded cancel with per-night releases and a refund.requested outbox event; a modification saga beside it.

"Cancellation releases the nights and starts a refund as two independent operations; a failing refund never reopens the cancellation. A modification acquires the new night first, settles the price difference, then swaps nights in one transaction — the reservation stays CONFIRMED with version + 1."

Saga versus cancel-and-rebook: the shortcut releases the scarce night before re-claiming it, so another request can take it in between.

"The refund fails — what then?" — It is its own record, retried with the same provider reference and escalated after a bound.

Hotel Extranet and a PMS Connector writing through the Inventory Service; a per-hotel sync cursor.

"Stop-sell, minimum stay and closed-to-arrival are Restriction rows checked in the hold transaction — a 422, never sellable = 0. Capacity cuts below held + confirmed are rejected. PMS batches apply only if their cursor is newer, and the cursor advances in the same transaction. For bookings made here, the platform ledger wins; disagreements go to reconciliation."

Platform-authoritative versus PMS-authoritative inventory; the cross-channel oversell window is bounded by sync latency, not eliminated.

"The PMS reports fewer rooms than you have sold?" — Do not apply it; record a conflict for the hotel to resolve; never cancel paid platform bookings because of a PMS report.

A shard router by hotel_id; each Booking DB shard with a synchronous standby and read replicas; another region replicated asynchronously.

"About 135 write transactions a second fit one primary; we shard for blast radius, so one hot hotel cannot exhaust everyone's connections. A hotel's inventory, holds, attempts, reservations and outbox share a shard, so confirmation stays a local transaction. A hot row still serializes: keep transactions to milliseconds, add admission control, fail fast with 409. In-region failover keeps RPO near zero; cross-region failover can lose seconds of commits and needs reconciliation with payment and PMS records."

Shared versus sharded infrastructure; synchronous in-region standby versus asynchronous cross-region replication.

"Can you still say 'never oversold' after a regional failover?" — Not absolutely: the guarantee holds for the ledger that survives; lost commits are repaired from provider and PMS records.

It looks fine in a single-request demo. Probe: "Request A and Request B both see available = 1 on 11-15 and click within the same second." Two independent reads both proceed; nothing stops both writes. The cached number was never the thing enforcing scarcity — conflating the displayed count with the authoritative row is the most common wrong turn.

UPDATE … SET held = held + 1 WHERE … AND sellable - held - confirmed >= 1, checking one row affected. This fixes the single-night race. Probe: "Both requests want 11-14 and 11-15. What stops a booking succeeding on 11-14 and failing on 11-15?" Independent per-night statements have nothing tying the outcomes together.

All nights' updates in one transaction, sorted by stay_date, with summed rows_affected required to equal the night count. A takes 11-14 (3 → 2) and 11-15 (1 → 0) and commits. B gets 11-14 but matches 0 rows on 11-15; 1 + 0 ≠ 2, so its whole transaction rolls back — including 11-14 — and the 409 names 11-15. The ascending order also removes deadlocks between overlapping stays.

If the confirmation never committed, the reservation table has nothing to find. Look up the booking attempt by the client's Idempotency-Key (3f9c2a71-book → BKA-1): it was written before the provider was called precisely so it exists after a crash.

BKA-1 is PENDING_PAYMENT with no reservation. Query the provider by BKA-1's reference — a status check, not a new authorization. It reports CAPTURED for PAY-1: money moved, but T3 (hold → CONSUMED, held → confirmed, RES-1, EVT-1) never committed.

The attempt moves PAYMENT_CAPTURED_BOOKING_FAILED → RECONCILING, and the worker runs the normal T3. Hold still ACTIVE → CONFIRMED and the notification goes out from the outbox. Hold EXPIRED and the night resold → REFUNDED. The fenced claim decides the branch, not the payment.

T3 writes the reservation, nights and outbox event atomically, so "confirmed but never announced" cannot happen. A sweep with an oldest-stuck-attempt alert finds these cases in minutes rather than via support. Provider calls reuse the attempt reference, so recovery can never charge twice.

Probe: "Which TTL guarantees the payment always wins?" None — a provider can be arbitrarily slow. A longer TTL only makes the race rarer while hiding scarce inventory longer.

Both writers start with a compare-and-set on the hold's own state. Confirmation first (t = 598 s): ACTIVE → CONSUMED matches, and the worker's ACTIVE → EXPIRED at 601 s matches 0 rows — a no-op. Expiry first (t = 600 s): the hold expires, HLD-2 takes the unit, and the late confirmation's fence matches 0 rows, so the money is refunded through reconciliation. The worker also defers holds whose attempt is younger than the 60 s auth_timeout, which makes the second ordering rare; the fence makes it safe.