How to design a hotel-reservation system

In this lesson, we’ll design the booking system for a hotel chain: a guest picks a hotel, a room type such as “king”, a check-in date and a check-out date, hits reserve, and the system decides in real time whether that exact room on those exact nights is still free. By the end you’ll be able to size the write path, name the exact database sequence that sells one room twice, choose among the three standard fixes for it, keep a payment retry from charging a card twice, and defend deliberate overbooking as a number in a column.

A booking system has one central rule: a given room on a given night is sold to exactly one guest. The engineering is making that rule hold under simultaneous users, a payment provider that times out, a cache that lags the database, and several services that share no database. The scale is small; correctness is the whole problem.

We’ll cover, in order, the traffic sizing that rules out sharding, the exact database sequence that sells one room twice, the three standard fixes for that race, a retry-safe payment path that cannot charge a card twice, and why deliberate overbooking is a number in a column, not a bug.

What the system must guarantee

A reservation system exists to enforce one sentence: a room-night can be sold once.

A room-night is the unit being sold: one room, of one type, at one hotel, on one calendar night. It is the unit this lesson counts and locks throughout. Everything else (search, ranking, pricing, loyalty points, cancellation policy) is commerce built on top of that single invariant (a statement that must be true of the data before and after every operation).

The system is asked to be good at four things, and each one costs something paid by a specific mechanism.

The terms in that table, once:

• To walk a guest is hotel jargon for turning up a guest whose room does not exist and paying to put them elsewhere.
• Atomic means all-or-nothing: the step happens completely or leaves no trace, with no in-between state another request can observe.
• To serialize work is to force it into single file. That is what makes atomicity possible, and it is what caps throughput.
• An idempotency key is a string the caller attaches to a request so the server recognises a retry of the same intent and returns the first answer instead of doing the work again.
• Inventory here is the count of room-nights available to sell, never a warehouse.
• A saga is a sequence of ordinary local steps, each with a compensation: a second operation that undoes the first after the fact (a refund undoing a charge) instead of rolling it back inside a transaction. It is the practical replacement for a distributed transaction (one all-or-nothing operation spanning several independent databases).

The write rate here is single digits per second, so this is a data-integrity problem, not a throughput problem, and the work is in the race condition. A race condition is a bug that appears only when two operations overlap in time and interleave in an unlucky order.

Those four properties are what force everything downstream, so the requirements section turns each one into a target.

What actually breaks in production

• A flash sale. A promotion that compresses a month of demand for one date into one hour turns a quiet row into a contended one.
• A payment timeout. The provider times out, the client retries, and the same customer intent is now in flight twice.
• A stale cache. It serves “3 rooms left” for a date that sold out ninety seconds ago, so users are failed at the payment page instead of the search page.
• A silently failed compensation. Room-nights stay held by a reservation nobody will ever pay for.

Requirements

Functional

• Search hotels by location and date range; show remaining availability per room type.
• Reserve a room type for a date range, atomically across every night of the stay.
• Pay, with a client-driven retry that cannot double-charge.
• Cancel, modify dates, and return the inventory.
• Admin: set inventory, rates, and a deliberate overbooking allowance per date.

Overbooking means accepting more reservations than there are physical rooms. Done by accident it is the central bug of this lesson; done on purpose it is standard practice, because a predictable share of guests never arrive (the allowance is derived at the end).

Out of scope, and saying so is part of the answer: pricing and yield optimisation (a forecasting model, not an architecture); room assignment (hotels pick a physical room number at check-in, not at booking); and synchronisation with outside travel agencies through a channel manager (an integration product of its own).

Non-functional

p99 is the ninety-ninth percentile: the number 99 of 100 requests come in under, describing the slow tail users actually complain about. A payment gateway is the outside company that talks to the card networks on your behalf; it is slow by your standards and you do not control it. Durability means that once the system has said yes, the data survives a crash.

The asymmetry that shapes everything: reads outnumber writes 400:1, the reads are allowed to be stale, and the writes are not allowed to be wrong. That split gives the system a cached read path and a small, strict write path, and the write path is where the design lives.

Back of the envelope

Let’s do three quick estimates, because each one settles a design question we would otherwise argue about: how many writes per second, how big the inventory is, and how a date range maps to nights.

Demand. 5,000 hotels x 200 rooms = 1,000,000 rooms. At 70% occupancy and a 3-night average stay, that is about 233,333 bookings/day, or 2.7 bookings/s average and ~8/s at a 3x peak. With ~100 detail-page views and ~10 searches (scoring ~30 hotels each) per booking, reads come to ~3,241/s at peak: a 400:1 read:write ratio.

Eight writes per second is the number the whole design hangs on, so sit with it. A single unremarkable Postgres primary (the one server that accepts writes, as opposed to the read-only replicas that follow it) handles thousands of small write transactions per second, three orders of magnitude above this. Because we are 1,000x under the limit of one box, there is no sharding argument, no queue argument, and no NoSQL argument on throughput grounds. The reads are a cache and two replicas. At eight writes a second you can afford the most expensive correctness mechanism available; the only question is which, and what it costs when demand is not uniform.

Inventory size. At a (hotel, room type, date) grain (5 room types, a 500-day booking horizon) the availability table is ~12.5 M rows at ~72 bytes each, about 900 MB. The entire forward inventory of a 5,000-hotel chain fits in RAM, index and all, which rules out every design that treats availability as a big-data problem. The reservation table grows with time instead, reaching ~128 GB over 5 years (partition by month, archive old stays).

