Two guests, one Deluxe Double
A riverside hotel has one Deluxe Double left for the nights of 14 and 15 March. The first version of its booking service reads availability, then inserts the booking. On a Sunday evening two guests press Book within the same second:
- Marta's request reads both nights: 1 left on each.
- Jonas's request reads the same rows 40 ms later: still 1 left, because nothing has been written yet.
- Marta's request inserts her booking and replies confirmed.
- Jonas's request inserts his booking and replies confirmed.
Two confirmation emails, one room. On 14 March one of them reaches the desk to find the room taken, and the hotel finds that guest a bed elsewhere, usually at its own cost. Nothing crashed and nothing was slow: both requests read a check that was true, and by the time Jonas's wrote, Marta's write had made it false.
Small numbers, one hard moment
The estimate first, the way Approaching a system design problem sets out. Say 5,000 hotels averaging 100 rooms in 10 room types, and 100,000 bookings a day: a little over 1 a second, peaking at maybe 100 a second. Guests search far more than they book; at 1,000 searches per booking that is ~1,200 availability reads a second on average and up to ~100,000 a second at that peak, a cache's job rather than the database's.
A guest books a room type, not room 412, so inventory is counted per room type per night: 5,000 × 10 × 365 ≈ 18 million rows a year, one ordinary table. Which room a guest actually sleeps in is assigned at check-in; while rooms of a type are interchangeable, modelling individual rooms in the booking system multiplies rows and hot spots for no gain. So this is not a scale problem. It is a correctness problem at the moment two people want the same last room, and a latency problem on search.
Search can be a few seconds wrong
Availability search reads a cache keyed by hotel:room_type:night holding total − booked − held, refreshed on every booking change. It may be a few seconds stale, which is acceptable because a search result reserves nothing. The inventory table is the truth: search may read it to fill the cache, but only the booking path changes it. A guest shown 1 left for a room that went a second ago learns it one step later, from the booking path, as "just sold out". That is also why the booking path never trusts the cache: checking it and then writing is the opening trace with a faster read. Filling and refreshing the cache is a Caching layers question.
The update that decides
The fix for Marta and Jonas is not a faster check. It is no separate check at all. The booking path asks the database to change a night's row only if the room is still there: UPDATE inventory SET held = held + 1 WHERE hotel_id = … AND room_type = … AND night = … AND booked + held < total. The predicate is evaluated inside the write. Marta's update takes the row lock on the 14th and adds her hold; Jonas's update on the same row waits for that lock, and once Marta's transaction commits, it finds booked + held equal to total and changes nothing. Zero rows updated means sold out. (That is PostgreSQL's default, read committed; at stricter levels Jonas's update fails with a serialization error and its retry finds the room gone, the same outcome, for reasons Transactions & isolation levels sets out.)
Marta's stay is two nights, so her hold is one transaction across two rows. If either updates zero rows, the whole transaction rolls back, and nobody ends up holding the 14th without the 15th. Update the nights in date order: two multi-night bookings that lock the same rows in opposite orders can deadlock, and the database then aborts one of them.
No extra lock is needed: the row lock the update takes is the lock, it ends with the transaction, and a second lock on top would only add cost. A Redis lock around the check would expire on a timer, possibly mid-write (see Distributed locks & leader election). At a concert's on-sale minute, thousands race for the same rows and the lock queue itself becomes the bottleneck; the Event Ticketing Challenge adds a queue in front of the hold for that.
Ten minutes to pay
Winning the update gives Marta a hold, not a booking. The same transaction inserts a reservation in state held with an expires_at ten minutes out, and commits. Only then does the service call the payment gateway, outside any transaction. Inside one, a three-second gateway call is a three-second row lock that every other guest booking that room type queues behind, and a gateway outage stops the hotel's sales.
When the charge succeeds, a second transaction confirms, and it is conditional too: UPDATE reservations SET state = 'confirmed' WHERE id = … AND state = 'held' AND expires_at > now(), and only if that changed a row, each night moves one from held to booked. A hold nobody pays for needs an exit, or every abandoned checkout takes a room off sale for good, so an expiry sweeper runs every minute and, in one transaction per reservation, marks a held reservation past expires_at as expired and gives its nights back. The sweeper and a late confirm can reach the same reservation at the same moment; both transitions require state = 'held', so only one of them changes it. In PostgreSQL now() is the time the confirm's transaction began, so a confirm that waited for a lock can still pass a moment after expires_at; that is harmless, because the room was still held for it and the sweeper leaves a confirmed reservation alone.
Every booking request also carries an idempotency key, created by Marta's browser once per checkout and stored in a unique column on reservations. If her connection drops after she presses Book and the page resubmits, the second request hits the unique constraint and gets back the existing reservation instead of holding a second room. The key has to come from the client: one the server made per request would be new on every retry, and the retry would hold and charge a second room. Where the gateway accepts its own idempotency key, sending the reservation id keeps a retried charge from becoming a second charge. The key in general belongs to Idempotency & exactly-once illusions, the gateway's side to Payments & billing integration.
When the money and the hold disagree
- The charge succeeds and the confirm write fails. Inside the hold's TTL, a retry with the same idempotency key finds the reservation still
heldand completes the transition. Past it, the conditional confirm updates zero rows: the service re-holds if a room is still free, and otherwise refunds automatically. The re-hold is one transaction on the same reservation: it moves the reservation fromheldorexpiredstraight toconfirmed, conditionally, so a second retry finds it done and stops; if it was stillheld, it returns its nights, as the sweeper would have; then it takes every night again with the hold's update, this time adding tobooked. If any night updates zero rows, it all rolls back, and the service refunds only after a conditional update moves the same reservation fromheldorexpiredtorefunding, returning its nights in that same transaction if it was stillheld, so a retry that confirmed it first is never refunded. So set the TTL longer than the payment call's own timeout, and start no charge on a hold with less than that timeout left. That makes a charge that outlives its hold rare, not impossible: a call that times out leaves its result unknown, and the gateway may still complete it later, which is why a reconciliation job compares the gateway's settlements with reservations for anything left over: a charge with no confirmed stay, or a confirmed stay with no charge. - The guest abandons at payment. The hold expires and the sweeper returns the room. Every hold has an exit, so no room can be leaked into a permanent limbo.
Selling 105 rooms out of 100
A ten-minute hold takes a room off sale while a guest types a card number, and some confirmed guests never arrive. Many hotels answer both with deliberate overbooking: up to, say, 105 bookings for 100 rooms, knowing that some nights a guest will be moved to another hotel. In this design that is one number, the limit the hold's predicate compares against (a whole-number sell_limit per room type and night, such as 105 where total is 100, instead of total). Whether to overbook, and by how much, is a product decision; the conditional update enforces whichever limit the hotel picks.
Drawing the booking as states
Open the Hotel Booking System Challenge and spend it on one thing: the booking path as a state machine. Draw the hold with a TTL, the payment outside the transaction, the conditional confirm, and the sweeper as the other way out of held, and write the predicate on the hold edge. Then put Marta and Jonas through your drawing: if a second request can reach a confirmed booking without crossing the edge that carries that predicate, the design still sells the room twice.