Hotel Reservation: search, booking without double-selling and cross-service consistency

The first lesson ended with a small inventory table in one relational database and a 1,000-to-1 read-to-write ratio. This lesson builds the two paths that ratio forces apart — a fast, forgiving search path and a slow, strict booking path — and then deals with the fact that payment lives in a different service.

Start with the reads. 23,000 searches per second must never touch the booking database. Here is what they touch instead.

The read path, built separately

Search asks a different question from booking. Booking asks "can I have exactly these three nights in this room type, right now, definitively?" Search asks "which of the 400 hotels near this beach have something plausible for these dates, roughly, sorted by relevance?"

The second question tolerates staleness of seconds and does not tolerate slowness. So build it as its own path:

1. A search index — a document per hotel carrying location, amenities, star rating, price band, and a compact availability summary for the next 12 months. Elasticsearch or a similar engine is the common implementation; the geospatial part is covered in Geospatial indexing options.
2. A denormalised availability cache keyed by (hotel_id, room_type_id, date) holding the available count, so a result set of 50 hotels is 50 cache lookups rather than 50 database round trips.
3. A change stream carrying every inventory update from the booking database into the index and the cache, so both converge within seconds.

Why staleness is acceptable here, precisely

A cached availability count is up to 30 seconds old. In those 30 seconds, at 2.3 bookings per second spread across 5,000 hotels, the chance that a specific hotel and room type changed is tiny — but it is not zero, and on a nearly-full popular hotel it is real.

So the contract is: search results are advisory. The count shown may be wrong; the booking transaction is authoritative. The user-visible consequence is the occasional "sorry, that just went" at the final step, which every travel site produces and every user has seen. Naming that consequence explicitly, rather than pretending the cache is correct, is the answer that scores.

The mitigation is to re-check availability at the moment the user opens the booking page, not only at submit. That is one cheap read against the primary database, at maybe 50 per second — affordable, and it moves the disappointment earlier in the funnel where it costs less.

Booking without double-selling

Now the strictly-consistent island. Two requests arrive within milliseconds for the last room. Exactly one must succeed.

Why the obvious code is wrong

available = SELECT total_inventory - total_reserved FROM inventory WHERE ...if available > 0:    UPDATE inventory SET total_reserved = total_reserved + 1 WHERE ...

Two requests read available = 1 at the same instant. Both pass the check. Both increment. Two rooms sold, one room exists. This is a read-then-write race, and it is the same bug as the distributed rate limiter (Making it distributed), in different clothing.

The gap between the read and the write is the vulnerability. Every fix closes it differently.

Option 1: pessimistic locking

SELECT ... FOR UPDATE takes a row lock before reading. The second request blocks until the first commits, then reads the updated value and correctly fails.

Correct and simple. Costs: the lock is held for the duration of the transaction, so a slow transaction — one that calls a payment provider while holding it — blocks every other booking for that room type and date. Deadlock is possible if two multi-night bookings lock rows in different orders, which is fixed by always locking dates in ascending order.

Throughput: at 23 bookings per second spread over 100,000 hotel-and-room-type combinations, contention is essentially nil except on one hotel during one sale. Perfectly adequate here.

Option 2: optimistic concurrency with a version column

Read the row and its version. Write with WHERE version = <the value you read>, incrementing it. If zero rows are affected, someone else won; retry from the read.

Correct, and holds no lock. Costs: under contention, retries multiply. If a hotel has one room left and fifty people want it, forty-nine retry, some repeatedly, and total work grows faster than the contention.

Right when conflicts are rare, which is the normal case here.

Option 3: a database constraint

Put the invariant in the schema and let the database enforce it:

ALTER TABLE room_type_inventory  ADD CONSTRAINT no_oversell  CHECK (total_reserved <= total_inventory * overbooking_factor);

Then write the update unconditionally. If it would violate the constraint, the database rejects the transaction. No read-then-write gap exists, because there is no read.

This is the recommendation, combined with Option 1 for the multi-row case. The invariant lives in one place, cannot be bypassed by a new service or a manual script, and survives a developer who forgets the locking convention. Application-level checks are defences that one careless code path removes; a constraint is not.

Idempotency: the retry problem

The transaction succeeds. The response is lost in the network. The client retries. Without a defence, that is two reservations and two charges.

The client generates an idempotency key — a unique identifier for this booking attempt, created before the first send and reused on every retry. The reservation table has a unique index on it. The second insert violates the unique constraint, and the service responds with the existing reservation rather than creating another.

This is the same mechanism as exactly-once ad click aggregation and it is the central subject of double-payment prevention. It appears in every correctness-critical system in this course.

Consistency across services

The booking transaction is correct inside one database. But reservation and payment live in different services with different databases. There is no transaction that spans them.

Why not a distributed transaction

Two-phase commit — a coordinator asking every participant to prepare, then telling them all to commit — does provide atomicity across services. It is rejected here for three reasons worth stating precisely.

It blocks. Between prepare and commit, participants hold locks. If the coordinator dies in that window, participants are stuck holding locks with no authority to release them, until a human intervenes.

It requires every participant to support it. An external payment provider will not enrol in your two-phase commit. This alone ends the discussion.

It couples availability. The transaction succeeds only if every participant is up, so overall availability is the product of the parts. Three services at 99.9% give 99.7%.

The saga pattern

Split the operation into a sequence of local transactions, each with a compensating transaction that undoes it.

1. Reserve inventory (compensation: release inventory).
2. Authorise payment (compensation: void the authorisation).
3. Confirm the reservation (compensation: cancel and refund).
4. Send confirmation (no compensation needed).

If step 2 fails, run step 1's compensation. The system reaches a consistent state, but it passes through inconsistent intermediate states — for a few seconds, inventory is reserved for a booking that will not exist. That is the trade: atomicity is exchanged for availability, and the inconsistency is bounded and self-healing rather than permanent.

Compensations are not rollbacks. Releasing inventory is a new forward action, visible in the audit trail, and it must itself be idempotent because the saga coordinator may retry it.

The transactional outbox

Step 1 must do two things: update the database and publish an event. Doing them separately is unsafe — commit then publish loses the event if the process dies between; publish then commit emits an event for a transaction that rolls back.

Write the event into an outbox table inside the same transaction as the inventory update. A separate relay process reads unpublished outbox rows and publishes them to the log, marking them sent. The database write and the event now succeed or fail together, and the relay's at-least-once publishing is handled by idempotent consumers. The payment system's consistency lesson develops this further.

Overbooking, deliberately

Historical no-show rates run at a few per cent for many hotels. Selling 110% of inventory converts most of that into revenue, and the policy is set per hotel and per season by the revenue team, not by engineering.

The engineering consequence is a walk policy: when more guests arrive than there are rooms, the system must identify who to relocate and cover the cost of an equivalent room elsewhere. That is a real workflow — detection at check-in, a rebooking service, and a compensation payment — and mentioning it shows you understand that the overbooking multiplier has an operational tail.