A per-physical-room grain would be ~40x bigger (36 GB) and, more importantly, wrong. Hotels do not sell room 412; they sell a king room, and the front desk assigns a physical room at check-in so it can group families, honour upgrades, and route housekeeping. Per-room modelling forces an assignment decision months early, turns every date change into a re-assignment, and makes a one-row update into a search for a free room. The unit of inventory is (hotel, room type, date). A 3-night stay touches exactly 3 rows and must take all 3 or none.

The interval convention

This is the smallest decision in the lesson, and one of the two ways to sell a room twice.

Every date range is the half-open interval [check_in, check_out): the check-in night is sold, the checkout night is not. So 14th to 17th means the nights of the 14th, 15th and 16th, three nights. Guest A checking out on the 17th and guest B checking in on the 17th share no night.

The consequence is mechanical. The condition is always date >= check_in AND date < check_out, never date BETWEEN check_in AND check_out. SQL’s BETWEEN includes both ends, so it matches nights + 1 rows and marks the checkout night as sold, and that is the night the next guest is checking in on. That is the same double-sold room as the concurrency race later, arriving through arithmetic; because no two transactions overlap, no database setting prevents it.

Turn a date pair into the nights it occupies in one place, with the guard that a zero-night or inverted range is rejected:

from datetime import date, timedelta

def nights(check_in, check_out) -> list:
    """The nights a stay occupies, on [check_in, check_out)."""
    ci, co = date.fromisoformat(check_in), date.fromisoformat(check_out)
    if co <= ci:                        # reject zero-night and inverted ranges
        raise ValueError("end_date must be strictly after start_date")
    out, d = [], ci
    while d < co:                       # `<`, not `<=`: the half-open guard
        out.append(d)
        d += timedelta(days=1)
    return out

assert len(nights("2026-11-14", "2026-11-17")) == 3

The range guard is not decoration. Without it, nights(d, d) returns an empty list, so the “wanted” count is 0, and the booking write’s whole defence (if rows != wanted) is 0 != 0, which is False: a stay of no nights passes every check, writes a reservation, and charges the card while taking no inventory. One missing comparison defeats the rowcount mechanism the rest of the design is built on, which is why we check the range in the only function that turns a date pair into nights.

That is the first of the two ways to double-sell a room, and it needs no concurrency at all. The API is where we make the half-open interval and the retry key part of the contract, so a caller cannot reintroduce the bug.

API sketch

One write endpoint with its four possible responses, then the supporting endpoints. The three-digit numbers are HTTP status codes: 201 created, 202 accepted-but-not-finished, 409 conflict, 422 well-formed but unprocessable.

POST /v1/reservations
  Idempotency-Key: 0f3c9a...                 -- client generated, one per user intent
  {"hotel_id": 812, "room_type_id": 3,
   "start_date": "2026-11-14", "end_date": "2026-11-17",
   "guest_id": 99120, "rate_quote": "rq_7f2a", "amount_cents": 84000}
  201 {"reservation_id": "res_...", "status": "confirmed"}
  202 {"reservation_id": "res_...", "status": "pending"}   -- 3-D Secure in flight
  409 {"error": "sold_out", "night": "2026-11-15"}
  422 {"error": "idempotency_key_reused_with_different_body"}

GET  /v1/hotels/{id}/availability?start=&end=&guests=
     -> {"king": {"remaining": 3, "as_of": "2026-11-01T10:22:07Z"}, ...}
POST /v1/reservations/{id}:cancel   Idempotency-Key: ...
GET  /v1/reservations/{id}

3-D Secure, on the 202 line, is the card networks’ extra authentication step: the bank interrupts the payment to make the cardholder confirm, usually with a texted code. It takes minutes, not milliseconds, which is why a booking must be allowed to sit in pending instead of resolving in one round trip.

Five choices in that contract carry weight:

• start_date and end_date are the half-open interval. The night of the 17th belongs to whoever checks in that day.
• Idempotency-Key is required on every call that changes data, not just create. A retried cancellation that issues two refunds is the same double-charge bug in a different place.
• The 409 names the night that failed, so the client can act on it: offer two nights instead of three, or a different room type.
• as_of in the availability response carries the age of a cached hint, so a caller can decide whether to re-check the database before showing a “1 left!” badge.
• rate_quote is a token the server issued earlier, so the price is never chosen by the client; the amount in the body is checked against that quote.

There is deliberately no PUT /availability/{date}/decrement endpoint. Publishing a “reduce the count by one” call would make all-or-nothing behaviour the client’s problem: a client that decremented two nights and then crashed would leave the third unbooked and the first two unsellable. The only public write is “reserve this stay”, and the all-or-nothing step happens on the server where it can be enforced.

Data model

Seven tables. room_inventory is the one to read closely: it is the contended table, it carries two counters, and its CHECK lines are the invariant written where the database can enforce it. PK marks the primary key; UUID is a 128-bit identifier any machine can generate on its own with no coordination.

hotels          hotel_id PK, name, geo_cell, timezone
room_types      room_type_id PK, hotel_id, name, occupancy, base_rate_cents

room_inventory                                  -- the contended table
  hotel_id BIGINT, room_type_id BIGINT, date DATE,
  total_inventory SMALLINT NOT NULL,            -- includes any deliberate overbook
  total_reserved  SMALLINT NOT NULL DEFAULT 0,
  version         BIGINT   NOT NULL DEFAULT 0,
  PRIMARY KEY (hotel_id, room_type_id, date),
  CHECK (total_reserved >= 0),
  CHECK (total_reserved <= total_inventory)     -- the invariant, in the schema

reservations
  reservation_id UUID PK, hotel_id, room_type_id, guest_id,
  start_date, end_date,                         -- HALF-OPEN [start, end)
  status,                                       -- pending | confirmed | cancelled
  amount_cents, idempotency_key, created_at,
  UNIQUE (idempotency_key)

