Worked design: hotel booking

Requirements & estimate

Search rooms by city and dates, see prices, book — and never sell the last room twice. Say 5,000 hotels averaging 100 rooms in 10 room types, 100,000 bookings a day (a little over 1/second, peaking at maybe 100/second) and a 1,000:1 search:book ratio, so ~1,200 availability reads/second on average and up to ~100,000/second at that peak — a cache's job, not the database's. Inventory is not per room but per room-type per night: 5,000 × 10 × 365 ≈ 18 million rows a year, which is nothing. The estimate says this is not a scale problem at all — it is a correctness problem at the moment two people want the same last room, and a latency problem on search.

The read path: search from a cache, book from the truth

Availability search reads a cache keyed by hotel:room_type:night holding total − booked − held, refreshed on every booking change and safe to be a few seconds stale — a search result is a quote, not a promise. The inventory table is the truth and only the booking path touches it. Which room a guest actually gets is assigned at check-in; modelling individual rooms in the booking system is a classic over-modelling that multiplies rows and hot spots for no gain.

Diagram components: Web App, Application Service, Redis, PostgreSQL.

Interactive diagram — open this article in RodGrid to explore it.

The write path: hold, pay, confirm

Booking a room type for three nights is one transaction across three inventory rows, each updated conditionally: UPDATE inventory SET held = held + 1 WHERE … AND booked + held < total. If any row updates zero, roll back — the race is decided by the database, never by an availability check made a moment earlier. That transaction creates a reservation in state held with an expires_at ten minutes out and returns; the guest then pays. Payment happens outside the transaction (an external call inside a lock is how you turn a slow gateway into a frozen hotel); on success a second conditional transition held → confirmed moves the count from held to booked. Every booking request carries an idempotency key, so a retried submit finds the existing reservation instead of holding a second room. A worker sweeps expired holds back into availability.

Diagram components: Web App, Application Service, PostgreSQL, Payment Gateway, Scheduler.

Interactive diagram — open this article in RodGrid to explore it.

Failures & trade-offs

• Two guests race for the last room → one conditional update succeeds, the other's transaction rolls back and the guest sees "just sold out". Correct, and cheaper than any lock you would write yourself.
• Payment succeeds but the confirm write fails → the hold still exists; a retry with the same idempotency key completes the transition, and a reconciliation job compares gateway settlements to reservations for anything left over.
• The guest abandons at payment → the hold expires and the sweeper returns the room. Every hold has an exit; no room can be leaked into a permanent limbo.
• Stated trade-off: a ten-minute hold blocks inventory that other guests would buy right now — hotels routinely answer that with deliberate overbooking (booked ≤ total × 1.05), a product decision the conditional update expresses in one number.

Common mistakes

• Read-then-write availability. "SELECT count WHERE available, then INSERT booking" double-books under concurrency; only a conditional update whose predicate is evaluated inside the write decides the race.
• Calling the payment gateway inside the database transaction. A three-second gateway means a three-second row lock on the room type, and a gateway outage locks the hotel.
• Holds without an expiry. Every abandoned checkout takes a room off sale for ever; expires_at plus a sweeper is the whole fix.
• No idempotency key on the booking request. A network retry after a timeout holds and charges a second room for the same guest — the reservation table needs a unique key per client attempt.

Try it on a Grid

Open the Hotel Booking System challenge and focus on one thing: the booking path as a state machine — hold with a TTL, payment outside the transaction, then a conditional confirm — with the sweeper drawn as the exit. A good run labels the hold edge with its predicate. Event Ticketing is the same race at ten thousand times the concurrency, where the on-sale spike also forces a queue in front of the hold.