← System Design Simulator

Hotel Reservation System System Design Interview Question

By Rahul Kumar · Senior Software Engineer · Updated · 9 components · 4 operations ·Source: Alex Xu, System Design Interview Vol 2, Chapter 7; Stripe idempotency docs; OpenTable and Booking.com engineering blogs

Problem: Design a hotel reservation system (Booking.com / Expedia / direct hotel chain) that takes rooms from search, through hold, payment, and confirmed booking, without overselling.

Overview

A hotel reservation system looks small on paper and is unforgiving in practice. The traffic numbers are tiny by modern internet standards, roughly 50 booking attempts per second at peak, but the correctness bar is absolute: you may never sell the same room twice. Two users clicking Book on the last room at the same moment must result in one confirmation and one polite rejection, not two charges and an angry guest at reception. The design leans on three classical moves: a short-lived Redis hold with TTL so the user has time to type a card number, a strongly consistent SQL inventory table that is the source of truth, and network-level idempotency keys so retries on flaky mobile links do not double-book. The interesting debates are whether to use SERIALIZABLE isolation or optimistic locking with a version column, whether holds belong in Redis or in the database, and how aggressively to cache the search path without serving stale availability.

Hotel Reservation System — Interactive Simulator

Runs fully client-side in your browser; no sign-up. Or open full screen →

Launch the interactive walkthrough for Hotel Reservation System — animated architecture diagram, step-by-step flow with real payloads, component swap, and a discrete-event stress simulator.

Summary

A consistency-critical booking pipeline: search is read-heavy and can be served from a denormalized availability cache; booking is write-critical and must never oversell. The design uses Redis for short-lived room holds (SETNX with TTL) plus idempotency keys, a strongly-consistent SQL database (MySQL / Postgres / Aurora) for the authoritative reservation + inventory tables, and an external payment provider (Stripe-style) with network-grade idempotency. The dominant design choice is 'hold then confirm' over optimistic 'book directly' — holds keep inventory reserved for 10 min during checkout so the user can enter payment details without the room disappearing or double-selling. The main tradeoff is locked but unpaid inventory during the hold window; bookable-room utilization is slightly reduced in exchange for a clean UX and a hard no-oversell guarantee.

Requirements

Functional

Non-functional

Capacity Assumptions

Back-of-Envelope Estimates

High-level architecture

The flow is search, then hold, then confirm. Search hits a denormalized availability cache derived from the inventory table by a background projection job; it is eventually consistent and that is fine, because the authoritative availability check happens at hold time. When the user clicks a room, the API issues a Redis SETNX with a 10-minute TTL on a key shaped like hold:{hotel_id}:{room_type}:{date_range}; if it succeeds, the user holds that room. The hold token is returned to the client and displayed as a countdown. At confirm time the Reservation API runs a transaction against the inventory table under SERIALIZABLE isolation, decrements the room count, and writes a row to the reservations table; if the transaction commits, the API calls the external payment provider with the client-supplied Idempotency-Key. The payment call is outside the SQL transaction because payment provider p99 is 1.5 seconds and holding a SERIALIZABLE transaction open that long would destroy throughput. If the payment fails after the SQL commit, a compensating transaction releases the room and the Redis hold. The trick that makes this safe is that the SQL row itself doubles as a semantic lock: SERIALIZABLE plus a UNIQUE constraint on (hotel_id, room_id, date) prevents two concurrent transactions from both decrementing inventory below zero. For high-contention room types we also use optimistic locking with a version column as a second line of defense, so a retry loop converts a serialization failure into a user-visible 409 instead of a stuck transaction.

Architecture Components (9)

Operations Walked Through (4)

Implementation

ReservationService with SERIALIZABLE isolation
public final class ReservationService {
  private final DataSource ds;
  private final RedisHoldStore holds;
  private final PaymentClient payment;

  public ReservationService(DataSource ds, RedisHoldStore holds, PaymentClient payment) {
    this.ds = ds; this.holds = holds; this.payment = payment;
  }

  public BookingResult book(BookingRequest req) throws SQLException {
    if (!holds.ownsHold(req.holdToken(), req.userId())) {
      return BookingResult.holdExpired();
    }

    // 1. Reserve inventory under SERIALIZABLE isolation.
    String reservationId;
    try (Connection c = ds.getConnection()) {
      c.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
      c.setAutoCommit(false);
      try {
        reservationId = reserveRoomTx(c, req);
        c.commit();
      } catch (SQLException e) {
        c.rollback();
        if (isSerializationFailure(e)) return BookingResult.conflictRetry();
        throw e;
      }
    }

    // 2. Charge payment OUTSIDE the SQL transaction (payment p99 ~1.5s).
    PaymentResult pr = payment.charge(req.idempotencyKey(), req.amount(), req.card());
    if (!pr.success()) {
      compensate(reservationId);
      return BookingResult.paymentDeclined(pr.reason());
    }
    holds.release(req.holdToken());
    return BookingResult.confirmed(reservationId);
  }