idempotency     key PK, request_hash, state, response_status, response_body, created_at
saga_log        saga_id PK, reservation_id, step, state, attempt, next_attempt_at
rooms           room_id PK, hotel_id, room_type_id, room_number   -- assigned at check-in

Three decisions worth defending:

total_inventory and total_reserved, not a single remaining. One combined counter cannot tell “sold out” from “an administrator set the room count to zero”, cannot express a deliberate overbooking allowance without lying about how many rooms exist, and turns taking a room out of service into a read-then-write against the very value bookings are already fighting over. With two columns, deliberate overbooking is a change to total_inventory alone.

The CHECK lives in the table, not the application. A CHECK constraint is a rule the database refuses to violate on every write from every caller. It costs nothing at write time, and it is the only guard that survives a new caller written by someone who never read this page.

reservations.idempotency_key is UNIQUE. The idempotency table is the fast path that answers a retry quickly; this constraint is the truth. Even a bug in the idempotency layer cannot produce two reservation rows for one intent, because the database rejects the second insert.

If you ever shard, shard on hotel_id. Every booking transaction touches rows for exactly one hotel, so partitioning on hotel_id keeps every write on a single machine and the correctness story never becomes a transaction spanning two databases. But at 900 MB and 8 writes/s you would shard only to limit blast radius or isolate a large tenant, never for throughput.

High-level architecture

The diagram splits at the gateway: a read path (search, cache, replicas) on one side and a write path (reservation service, idempotency store, primary, then the saga) on the other. Two things to notice: the primary is the only store the reservation service writes to inside a transaction, and the payment service hangs off the saga orchestrator, never off the primary.

flowchart TD
    U(["client"]) --> GW["API gateway<br/>auth, per-account rate limit"]
    GW --> SRCH["Search service<br/>geo + date filter"]
    GW --> RES["Reservation service<br/>only writer of room counts"]
    SRCH --> CACHE[("Availability cache<br/>invalidated on write, not TTL-expired")]
    CACHE -.->|"miss"| RO[("Read replicas<br/>availability only")]
    RES --> IDEM[("Idempotency store<br/>24 h TTL")]
    RES --> DB[("Primary<br/>room_inventory + reservations<br/>ONE transaction, authoritative")]
    DB --> RO
    DB -->|"write-through invalidate"| CACHE
    RES --> SAGA["Saga orchestrator<br/>durable step log"]
    SAGA --> PAY["Payment service<br/>authorize, then capture"]
    SAGA --> LOY["Loyalty service"]
    SAGA --> MSG["Confirmation email<br/>irreversible, therefore last"]
    SAGA --> Q[["Retry queue<br/>durable delivery"]]
    Q --> SAGA
    SWEEP["Expiry sweeper<br/>releases pending holds"] --> DB

The four claims this picture makes, each defended by a section below:

1. The inventory decrement and the reservation insert are in one transaction on one primary.
2. The payment call is outside it: the arrow that must not exist is one from the reservation transaction to the payment gateway.
3. The cache is refreshed by the write itself instead of being left to expire on a timer (the write-through invalidate arrow).
4. The confirmation email is last, because it is the only step with no way to undo it.

The saga orchestrator keeps a durable step log so a crash resumes instead of restarting. The expiry sweeper is a background job that releases holds nobody completed; it is a correctness component, not a cleanup job. The retry queue’s mechanics come from the distributed message queue chapter.

The double-booking race

Two well-written booking requests can sell the same room, and it helps to watch it happen once. Here is the setup: one room-night is left (total_inventory = 11, total_reserved = 10, so remaining = 1), two requests arrive 4 ms apart on two application servers, against one database at its default isolation level, READ COMMITTED.

An isolation level decides how much two overlapping transactions see of each other. READ COMMITTED (the default in Postgres, MySQL and SQL Server) promises only that you never read a change another transaction has not committed yet. That is not enough.

 T1  (READ COMMITTED)                    T2  (READ COMMITTED)
 BEGIN                                   BEGIN
 SELECT total_inventory - total_reserved
   ... date='2026-11-14';       -> 1
                                         SELECT total_inventory - total_reserved
                                           ... date='2026-11-14';      -> 1
 -- remaining is 1, allow it
                                         -- remaining is 1, allow it
 UPDATE room_inventory
    SET total_reserved = 11              -- value computed IN THE APP
    ...;
 INSERT INTO reservations ...;
 COMMIT
                                         UPDATE room_inventory
                                            SET total_reserved = 11
                                            ...;
                                         INSERT INTO reservations ...;
                                         COMMIT

 -- total_inventory 11, total_reserved 11, and 12 reservation rows exist.
 -- The counter is wrong AND the room-night is sold twice.

Both transactions read remaining = 1 and both wrote 11. Nothing in READ COMMITTED connects the SELECT to the UPDATE that follows it: each statement gets a fresh snapshot (a consistent view as of the moment that statement began), so T2’s SELECT legitimately saw the world before T1 committed and its UPDATE overwrote the world after. This is the textbook lost update, and it is permitted at READ COMMITTED: not a database bug, but the published definition of the level.

Now write the UPDATE the other way: SET total_reserved = total_reserved + 1, letting the database read the current value instead of the application. The lost update disappears, because that statement re-reads the latest committed row while holding a row lock (an exclusive claim on that single row that blocks other writers until the transaction ends). But total_reserved ends at 12 against a total_inventory of 11, and you are still oversold: the difference is that this version leaves evidence a CHECK constraint would have refused outright.

So the defect is not “we did not lock”. The defect is that the decision and the write were not the same step. Anything that re-unites them fixes it.

What each isolation level does to this trace

