Chapter 7: Design a Hotel Reservation System
volume2 hotel-reservation booking transactions concurrency
Status: 🟩 Interview ready
Difficulty: Hard
Time to complete: 50 min read + practice
Overview
A hotel reservation system lets users search for available hotels by location and date, reserve rooms, and manage bookings. Booking.com, Expedia, and Airbnb are the canonical examples.
Why this matters:
- Classic concurrency and double-booking problem (appears at Booking.com, Airbnb, Expedia interviews)
- Deep dive into database transactions, locking strategies, and idempotency
- Teaches how to handle inventory at scale (same patterns apply to ticketing, airline seats)
Problem Statement
Design a hotel reservation system that:
- Allows users to search available hotels by location and date range
- Reserves rooms without double-booking
- Supports cancellation
- Handles concurrent booking attempts (race conditions)
- Provides exactly-once reservation semantics (idempotency)
Step 1: Requirements & Scope (5 min)
Functional Requirements
Clarifying questions:
- How many hotels and rooms? → 5,000 hotels, 1 million rooms
- Search by? → Location, check-in/check-out dates, room type
- Double-booking prevention required? → Yes, absolutely no double-booking
- Cancellations allowed? → Yes
- Payment in scope? → Yes, pre-authorization flow
- Any overbooking allowed (like airlines)? → Support configurable overbooking
Scope:
- Search hotels by location + date
- Reserve a room (prevent double booking)
- Cancel reservations
- View reservation history
- Payment integration (pre-auth → capture)
Non-Functional Requirements
- No double-booking: Highest priority correctness requirement
- Read heavy: 10:1 read/write ratio
- Scale: 5,000 hotels, 1M rooms
- 70% occupancy: ~700K rooms occupied on average
- Peak booking: 10% of bookings occur within 3 days of check-in
- Idempotency: Exactly-once reservation semantics
- Low latency: Search < 100ms, booking < 500ms
- High availability: 99.99% uptime
Scale Estimation
Hotels: 5,000
Rooms: 1,000,000 (avg 200 rooms/hotel)
Occupancy: 70% → 700,000 active reservations at any time
Daily bookings: 1,000,000 × 70% / 365 ≈ 1,918 bookings/day
Peak bookings: 10% in last 3 days = ~64 bookings/hour in peak
Reads/writes: 10:1 ratio → reads dominate → cache aggressively
Room availability queries (search):
- 5,000 hotels × 365 days × ~20 room types = 36.5M records in inventory
- Very cacheable (availability changes infrequently)
Step 2: High-Level Design (10 min)
Services
┌───────────────────────────────────┐
│ API Gateway │
│ (Auth, Rate Limiting, Routing) │
└─────────────┬─────────────────────┘
│
┌───────────────────────────┼───────────────────────────┐
│ │ │
▼ ▼ ▼
┌──────────────────┐ ┌──────────────────┐ ┌──────────────────┐
│ Hotel Service │ │ Rate Service │ │Reservation Service│
│ │ │ │ │ │
│ - CRUD hotels │ │ - Dynamic pricing│ │ - Create booking │
│ - CRUD rooms │ │ - Seasonal rates │ │ - Cancel booking │
│ - Amenities │ │ - Room type rates│ │ - View bookings │
│ - Photos │ │ - Discounts │ │ - Check inventory │
└──────────────────┘ └──────────────────┘ └────────┬─────────┘
│
▼
┌──────────────────┐
│ Payment Service │
│ │
│ - Pre-auth │
│ - Capture │
│ - Refund │
└──────────────────┘
Data Models
Hotel data:
CREATE TABLE hotel (
hotel_id BIGINT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
address VARCHAR(500),
city VARCHAR(100),
country VARCHAR(100),
star_rating TINYINT, -- 1-5
description TEXT,
created_at TIMESTAMP
);
CREATE TABLE room_type (
room_type_id BIGINT PRIMARY KEY,
hotel_id BIGINT REFERENCES hotel(hotel_id),
name VARCHAR(100), -- "King Suite", "Standard Double"
description TEXT,
max_occupancy INT,
price_per_night DECIMAL(10,2)
);Availability/Inventory data:
-- Core inventory table: one row per hotel + room type + date
CREATE TABLE room_type_inventory (
id BIGINT PRIMARY KEY,
hotel_id BIGINT NOT NULL,
room_type_id BIGINT NOT NULL,
date DATE NOT NULL,
total_inventory INT NOT NULL, -- total rooms of this type
total_reserved INT NOT NULL DEFAULT 0,
UNIQUE KEY uk_hotel_roomtype_date (hotel_id, room_type_id, date)
);Reservation data:
CREATE TABLE reservation (
id BIGINT PRIMARY KEY,
hotel_id BIGINT NOT NULL,
room_type_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
status ENUM('pending', 'confirmed', 'cancelled', 'refunded'),
start_date DATE NOT NULL,
end_date DATE NOT NULL,
idempotency_key VARCHAR(64) UNIQUE NOT NULL, -- client-generated UUID
created_at TIMESTAMP,
updated_at TIMESTAMP
);API Design
Search hotels:
GET /v1/hotels/availability?city=NYC&check_in=2026-06-01&check_out=2026-06-05&guests=2
Response:
{
"hotels": [
{
"hotel_id": 101,
"name": "Grand Hotel NYC",
"star_rating": 4,
"available_room_types": [
{
"room_type_id": 201,
"name": "King Suite",
"available_rooms": 5,
"price_per_night": 299.00
}
]
}
]
}
Create reservation:
POST /v1/reservations
{
"hotel_id": 101,
"room_type_id": 201,
"user_id": 9999,
"start_date": "2026-06-01",
"end_date": "2026-06-05",
"idempotency_key": "550e8400-e29b-41d4-a716-446655440000" // UUID from client
}
Response 201 Created:
{
"reservation_id": 7654321,
"status": "confirmed",
"total_price": 1196.00
}
Response 409 Conflict (double-booking detected):
{
"error": "no_availability",
"message": "Room type no longer available for selected dates"
}
Cancel reservation:
DELETE /v1/reservations/{reservation_id}
Response 200:
{
"reservation_id": 7654321,
"status": "cancelled",
"refund_amount": 1196.00
}
Step 3: Deep Dive (25 min)
The Core Problem: Preventing Double Booking
This is the crux of the interview. Two users try to book the last available room at the same time.
Time: T0 - 1 room of type "King Suite" available on 2026-06-01
(total_inventory=3, total_reserved=2 → 1 available)
User A: Check availability → sees 1 room available
User B: Check availability → sees 1 room available
User A: Books → total_reserved = 3 ✅
User B: Books → total_reserved = 4 ❌ DOUBLE BOOKED!
Three solutions to prevent double booking:
Option 1: Pessimistic Locking (SELECT FOR UPDATE)
How it works: Lock the row when reading, preventing others from reading or writing until transaction completes.
BEGIN TRANSACTION;
-- Lock the inventory rows for the date range
SELECT * FROM room_type_inventory
WHERE hotel_id = 101
AND room_type_id = 201
AND date BETWEEN '2026-06-01' AND '2026-06-04'
FOR UPDATE; -- <-- Lock acquired, no one else can read/modify
-- Check availability
-- If available_rooms > 0 for all dates, proceed
UPDATE room_type_inventory
SET total_reserved = total_reserved + 1
WHERE hotel_id = 101
AND room_type_id = 201
AND date BETWEEN '2026-06-01' AND '2026-06-04';
INSERT INTO reservation (...) VALUES (...);
COMMIT;Pros:
- Correct: Guarantees no double-booking
- Simple to reason about
Cons:
- Deadlock risk: Two transactions lock rows in different order
- Low throughput: Popular rooms on popular dates become bottleneck
- Lock contention: Other requests queue up waiting for lock
When to use: Low concurrency scenarios, safety-critical operations
Option 2: Optimistic Locking (Version Number / CAS)
How it works: No lock on read. Add a version column. On update, check that version hasn’t changed since you read it. If version changed, someone else updated first — retry.
-- Schema change: add version column
ALTER TABLE room_type_inventory ADD COLUMN version INT DEFAULT 0;
-- Step 1: Read (no lock)
SELECT id, total_inventory, total_reserved, version
FROM room_type_inventory
WHERE hotel_id = 101
AND room_type_id = 201
AND date = '2026-06-01';
-- Returns: id=5001, total_inventory=3, total_reserved=2, version=7
-- Step 2: In application code, verify availability
available = total_inventory - total_reserved -- = 1 (OK!)
-- Step 3: Try to update with CAS (Compare-And-Swap)
UPDATE room_type_inventory
SET total_reserved = total_reserved + 1,
version = version + 1
WHERE id = 5001
AND version = 7; -- <-- Only succeeds if version is still 7!
-- Check rows affected:
-- If rows_affected = 1: Success! We won the race.
-- If rows_affected = 0: Someone else updated first (version changed) → RETRYPros:
- No deadlocks (no locks held)
- High read throughput
- Works well when conflicts are rare
Cons:
- Retry logic adds complexity
- High conflict rate under heavy load → many retries → poor performance
- Not suitable for very hot inventory (e.g., last few rooms of a popular hotel)
When to use: Low-to-moderate concurrency, reads >> writes, conflicts are rare
Option 3: Database Constraint (UNIQUE + Conditional Update)
How it works: Use a SQL constraint to enforce the invariant directly at the DB level.
-- Constraint: reserved cannot exceed total
-- Use a conditional update and check it atomically
UPDATE room_type_inventory
SET total_reserved = total_reserved + 1
WHERE hotel_id = 101
AND room_type_id = 201
AND date BETWEEN '2026-06-01' AND '2026-06-04'
AND total_reserved < total_inventory; -- <-- DB enforces this atomically
-- If rows_affected == expected_days: Success
-- If rows_affected < expected_days: At least one date is fully booked → RollbackPros:
- Atomic at DB level
- Simple: no version column, no application-level retry loop
- No deadlock risk
Cons:
- Less explicit than version-based locking
- Needs careful transaction handling to rollback partial updates
Recommended Approach: Optimistic Locking + DB Constraint
┌─────────────────────────────────────────────────────────────┐
│ Booking Flow │
│ │
│ 1. Client sends booking request with idempotency_key │
│ 2. Check idempotency_key → if exists, return cached result │
│ 3. Read inventory (no lock) for all nights │
│ 4. If any night has 0 availability → return 409 fast │
│ 5. BEGIN TRANSACTION │
│ a. UPDATE inventory WHERE total_reserved < total_inventory│
│ b. INSERT INTO reservation │
│ c. If any UPDATE affected 0 rows → ROLLBACK, return 409 │
│ d. COMMIT │
│ 6. Trigger payment pre-authorization │
│ 7. Cache result against idempotency_key │
└─────────────────────────────────────────────────────────────┘
Idempotency: Preventing Duplicate Bookings
Problem: Network timeout after DB commit — client retries, creating a second booking for same reservation.
Client → POST /reservations → [network timeout]
Client doesn't know if it succeeded
Client → POST /reservations (retry) → Creates duplicate booking!
Solution: Idempotency Key
- Client generates a UUID before sending the request:
idempotency_key = UUID() - Server stores
idempotency_keywith UNIQUE constraint inreservationtable - On retry, server sees key already exists → returns original response (no duplicate)
Request 1: POST /reservations {idempotency_key: "abc-123", ...}
→ Creates reservation #7654321
→ Stores idempotency_key in DB
Network timeout → client doesn't see response
Request 2: POST /reservations {idempotency_key: "abc-123", ...} (retry)
→ Server: SELECT * FROM reservation WHERE idempotency_key = "abc-123"
→ Found! Return same response as Request 1
→ NO new reservation created
Implementation:
-- UNIQUE constraint prevents duplicate inserts at DB level
CREATE TABLE reservation (
...
idempotency_key VARCHAR(64) UNIQUE NOT NULL
);
-- If INSERT fails with unique constraint violation:
-- → Fetch and return existing reservationDatabase Schema (Complete)
┌──────────────────────────────────────────────┐
│ hotel │
│ hotel_id (PK) | name | city | star_rating │
└──────────────────┬───────────────────────────┘
│ 1:N
┌──────────────────▼───────────────────────────┐
│ room_type │
│ room_type_id (PK) | hotel_id (FK) | name │
│ max_occupancy | price_per_night │
└───────────┬──────────────────────────────────┘
│ 1:N
┌───────────▼──────────────────────────────────┐
│ room_type_inventory │
│ id (PK) | hotel_id | room_type_id | date │
│ total_inventory | total_reserved | version │
│ UNIQUE(hotel_id, room_type_id, date) │
└──────────────────────────────────────────────┘
┌──────────────────────────────────────────────┐
│ reservation │
│ id (PK) | hotel_id | room_type_id | user_id │
│ status | start_date | end_date │
│ idempotency_key (UNIQUE) | created_at │
└──────────────────────────────────────────────┘
Overbooking Strategy
Hotels (and airlines) intentionally overbook to account for expected cancellations.
total_inventory = 100 (physical rooms)
overbooking_buffer = 10% → allow up to 110 reservations
Booking allowed when:
total_reserved < total_inventory * (1 + overbooking_ratio)
In DB:
UPDATE room_type_inventory
SET total_reserved = total_reserved + 1
WHERE total_reserved < (total_inventory * 1.10);
Why it works: Historical data shows ~10% cancellation rate. Overbooking by 10% fills rooms that would otherwise sit empty. If everyone shows up, hotel compensates guests with upgrades or alternative accommodation.
Caching Strategy
Problem: Availability queries are read-heavy (10:1 ratio). Querying DB for every search is wasteful.
┌─────────────────────────────────────────────────────────┐
│ Cache Architecture │
│ │
│ Search Query → Redis Cache → Cache Hit → Return data │
│ ↓ │
│ Cache Miss │
│ ↓ │
│ Read from DB → Populate cache │
│ │
│ Cache Key: "availability:{hotel_id}:{room_type_id} │
│ :{date}" │
│ Cache TTL: 30 seconds (availability is time-sensitive)│
│ │
│ Write path: DB write → Invalidate Redis cache │
└─────────────────────────────────────────────────────────┘
What to cache:
- Room availability counts (read frequently, changes on booking)
- Hotel metadata (name, description, photos — changes rarely, long TTL)
What NOT to cache:
- Reservation details (user PII, security concern)
- Final availability check before booking (must go to DB for correctness)
Search Optimization with Elasticsearch
Problem: SQL LIKE queries for hotel search by name/amenity/description are slow at scale.
┌──────────────────────────────────────────────────────────────┐
│ Two-Phase Search │
│ │
│ Phase 1: Elasticsearch (text search + geo search) │
│ Query: city=NYC, amenities=pool, rating>=4 │
│ Returns: List of matching hotel_ids │
│ │
│ Phase 2: Availability DB / Redis │
│ Query: Check room_type_inventory for returned hotel_ids │
│ Returns: Hotels with available rooms for requested dates │
│ │
│ Search DB ≠ Booking DB (separate concerns, separate scale) │
└──────────────────────────────────────────────────────────────┘
Elasticsearch index:
{
"hotel_id": 101,
"name": "Grand Hotel NYC",
"city": "New York",
"location": { "lat": 40.7128, "lon": -74.0060 },
"star_rating": 4,
"amenities": ["pool", "gym", "spa", "parking"],
"description": "Luxury hotel in midtown Manhattan"
}Payment Flow
Step 1: Pre-Authorization (at time of booking)
Client → Payment Service → Card Network
"Reserve $1196.00 on card"
Card network holds funds but does not charge yet
Returns: pre_auth_id
Step 2: Reservation Confirmation
Reservation created in DB with status = "confirmed"
pre_auth_id stored with reservation
Step 3: Capture (at check-in or D-1 day)
Payment Service → Card Network
"Capture $1196.00" (using pre_auth_id)
Card is charged
Step 4: On Cancellation
Payment Service → Card Network
"Void pre-auth" (if before capture) or "Refund" (if after)
Why pre-auth: Protects hotel from non-payment. Protects guest from being charged for cancelled bookings. Standard in travel industry.
Microservices Architecture (Final)
┌──────────────────────────────────────────────────────────────────┐
│ Clients │
│ (Web, iOS, Android, Partner APIs) │
└──────────────────────────┬───────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────────────┐
│ API Gateway │
│ Auth (JWT) | Rate Limiting | SSL Termination │
└──────┬───────────────────┬──────────────────────┬───────────────┘
│ │ │
▼ ▼ ▼
┌──────────────┐ ┌──────────────────┐ ┌───────────────────┐
│ Hotel Service │ │Reservation Service│ │ Rate Service │
│ │ │ │ │ │
│ - Hotel CRUD │ │ - Book room │ │ - Dynamic pricing │
│ - Room CRUD │ │ - Cancel booking │ │ - Seasonal rates │
│ - Photos │ │ - View bookings │ │ - Discount rules │
└──────┬───────┘ └────────┬─────────┘ └─────────┬─────────┘
│ │ │
▼ ▼ │
┌──────────────┐ ┌──────────────────┐ │
│ Hotel DB │ │ Reservation DB │ │
│ (MySQL RDS) │ │ (MySQL RDS) │ │
│ │ │ │ │
│ hotel │ │ reservation │ │
│ room_type │ │ room_type_ │ │
│ │ │ inventory │ │
└──────────────┘ └────────┬─────────┘ │
│ │
┌──────▼──────┐ │
│ Redis │◄────────────────┘
│ (Cache) │
└─────────────┘
┌─────────────────────┐
│ Elasticsearch │
│ (Hotel search, │
│ geo search, │
│ text search) │
└─────────────────────┘
┌─────────────────────┐
│ Payment Service │
│ (Stripe/Braintree │
│ integration) │
└─────────────────────┘
Design Summary — Full Booking Flow
1. User searches: GET /hotels/availability?city=NYC&check_in=...
└─ API Gateway → Hotel Service
└─ Elasticsearch: find hotels matching city/amenities
└─ Redis: check availability counts for matching hotels
└─ Return: list of hotels with available room types
2. User selects room and clicks "Book"
└─ Client generates idempotency_key = UUID()
└─ POST /reservations {hotel_id, room_type_id, dates, idempotency_key}
3. Reservation Service receives request
└─ Check idempotency_key in reservation table
└─ If exists → return existing result (deduplicate)
└─ If new → continue
4. Pre-authorization
└─ Reservation Service → Payment Service
└─ Payment Service → Card Network: pre-auth $X
└─ Returns: pre_auth_id
5. Atomic DB transaction
└─ BEGIN TRANSACTION
└─ UPDATE room_type_inventory
SET total_reserved = total_reserved + 1
WHERE hotel_id=X AND room_type_id=Y AND date IN (...)
AND total_reserved < total_inventory
└─ If affected_rows != expected_nights → ROLLBACK → 409 Conflict
└─ INSERT INTO reservation (status='confirmed', pre_auth_id)
└─ COMMIT
6. Invalidate Redis cache for affected inventory
7. Return 201 Created with reservation_id
8. At check-in (D-day): Payment Service captures pre-auth
9. On cancellation:
└─ UPDATE reservation SET status='cancelled'
└─ UPDATE room_type_inventory SET total_reserved = total_reserved - 1
└─ Payment Service: void/refund pre-auth
Interview Questions & Answers
Q: How do you prevent double-booking?
A: Use a database transaction with a conditional UPDATE: UPDATE room_type_inventory SET total_reserved = total_reserved + 1 WHERE total_reserved < total_inventory. The DB evaluates this atomically. If rows_affected == 0, no room was available and we rollback. Optionally add an optimistic lock version column for higher-read scenarios. Never rely on application-level read-then-write — always enforce the constraint in the DB.
Q: What is idempotency and why do you need it for reservations?
A: Idempotency means the same request produces the same result when repeated. For reservations, if a network timeout causes the client to retry, we could create duplicate bookings. The solution is an idempotency key (client-generated UUID) stored with UNIQUE constraint. On retry, the server detects the key already exists and returns the original response without creating a new reservation.
Q: Pessimistic vs Optimistic locking — which would you choose?
A: For hotel reservations, optimistic locking (or a DB constraint) is generally better because: (1) read-heavy workload means most operations don’t conflict, (2) pessimistic locking creates contention on popular rooms/dates, (3) deadlock risk with pessimistic locking across multiple date rows. Use pessimistic locking only if the system requires strict serialization and conflict rates are very high.
Q: How would you handle the search at scale (5,000 hotels)?
A: Two-phase approach: Phase 1 uses Elasticsearch for text/geo search, returning matching hotel IDs quickly. Phase 2 checks Redis for real-time availability counts for those hotel IDs. This separates the search concern (text/geo) from the availability concern (inventory counts). Redis is particularly effective here since availability for a given hotel+room+date is a small integer that fits in a few bytes and can be cached with a short TTL (30 seconds is acceptable).
Q: What happens if the payment service fails during booking?
A: We need a saga or two-phase approach. The pre-auth happens before the DB reservation insert. If pre-auth fails → return error, no DB write. If DB write fails after pre-auth → void the pre-auth. If the void also fails → mark reservation as pending_payment_void and a background job retries the void asynchronously. Use a dead letter queue for permanent payment failures so they can be manually reviewed.
Key Takeaways
- Double-booking prevention: Conditional UPDATE in a DB transaction (
WHERE total_reserved < total_inventory) is the cleanest solution — atomic at the DB level, no deadlocks - Idempotency key: Client-generated UUID with UNIQUE constraint prevents duplicate reservations from retries
- Optimistic locking (version column + CAS) outperforms pessimistic locking for read-heavy booking workloads
- Separate search from booking: Elasticsearch for hotel discovery, relational DB + Redis for inventory management
- Cache availability aggressively: 10:1 read/write ratio means Redis can absorb most availability checks; always verify in DB before final commit
- Payment pre-authorization: Reserve funds before confirming; avoid charging before the booking is confirmed in DB
- Overbooking is intentional: Hotels accept it as a business model; encode
total_inventory * overbooking_ratioas the cap, not rawtotal_inventory
Related Resources
- ch09-distributed-id-generator - Generating reservation IDs at scale
- key-patterns > Idempotency - Idempotency patterns across services
- distributed-system-components > Database Transactions - ACID, transactions, locking
- ch04-rate-limiter - Rate limiting booking API to prevent abuse
Last Updated: 2026-04-13
Status: Core concurrency + transaction design — must know for booking/ticketing system interviews!