  private String reserveRoomTx(Connection c, BookingRequest req) throws SQLException {
    // Row-level semantics: UNIQUE(hotel_id, room_id, date) prevents double-sell.
    try (PreparedStatement ps = c.prepareStatement(
        "INSERT INTO reservations(id, hotel_id, room_id, user_id, check_in, check_out, status) " +
        "VALUES (?,?,?,?,?,?, 'PENDING')")) {
      String id = UUID.randomUUID().toString();
      ps.setString(1, id); ps.setLong(2, req.hotelId()); ps.setLong(3, req.roomId());
      ps.setString(4, req.userId()); ps.setDate(5, Date.valueOf(req.checkIn()));
      ps.setDate(6, Date.valueOf(req.checkOut()));
      ps.executeUpdate();
      return id;
    }
  }

  private boolean isSerializationFailure(SQLException e) {
    return "40001".equals(e.getSQLState()); // Postgres serialization_failure
  }

  private void compensate(String reservationId) { /* mark cancelled, restore inventory */ }
}
Hold-then-confirm flow with Redis SETNX
public final class RedisHoldStore {
  private final Jedis jedis;
  private static final Duration HOLD_TTL = Duration.ofMinutes(10);

  public RedisHoldStore(Jedis jedis) { this.jedis = jedis; }

  // Returns a hold token if the lock was acquired, empty if someone else holds it.
  public Optional<String> tryHold(long hotelId, long roomId, LocalDate checkIn,
                                  LocalDate checkOut, String userId) {
    String key = holdKey(hotelId, roomId, checkIn, checkOut);
    String token = UUID.randomUUID().toString();
    String res = jedis.set(key, userId + ":" + token,
        SetParams.setParams().nx().px(HOLD_TTL.toMillis()));
    return "OK".equals(res) ? Optional.of(token) : Optional.empty();
  }

  public boolean ownsHold(String token, String userId) {
    // Scan by token -> user; real impl stores reverse index at hold time.
    String raw = jedis.get("holdtoken:" + token);
    return raw != null && raw.startsWith(userId + ":");
  }

  public boolean extend(String token) {
    return jedis.pexpire("holdtoken:" + token, HOLD_TTL.toMillis()) == 1;
  }

  public void release(String token) { jedis.del("holdtoken:" + token); }

  private static String holdKey(long hid, long rid, LocalDate in, LocalDate out) {
    return "hold:" + hid + ":" + rid + ":" + in + ":" + out;
  }
}
Inventory update with optimistic locking (version column)
public final class InventoryRepo {
  private final DataSource ds;
  public InventoryRepo(DataSource ds) { this.ds = ds; }

  // Returns true if the decrement succeeded, false if the version moved under us.
  public boolean decrementAvailable(long hotelId, long roomTypeId, LocalDate date,
                                    int expectedVersion) throws SQLException {
    String sql = "UPDATE inventory SET available = available - 1, version = version + 1 " +
                 "WHERE hotel_id = ? AND room_type_id = ? AND date = ? " +
                 "AND version = ? AND available > 0";
    try (Connection c = ds.getConnection();
         PreparedStatement ps = c.prepareStatement(sql)) {
      ps.setLong(1, hotelId); ps.setLong(2, roomTypeId);
      ps.setDate(3, Date.valueOf(date)); ps.setInt(4, expectedVersion);
      return ps.executeUpdate() == 1;
    }
  }

  // Retry wrapper: up to N attempts, re-reading version each loop.
  public void reserveWithRetry(long hotelId, long roomTypeId, LocalDate date, int maxAttempts)
      throws SQLException, OutOfInventoryException {
    for (int attempt = 0; attempt < maxAttempts; attempt++) {
      InventoryRow row = load(hotelId, roomTypeId, date);
      if (row.available <= 0) throw new OutOfInventoryException();
      if (decrementAvailable(hotelId, roomTypeId, date, row.version)) return;
    }
    throw new SQLException("optimistic retry exhausted");
  }

  private InventoryRow load(long hotelId, long roomTypeId, LocalDate date) throws SQLException {
    /* select available, version from inventory where ... */
    return new InventoryRow(1, 0);
  }

  private record InventoryRow(int available, int version) {}
  public static final class OutOfInventoryException extends Exception {}
}

Key design decisions & trade-offs

Interview follow-ups

Related