“REPEATABLE READ fixes it” is only true on one of the two most popular engines. InnoDB serves a transaction’s snapshot to a plain SELECT but the latest committed row to any statement that locks or writes, so read and write see different worlds inside one transaction. The database internals chapter derives this.

SERIALIZABLE (which guarantees the result is as if transactions ran one after another) is correct and still the wrong default here: it turns contention into aborted transactions, so every transaction must be safe to re-run from the start (which a booking with a payment capture inside it is not, because the money has already moved) and its abort rate grows faster than linearly as contention rises, so it degrades worst during exactly the flash sale you bought it for.

Three fixes, priced

The race has three standard cures: hold a lock, detect a conflict and retry, or fold the check into the write. To choose between them we price everything in one currency: T, the time one booking transaction spends inside the database. While a transaction holds a row, no other can change it, so one row can be updated at most once per T: a ceiling of 1/T updates per second.

A booking transaction does a conditional UPDATE (~1 ms), an INSERT (~1 ms), a commit fsync (~1 ms, the OS call that forces data onto durable disk), and two app round trips (~2 ms), so T ≈ 5 ms and the ceiling is 200 bookings/s on one row. The hottest row in the system is one date at one 200-room property; even if all 200 room-nights sell inside a single flash-sale hour, that is 0.056/s, about 3,600x below the ceiling, roughly 0.03% utilisation. Every option below is affordable, so the choice is made on failure behaviour, not throughput.

Fix 1 — pessimistic: SELECT ... FOR UPDATE

Pessimistic control assumes a conflict and prevents it up front by taking a lock. SELECT ... FOR UPDATE reads rows and locks them against other writers until the transaction ends.

BEGIN;
SELECT total_inventory, total_reserved
  FROM room_inventory
 WHERE hotel_id = $1 AND room_type_id = $2
   AND date >= $3 AND date < $4                 -- half-open interval
 ORDER BY date                                  -- deterministic lock order
   FOR UPDATE;                                  -- N rows for an N-night stay
-- decide in application code, then
UPDATE room_inventory SET total_reserved = total_reserved + 1
 WHERE hotel_id = $1 AND room_type_id = $2
   AND date >= $3 AND date < $4;
INSERT INTO reservations (...) VALUES (...);
COMMIT;

ORDER BY date is load-bearing. A statement locking several rows takes them in whatever order the planner chose. Two overlapping stays that acquire the same nights in opposite orders is a deadlock: each holds a row the other waits for. Sorting rows into one agreed order makes the cycle impossible. Getting it wrong is worse than an error: Postgres does not look for the cycle until deadlock_timeout (one second by default) expires, so the bug costs a full second of latency before it costs an error.

Use pessimistic locking when the decision depends on values the write statement cannot refer to (a rate table, a loyalty tier, a policy row in another table) because an UPDATE’s condition can only mention columns of the row being written. A multi-night stay is not that case: one ranged UPDATE handles all N nights, and its rowcount (the number of rows it actually changed) is the decision. A rowcount equal to the number of nights means every night was available; anything less means some night sold out, and rolling back releases the nights that did succeed.

Fix 2 — optimistic: version column plus retry

Optimistic control assumes conflicts are rare, takes no lock, records which version of the row it read, and refuses the write if that version has moved on. That is what the version column is for.

UPDATE room_inventory
   SET total_reserved = total_reserved + 1, version = version + 1
 WHERE hotel_id = $1 AND room_type_id = $2 AND date = $3
   AND version = $4                             -- the version the caller read
   AND total_reserved + 1 <= total_inventory;
-- rowcount 0: someone else moved first, or it is sold out. Re-read to tell.

A conflict wastes the whole attempt, and it only happens if a second writer arrives while the first is still in flight: a window of width T. On the hottest row that is about one retry in 3,600 bookings, so under normal load optimistic control is strictly cheaper than pessimistic. Under heavy contention it inverts: when c writers pile onto one row, total attempts are c(c+1)/2, growing with the square of the crowd (5.5 wasted attempts each at 10 writers, 25.5 each at 50).

Pessimistic and optimistic share the ceiling 1/T; they differ above it. Pessimistic makes callers queue, so latency rises while throughput holds. Optimistic burns wasted work as the square of the crowd, so throughput falls as load rises: a feedback loop where failures cause more failures. That is why a flash sale is the one place to switch to locking.

Fix 3 — the conditional update, plus the constraint

The third fix removes the read entirely: the availability check becomes part of the UPDATE’s WHERE clause, so there is no gap between deciding and writing.

-- schema backstop: CHECK (total_reserved <= total_inventory)
UPDATE room_inventory
   SET total_reserved = total_reserved + 1
 WHERE hotel_id = $1 AND room_type_id = $2
   AND date >= $3 AND date < $4                 -- half-open interval
   AND total_reserved < total_inventory;        -- the check IS the write
-- rowcount < nights means SOME night sold out; ROLLBACK releases the rest.

The price is zero: no extra round trip, no lock held beyond the one the UPDATE takes anyway, no version to read first, no retry loop. The condition is evaluated by the same statement that performs the write, while it holds the row lock: the same move as writing count = count + 1 instead of reading the count and assigning it.

The CHECK constraint is the backstop. It cannot prevent anything the conditional UPDATE already prevents; what it catches is the next code path (a bulk importer, an admin tool, a migration) written by somebody who does not know the rule. It turns a silent overbooking into a loud SQLSTATE 23514, which the API returns as a 409.

The four guards, side by side

Ship all four. They are not alternatives. Default to the conditional UPDATE, including for multi-night stays (the ranged form plus a rowcount check is the whole mechanism). Escalate to FOR UPDATE only when the decision must read tables the UPDATE cannot mention. Keep the CHECK forever, because it costs nothing and is the only guard that protects you from code you have not written yet.

Idempotency and the payment that must not run twice

There is a second way to sell a room twice: not two users racing, but one user’s request arriving twice. The client sends POST /v1/reservations, the response is lost to a dropped connection, and the client retries. From the server’s side that retry is indistinguishable from a second booking, and getting it wrong charges the card twice.

You cannot fix this by making delivery reliable: exactly-once delivery does not exist. What exists is at-least-once delivery plus a deduplication key: a string that lets the receiver recognise “I have already done this one”. The key must be chosen by the client, because only the client knows that two requests are one intent and not a customer genuinely booking two rooms.

Four idempotency rules

A canonical hash sorts the request’s fields into a fixed order, drops anything that differs between attempts, serializes the result the same way every time, and runs it through a hash such as SHA-256. Two attempts at the same intent produce the same fingerprint; two different requests almost certainly do not. An atomic claim is a single statement that both checks whether the key is taken and takes it if not, with no gap: INSERT ... ON CONFLICT DO NOTHING inserts the row or quietly does nothing if it already exists, reporting which through the rowcount. The alternative (a SELECT to check then an INSERT to claim) is the booking race moved one table over, with a window a whole network round trip wide.

import hashlib, json

VOLATILE = {"attempt", "retry_count", "client_ts", "requested_at", "trace_id"}

def idempotency_key(request: dict) -> str:
    """R1. A canonical hash of WHAT is asked for; anything that changes
    between attempts is excluded, or a retry becomes a new intent."""
    canonical = {k: v for k, v in sorted(request.items()) if k not in VOLATILE}
    return hashlib.sha256(
        json.dumps(canonical, sort_keys=True, separators=(",", ":")).encode()
    ).hexdigest()

def op_key(key: str, operation: str) -> str:
    """R2. One row per (intent, operation)."""
    return key + ":" + operation

The rule is not “look before you write”; it is “let the write be the look.” These four rules are shared across the payments and digital wallet chapters too, because three services with three different idempotency designs is how a company ends up with three different double-charge bugs.

The booking write, end to end

The whole booking is three phases, with the slow payment call between two transactions and inside neither.

flowchart LR
    T1["Transaction 1<br/>claim idempotency key<br/>take inventory (conditional UPDATE)<br/>insert pending reservation<br/>record saga step 2"] --> P["Authorize payment<br/>outside any transaction<br/>slow, ~3 s"]
    P --> T2["Transaction 2<br/>confirm, or release every night<br/>store response body"]

class SoldOut(Exception):
    """Raised inside the transaction, so the rollback is the release."""


def release_nights(db, res_id, body) -> str:
    """The decline path. The orchestrator retries this after a timeout with no
    way to know whether the first attempt landed, so it MUST be safe to run
    twice. The guard is the status transition -- the decrement itself is not
    idempotent and cannot be made so, because `total_reserved - 1` has no idea
    it already ran."""
    claimed = db.execute(
        "UPDATE reservations SET status='cancelled' "
        " WHERE reservation_id=%s AND status='pending'", (res_id,)).rowcount
    if claimed == 0:
        return "already released"            # nothing left to give back
    db.execute("UPDATE room_inventory SET total_reserved = total_reserved - 1 "
               " WHERE hotel_id=%s AND room_type_id=%s "
               "   AND date >= %s AND date < %s AND total_reserved > 0",
               (body["hotel_id"], body["room_type_id"],
                body["start_date"], body["end_date"]))
    return "released"

def reserve(db, gateway, key: str, body: dict):
    """One user intent -> at most one reservation and at most one charge.
    `key` is R1's canonical hash of `body`, sent by the client."""
    h = hashlib.sha256(json.dumps(body, sort_keys=True).encode()).hexdigest()

    with db.transaction():                       # step 1: the ONLY DB transaction
        claimed = db.execute(
            "INSERT INTO idempotency (key, request_hash, state) "
            "VALUES (%s, %s, 'in_progress') ON CONFLICT (key) DO NOTHING",
            (key, h)).rowcount == 1
        if not claimed:
            prior = db.execute(
                "SELECT request_hash, state, response_status, response_body "
                "FROM idempotency WHERE key = %s", (key,)).one()
            if prior.request_hash != h:
                return 422, {"error": "idempotency_key_reused_with_different_body"}
            if prior.state == "in_progress":
                return 409, {"error": "in_flight", "retry_after": 2}
            return prior.response_status, prior.response_body   # byte-identical replay

        # One ranged conditional UPDATE covers every night; the rowcount is the
        # decision. This is why a multi-night stay does not need FOR UPDATE.
        wanted = len(nights(body["start_date"], body["end_date"]))
        rows = db.execute(
            "UPDATE room_inventory SET total_reserved = total_reserved + 1 "
            " WHERE hotel_id=%s AND room_type_id=%s AND date >= %s AND date < %s "
            "   AND total_reserved < total_inventory",
            (body["hotel_id"], body["room_type_id"],
             body["start_date"], body["end_date"])).rowcount
        if rows != wanted:                       # some night was sold out
            raise SoldOut                        # rollback releases every night
        res_id = db.execute(
            "INSERT INTO reservations (status, idempotency_key, ...) "
            "VALUES ('pending', %s, ...) RETURNING reservation_id", (key,)).one()
        # The saga step is recorded BEFORE the step runs, in the transaction that
        # took the inventory: a crash between step 2 and step 3 is the window
        # where the money has moved and nothing says so; this row is what the
        # orchestrator resumes from.
        db.execute(
            "INSERT INTO saga_log (saga_id, reservation_id, step, state, "
            "next_attempt_at) VALUES (%s, %s, 2, 'pending', now())",
            (key, res_id))

    # step 2: OUTSIDE the transaction. The gateway gets its own derived key (R2).
    auth = gateway.authorize(amount=body["amount_cents"],
                             idempotency_key=key + ":auth")

    with db.transaction():                       # step 3: settle the outcome
        db.execute("UPDATE saga_log SET state=%s WHERE saga_id=%s AND step=2",
                   ("done" if auth.approved else "declined", key))
        if auth.approved:
            db.execute("UPDATE reservations SET status='confirmed' "
                       " WHERE reservation_id=%s AND status='pending'", (res_id,))
            out = (201, {"reservation_id": res_id, "status": "confirmed"})
        else:
            release_nights(db, res_id, body)     # idempotent; safe to retry
            out = (402, {"error": "payment_declined"})
        db.execute("UPDATE idempotency SET state='done', response_status=%s, "
                   "response_body=%s WHERE key=%s", (out[0], out[1], key))
    return out

The load-bearing points:

The idempotency claim and the inventory decrement are in the same transaction. If they were in two, a crash between them is exactly the window that produces a second reservation for one intent.

The gateway call is outside every transaction. A 3-second network call inside the booking transaction would take T from 5 ms to 3,005 ms and the ceiling from 200/s to 0.33/s: a 601x collapse. A 500-room flash sale would then be within 2.4x of saturation, one gateway slowdown away from queueing every booking behind a single row lock. This is the single most common structural mistake in the problem.

The saga_log step-2 row is written inside step 1’s transaction, before step 2 runs. That ordering makes “payment succeeded, confirm step crashed” recoverable: the row saying a payment is about to be attempted is durable before the attempt, so a process that dies between step 2 and step 3 leaves evidence instead of a silent hole. The orchestrator’s retry loop and backoff come from the message queue chapter; this function is the single-service half, and the log row is its handshake with the other half.

The gateway gets key + ":auth", not key. Two independent systems deduplicating on the same string is a collision waiting for the day somebody routes a refund through it.

Authorize, do not capture. An authorization reserves money on the card without moving it; a capture actually takes it. An unwanted authorization is voided and leaves nothing on the guest’s statement; an unwanted capture is a refund, which is visible, slow to settle, and generates a support call. So authorize at booking and capture later, at the cancellation deadline or at check-in.

A request that finds state = 'in_progress' gets a 409, not a second attempt. Waiting for the first attempt to finish would hold a request open across a slow gateway call, so an aggressive retrier leaks server threads until the service stops answering anyone.

Something must move the in_progress row, or the 409 becomes permanent. If a process dies after step 1 commits, the reservation sits pending until the sweeper releases it, but the idempotency row would stay in_progress for the whole 24-hour TTL, so every retry gets 409 in_flight for a room the sweeper already put back on sale. So the sweeper owns both rows: an in_progress idempotency row whose reservation’s hold has expired is moved to state='abandoned' in the same transaction that releases the hold, and abandoned is treated as absent, so the next retry re-runs the intent.

The release is guarded by a status change, not the decrement. total_reserved = total_reserved - 1 can never be made safe to run twice: subtraction has no way to know it already happened. Run blind twice, it does not merely release the room twice; it invents a room-night the hotel does not have, and the CHECK cannot catch it because total_reserved is moving down, the direction the constraint permits. So the WHERE status='pending' change carries the safety: it succeeds with rowcount 1 exactly once, ever, and the decrement runs only when it does.

TTL

The idempotency store’s TTL (time to live, how long a record survives before automatic deletion) is not a storage decision: 24 hours of records is ~426 MB, which is nothing. A candidate who proposes a 1-hour TTL “to save space” has optimised 400 MB and reopened the double-charge window. The TTL is a choice about meaning: how long two requests still count as the same intent. Bound it from below by the longest legitimate retry (a client backoff totals seconds, a human refresh is minutes, a mobile app resuming after a flight is hours) and from above by the point where replaying a stored response would be wrong (the user has since cancelled). 24 hours sits comfortably between, which is why every payment API converges on it. Keep the response body, not just the key, so a retry gets the same reservation id.

Cache invalidation, and the direction that matters

At a 400:1 read:write ratio, putting a cache in front of availability is obviously right. The interesting question is what happens when the cached number is out of date, because the two ways of being wrong are not symmetric.

• Stale-unavailable: the cache still says sold out after a cancellation freed a room. A guest who would have booked does not see it.
• Stale-available: the cache still shows rooms after the last one sold. A guest is led all the way to the payment page and then refused.

At a 60-second TTL, a stale “unavailable” hides about 0.005% of inventory for a minute. A stale “available” walks users to the payment page and fails them there, on the hottest rows, up to 17% of booking attempts. The two harms differ by about 3,600x, and the second lands at the most expensive moment in the product to say no, after card entry.

A stale “available” has one thing it does not do: it does not overbook. The reservation path re-checks against the database with the conditional UPDATE, so the cache may be wrong in both directions and the invariant is untouched. The cache is a hint about conversion, never a participant in correctness, which is exactly what lets you cache aggressively without arguing about consistency between two stores.

Four consequences:

• Invalidate on write; do not expire on a timer. Invalidating means deleting the cached entry as part of the write that made it wrong. At ~8 writes/s and one to three rows per booking, that is at most ~24 cache deletes/s: nothing. Keep a TTL of minutes only as a backstop for a lost invalidation; its job is to bound a bug, not to bound staleness.
• Bias the cached value downward. When a row is within one room of sold out, do not cache it; serve it from a replica. That converts the expensive direction of staleness into the cheap one exactly where it matters.
• Cache the availability of a row, not a whole search result. A result keyed on (city, date range, guests) has enormous cardinality, and one booking would invalidate thousands of entries. Keyed on (hotel_id, room_type_id, date), a booking invalidates one to three.
• The last remaining room is a hot key: a single key receiving far more traffic than any other, which no key-spreading scheme fixes. Fortunately only the reads concentrate; the write rate on it is 0.056/s, and the reads are served from a replicated cache entry.

Order the two stores the same as any write touching two: commit the database first, then delete the cache entry. Deleting first leaves a window in which another request reads the not-yet-committed database and repopulates the cache with the old value, leaving it wrong until the next write, which on a quiet date may be days away.

When the booking spans services

A booking has to change four systems (inventory, payment, loyalty, messaging) with four separate databases. No single transaction can span them.

Why not two-phase commit

Two-phase commit (2PC) makes several databases commit or abort together: a coordinator asks every participant to PREPARE (“promise you can commit”), and once all promise, it tells them to commit. The promise is the expensive part: a prepared participant holds its locks and cannot decide anything until the coordinator returns.

The steady-state cost is mild: two in-datacenter round trips and a prepare fsync take T from 5 ms to 7 ms, so 200/s becomes 143/s and nobody notices. The disqualifying cost is the failure mode. A coordinator that crashes after PREPARE leaves every participant holding locks with no authority to release them; through a 30-second failover the hottest row serves one booking every 30 seconds against a demand of one every 18, oversubscribed 1.67x, with everything behind it timing out. 2PC does not degrade gently; it converts a partial failure into an indefinite lock hold. It also cannot include the payment gateway, which will never enrol as a participant, so the hardest step stays outside it regardless.

The saga

The alternative is a saga: run the steps as ordinary local transactions, one service at a time, and pair each forward step with a compensation that undoes it after the fact. There is no rollback across the whole thing (some steps have committed and been observed), only a deliberate, recorded undo.

flowchart LR
    S1["1 reserve inventory<br/>local txn"] --> S2["2 authorize payment<br/>gateway, keyed"]
    S2 --> S3["3 confirm reservation<br/>local txn"]
    S3 --> S4["4 award loyalty points"]
    S4 --> S5["5 send confirmation<br/>NO compensation, therefore last"]

    S1 -.->|"C1 release nights"| X1(["inventory restored"])
    S2 -.->|"C2 void the auth"| X2(["nothing on the statement"])
    S3 -.->|"C3 cancel + notify"| X3(["user-visible: guest was told"])
    S4 -.->|"C4 revoke points"| X4(["balance restored"])

Three rules turn that picture into a design:

Order the steps so the least reversible one is last. Voiding an authorization leaves no trace; a refund leaves a line on the statement; a confirmation email cannot be unsent. Sending the confirmation before the charge settles means the only compensation left is a second email apologising for the first: that is not a compensation, it is an incident.

Every compensation is safe to run twice and safe to run when the forward step never happened. The orchestrator retries after a timeout with no way to know whether the first attempt landed. release_nights above does this by guarding on the status transition, not the decrement.

The pending reservation is a semantic lock: a lock enforced by the meaning of a column value, not the database’s locking machinery. It holds the room-nights without holding a database lock, which is what lets the transaction commit in 5 ms while the gateway takes 3 seconds. That hold is a tax on inventory: at ~8 bookings/s, a 15-minute TTL means ~7,292 concurrent holds, and a hold is a reservation, not a room: times the 3-night average, that is 21,876 room-nights, about 2.2% of a single night’s rooms (8.8% at 60 minutes). Choose the hold’s length from the longest legitimate payment interaction (a 3-D Secure challenge takes minutes), and run the sweeper, because a hold nothing ever releases is inventory permanently deleted by a crash.

The saga log is a durable work queue. Each step writes (saga_id, step, state, next_attempt_at) before it runs and updates the row after, so a crash mid-step resumes instead of restarting, which is why every step must be safe to repeat. The one thing a saga cannot give you is isolation: between step 1 and step 3 the reservation is visible in a half-finished state, so every reader has to understand what pending means. That is exactly why status is in the API contract instead of hidden.

Deliberate overbooking is a constant, not a bug

Hotels sell more rooms than they have, on purpose, because a predictable fraction of guests never arrive (a no-show). We can compute the allowance and express it so it changes no code and threatens no invariant. Here is the idea first: sell a few extra rooms, and you walk a guest only on the unlucky night when too few of them no-show, so the question is how many extras keep that night rare enough.

Sell 200 + a rooms against 200 physical ones, where a is the allowance. You walk somebody whenever fewer than a guests no-show. The number of no-shows is a binomial count: 200 + a independent guests each failing to arrive with probability 5%, written X ~ Binomial(200 + a, 0.05). The count is 200 + a, not 200, because you sold 200 + a rooms and every one has a guest who may not turn up. Taking n = 200 is the standard slip, and it overstates the walk probability at every allowance. So the probability of walking someone is P(X <= a - 1), and you want the smallest a whose walk probability the business accepts, here around 10%.

The normal approximation: mean 200 x 0.05 = 10, standard deviation (200 x 0.05 x 0.95) ^ 0.5 = 3.08. Stepping 1.28 standard deviations below the mean (the normal quantile that cuts off the bottom 10%) with a half-unit continuity correction (the adjustment for approximating a whole-number count with a smooth curve) gives 10 - 1.28 x 3.08 + 0.5 = 6.55, so 7.

The exact binomial confirms it, computed row by row because n moves with a:

a = 6:  P(walk) = P(X <= 5), X ~ Binomial(206, 0.05)   =  0.052
a = 7:  P(walk) = P(X <= 6), X ~ Binomial(207, 0.05)   =  0.104
a = 8:  P(walk) = P(X <= 7), X ~ Binomial(208, 0.05)   =  0.179

An allowance of 7 lands at 10.4%, within a rounding of the 10% the business chose; 6 lands at 5.2%, half the risk anybody asked for, bought with one saleable room a night at every property. Two ways this goes wrong: using Binomial(200, 0.05) instead of 200 + a shifts the rows to 6.2%, 12.4%, 21.3% and quietly changes the answer in the risk-increasing direction; and skipping the continuity correction gives 10 - 1.28 x 3.08 = 6.06, a cut point on a smooth curve read as a whole number, when for a count of guests it sits half a unit lower.

The way you express the result is total_inventory = 207 for that date: one number in one row, leaving the CHECK, the conditional UPDATE and the entire concurrency story untouched. That is the whole argument for two columns over one: overbooking is one honest integer, not a lie about how many rooms the hotel has. What is never acceptable is an overbook that arrives through a lost update, because then the amount is unbounded, the date unpredictable, and no yield model knows it happened.

Bottlenecks and scaling

Nothing here is close to a hardware limit, which is the point. The two rows that describe real work are the reservation table, which grows without bound over years, and pending holds, which grow without bound if the sweeper stops.

Replica lag (the delay between the primary committing and a replica reflecting it, usually milliseconds) gives the load-bearing rule: the booking transaction must never read a replica. Displaying availability from a replica is fine, but a SELECT ... FOR UPDATE or conditional UPDATE evaluated against data a second out of date is the double-booking race with a wider window. Route by intent (is this read part of a decision to sell?) not by whether the statement is a SELECT.

Failure modes

Every way the system fails leaves a concrete trace, has a signal that detects it, and already has a guard built earlier.

Alternatives rejected

Each design below names what it is good at, why this design does not use it, and where it would be right.

Redis DECR as the source of truth for inventory. Atomic, microseconds fast, no lock contention at any rate here. Rejected because the decrement and the reservation row cannot commit together (a crash between them sells a room with no booking or books a room it never claimed) and because Redis persistence acknowledges before the fsync window closes, so the counter can lose writes a confirmed reservation depends on. Correct as a front gate for pure rate-shedding, which is a different job from being the ledger.

Event sourcing the inventory. Stores the sequence of things that happened and derives current state by replaying them. A perfect audit trail and a natural fit for cancellations. Rejected because enforcing remaining >= 0 requires knowing remaining at the write, which means replaying the stream on every booking, so in practice you keep a running counter alongside it and serialize writes against it, which is the design already here plus more moving parts. Keep the event log as a derived audit stream, not the place the rule is enforced.

SERIALIZABLE everywhere. You stop having to reason about which anomalies your level permits, and the write-skew case snapshot isolation misses is handled. Rejected because it demands re-runnable transactions and a booking’s most important step is a payment; its abort rate grows superlinearly during the flash sale you bought it for; and a conditional UPDATE already makes the invariant a write-write conflict, which even snapshot isolation catches. Reach for it when an invariant spans rows the transaction does not write and cannot be a constraint, not this problem.

One remaining column instead of two. One less field. Rejected because a deliberate overbook then has to be a lie about how many rooms exist, an out-of-service room becomes indistinguishable from a sale, and reconciliation loses its second anchor. Two columns cost 2 bytes per row, ~25 MB across the whole table.

Two-phase commit across the services. Real all-or-nothing across four databases. Rejected on the failure mode (a coordinator crash holds every participant’s locks indefinitely and oversubscribes the hottest row 1.67x) and because the payment gateway will never enrol. Correct inside one database across several tables, which is exactly where this design already uses it: the idempotency claim, the decrement and the reservation insert commit together.

Per-physical-room inventory. You always know which room a guest has. Rejected on the 36 GB table, the re-assignment churn every date change causes, and above all because it contradicts how hotels operate: assignment is a check-in decision that trades against upgrades, adjoining rooms, and housekeeping routes. Correct for products where the unit really is the unit, such as a specific seat or a specific vacation home.

Conclusion

• The invariant is one sentence: a room-night is sold once. At eight writes a second, this is a correctness problem, not a scale problem: one primary, a cache, and two replicas carry all of it.
• The double-booking race is a lost update, permitted at the default READ COMMITTED. The defect is that the decision and the write were not one step, and the free fix is a conditional UPDATE (WHERE total_reserved < total_inventory) backed by a CHECK constraint. Reach for FOR UPDATE only when the decision must read other tables.
• The unit of inventory is (hotel, room type, date), and every date range is half-open [check_in, check_out): a wrong predicate sells the checkout night twice with no concurrency involved.
• A retry is one intent: dedup on a client-chosen key claimed with INSERT ... ON CONFLICT DO NOTHING, keep the payment call outside the transaction, and derive one key per side effect. Every compensation must be safe to run twice, guarded by a status transition, not a blind decrement.
• Across services, use a saga with ordered compensations (least reversible last) and a pending reservation as a semantic lock, not two-phase commit. Deliberate overbooking is total_inventory = 207 for a date, derived from the no-show rate: one honest integer that leaves every mechanism untouched.

One line to remember: at eight writes a second the whole design is one sentence held under pressure (a room-night is sold once) and every mechanism here exists to make the decision and the write the same step.

Further reading

• Gray & Reuter, Transaction Processing: Concepts and Techniques: the foundational treatment of isolation, locking, and two-phase commit.
• Berenson et al., “A Critique of ANSI SQL Isolation Levels” (1995): where lost update and the anomaly-per-level table come from.
• Garcia-Molina & Salem, “Sagas” (1987): the original paper defining the saga and its compensations.
• The database internals chapter for the MVCC, isolation-level, and locking machinery this design spends, and the distributed message queue chapter for the durable retry the saga log rides on.