Chapter 7 Flashcards - Hotel Reservation System
flashcards volume2 hotel-reservation booking transactions concurrency
What is the core correctness requirement for a hotel reservation system?
?
No double-booking. Two users cannot both successfully book the last available room. This is enforced at the database level using atomic transactions — never rely on application-level read-then-write because a race condition can exist between the read and write steps.
What are the three strategies to prevent double-booking?
?
- Pessimistic locking (SELECT FOR UPDATE): Lock rows on read, no one else can read/write until commit. Simple but deadlock risk and low throughput. 2. Optimistic locking (version number + CAS): No lock on read, check version on update, retry if version changed. Good for low-conflict scenarios. 3. DB constraint (conditional UPDATE): UPDATE WHERE total_reserved < total_inventory — DB enforces atomically, no version column needed. Recommended approach!
Explain pessimistic locking for hotel reservations.
?
Use SELECT FOR UPDATE to lock inventory rows during read: BEGIN; SELECT * FROM room_type_inventory WHERE hotel_id=X AND date IN (…) FOR UPDATE; check availability in app; UPDATE total_reserved = total_reserved + 1; INSERT reservation; COMMIT. Lock held from read to commit — no other transaction can modify these rows. Con: Deadlock risk if two transactions lock rows in different order, low throughput on hot rooms/dates.
Explain optimistic locking with version number (CAS).
?
Add a version INT column to room_type_inventory. Read row without lock (gets version=7). In app, verify availability. UPDATE SET total_reserved = total_reserved + 1, version = version + 1 WHERE id = X AND version = 7. Check rows_affected: if 1 = success; if 0 = someone else changed version, retry. No deadlocks, good throughput. Best when conflicts are rare (read-heavy, low concurrency on same row).
What is the recommended approach for preventing double-booking and why?
?
DB constraint via conditional UPDATE: 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 rows_affected != expected_nights, at least one date is fully booked — rollback. Pros: Atomic at DB level, no version column, no deadlock, simple application logic. Combine with optimistic locking version for extra safety under high concurrency.
What is an idempotency key and why is it required for reservation APIs?
?
Client-generated UUID sent with every booking request. Server stores it with UNIQUE constraint in reservation table. If client retries (e.g., network timeout), server detects idempotency_key already exists and returns the original response without creating a duplicate booking. Without it: timeout → retry → two reservations charged for same dates. The UNIQUE constraint enforces deduplication at DB level — application just catches the unique violation and returns the cached result.
Walk through the full idempotency flow for a hotel booking retry.
?
- Client generates idempotency_key = UUID() before sending. 2. POST /reservations {… idempotency_key: “abc-123”}. 3. Server: SELECT * FROM reservation WHERE idempotency_key = “abc-123”. 4a. If NOT found: create reservation, return 201 Created. 4b. If found: return original 201 response (no new reservation). 5. Network timeout scenario: client retries with same key → Step 4b triggers → no duplicate. DB UNIQUE constraint prevents the INSERT from succeeding even if application logic has a bug.
What does the room_type_inventory table look like and why is it designed this way?
?
Columns: id, hotel_id, room_type_id, date, total_inventory, total_reserved, version. UNIQUE KEY on (hotel_id, room_type_id, date). One row per room type per day. This design allows: atomic per-day availability check, easy range queries for multi-night stays, conditional UPDATE to prevent overbooking. total_inventory = physical rooms; total_reserved = booked rooms; available = total_inventory - total_reserved. Version column enables optimistic locking.
What is the reservation table schema and what is special about it?
?
Columns: id (PK), hotel_id, room_type_id, user_id, status (pending/confirmed/cancelled/refunded), start_date, end_date, idempotency_key (UNIQUE), pre_auth_id, created_at, updated_at. The idempotency_key UNIQUE constraint is the critical field — it prevents duplicate reservations. status tracks the lifecycle. pre_auth_id links to the payment pre-authorization so it can be captured at check-in or voided on cancellation.
How does the atomic check-then-book work in SQL?
?
BEGIN TRANSACTION; UPDATE room_type_inventory SET total_reserved = total_reserved + 1 WHERE hotel_id = X AND room_type_id = Y AND date BETWEEN start AND end AND total_reserved < total_inventory; — check rows_affected == number_of_nights; if not, ROLLBACK (availability changed since read); INSERT INTO reservation VALUES (…); COMMIT. The WHERE total_reserved < total_inventory is the atomic guard — evaluated by the DB engine, not the application, so no race condition.
What is overbooking and why do hotels (and airlines) allow it?
?
Intentionally accepting more reservations than physical rooms. Based on historical cancellation rates (e.g., 10%). Formula: allow booking when total_reserved < total_inventory * (1 + overbooking_ratio). Example: 100 rooms, 10% buffer → accept up to 110 reservations. If all guests show up, hotel provides alternative accommodation or upgrades. Why allow it: empty rooms = lost revenue. Overbooking maximizes occupancy. The overbooking_ratio is configurable per hotel based on their historical cancellation data.
What is the caching strategy for room availability queries?
?
Cache room availability counts in Redis. Key: “availability:{hotel_id}:{room_type_id}:{date}”. Value: available_rooms count. TTL: 30 seconds (short because availability changes on each booking). On booking: DB write → invalidate Redis cache for affected dates. On search: Redis hit returns count instantly without DB query. CRITICAL: Never use cached value as final check before booking — always verify in DB during the transaction. Cache is for read performance only, DB is the source of truth.
How does Elasticsearch fit into the hotel reservation system?
?
Two-phase search: Phase 1 = Elasticsearch for text/geo search (hotel name, amenities, city, location). Returns matching hotel_ids. Phase 2 = Redis/DB for availability counts for those hotel_ids and requested dates. Why separate? Elasticsearch excels at text search and geo queries (find hotels within 5km of Times Square) but cannot atomically check inventory. Relational DB excels at transactional inventory management but is poor at text/geo search. Keep them separate and combine results at the application layer.
What fields would you index in Elasticsearch for hotel search?
?
hotel_id, name (text, analyzed), city (keyword), location (geo_point for distance queries), star_rating (numeric for range filters), amenities (keyword array for filter), description (text, full-text search), price_range. Geo queries: find hotels within X km of a point. Filter queries: amenities contains “pool” AND star_rating >= 4. Text queries: description contains “beachfront”. Results: list of hotel_ids passed to availability check.
Explain the payment pre-authorization flow for hotel reservations.
?
Step 1 Pre-auth (at booking): Reserve $X on card without charging. Card network holds funds. Returns pre_auth_id. Step 2 Reservation confirmed: Store pre_auth_id with reservation. Step 3 Capture (at check-in or D-1): Charge the reserved amount using pre_auth_id. Step 4 On cancellation: If before capture → void pre-auth (no charge). If after capture → refund. Why pre-auth: Protects hotel from no-shows. Protects guest from being charged before stay. Pre-auth typically expires in 7 days — must capture or re-auth before expiry.
What happens if the payment service fails mid-booking?
?
Use a saga pattern: Step 1 = Pre-auth payment → Step 2 = DB reservation insert. If pre-auth fails: return error, no DB write (safe). If DB write fails after pre-auth: compensating transaction = void the pre-auth. If void also fails: save reservation with status = “pending_payment_void” and a background job retries void asynchronously. Use dead letter queue for permanent failures. The key insight: pre-auth comes before DB write so we can always void if the DB step fails.
What are the scale estimates for a hotel reservation system?
?
Hotels: 5,000. Rooms: 1M (avg 200/hotel). Occupancy: 70% = 700K active reservations. Daily bookings: ~1,900/day (1M rooms × 70% / 365). Read/write ratio: 10:1 (search is far more frequent than booking). Inventory table size: 5,000 hotels × 20 room types × 365 days = 36.5M rows (manageable for MySQL with proper indexing). Cache: availability for all hotel+room+date combinations fits in Redis memory comfortably.
What is the read/write ratio for a hotel reservation system and how does it affect design?
?
10:1 read/write ratio. Reads (search, availability checks) dominate. Implications: (1) Cache availability aggressively in Redis to absorb read load. (2) Read replicas for DB to scale search queries. (3) Elasticsearch handles hotel search, not the booking DB. (4) Optimistic locking preferred over pessimistic locking — reads don’t block each other. (5) Inventory check during transaction still goes to primary DB (write-path) to ensure freshness.
How would you handle peak booking (10% of bookings in last 3 days before check-in)?
?
Autoscaling reservation service pods for the surge. Pre-warm Redis cache for popular hotels and near-term dates. Use message queue (Kafka/SQS) to buffer booking requests if DB becomes bottleneck. Circuit breaker around payment service calls to prevent cascade failures. Rate limit booking API per user (prevent bots from hammering availability). Monitor reservation service latency P99 and alert early. The 3-day peak is predictable — scale proactively, not reactively.
What are the four main services in a hotel reservation system?
?
- Hotel Service: CRUD for hotels, room types, photos, amenities. 2. Rate Service: Dynamic pricing, seasonal rates, discount rules. Kept separate because pricing logic is complex and changes frequently. 3. Reservation Service: Core booking, cancellation, viewing bookings, inventory management. 4. Payment Service: Pre-auth, capture, void, refund, integration with Stripe/Braintree/card networks. Separation of concerns: pricing doesn’t need to know about reservations; payment doesn’t need to know about hotel details.
How do you handle reservation cancellations atomically?
?
BEGIN TRANSACTION; UPDATE reservation SET status = ‘cancelled’ WHERE id = X AND status = ‘confirmed’; UPDATE room_type_inventory SET total_reserved = total_reserved - 1 WHERE hotel_id = X AND room_type_id = Y AND date BETWEEN start AND end; COMMIT. After commit: void or refund payment asynchronously. The two updates must be in the same transaction — if reservation update succeeds but inventory decrement fails, we have a phantom cancelled reservation without freed inventory (ghost booking).
What is the difference between hotel_id stored in room_type_inventory vs a JOIN approach?
?
Denormalization: storing hotel_id in room_type_inventory (alongside room_type_id which already implies the hotel) is intentional. It makes the availability query faster — the WHERE clause can use the composite index (hotel_id, room_type_id, date) without a JOIN. At booking scale, eliminating JOINs matters. The cost is slight data redundancy (hotel_id is already implicit via room_type_id → hotel_id FK), but it’s acceptable for a read-heavy, performance-critical table.
How would you generate unique reservation IDs at scale?
?
Use a distributed ID generator. Options: (1) UUID v4 — globally unique, no coordination, but 128 bits (larger index). (2) Snowflake ID — 64-bit: timestamp + datacenter + worker + sequence. Sortable by time, compact, high throughput. (3) DB auto-increment — works for single DB, becomes bottleneck with sharding. Recommended: Snowflake-style ID for reservation_id (sortable, compact, distributed). UUID for idempotency_key (client-generated, no server coordination needed).
How do you ensure the availability count in Redis stays consistent with the DB?
?
Write-through or cache-aside with invalidation: On every booking or cancellation, after the DB transaction commits, invalidate the Redis key for each affected (hotel_id, room_type_id, date). Next read is a cache miss → reads from DB → repopulates cache. Short TTL (30 seconds) as a safety net ensures eventual consistency even if an invalidation event is lost. Never update Redis directly without the DB transaction committing first — Redis is a cache, not the source of truth.
A hotel has 5 room types and a guest books for 4 nights. How many DB rows are updated?
?
5 room types × 4 nights = 20 rows in room_type_inventory… but actually only the 1 selected room type for 4 nights = 4 rows updated. The UPDATE targets WHERE room_type_id = Y (specific type chosen) AND date IN (‘2026-06-01’, ‘2026-06-02’, ‘2026-06-03’, ‘2026-06-04’). So rows_affected must equal 4 (one per night). If rows_affected < 4, at least one night ran out of availability for that room type → rollback entire transaction.
Total Cards: 25
Review Time: 20-25 minutes
Priority: HIGH — Core concurrency, transaction design, and idempotency patterns tested at Booking.com, Airbnb, Expedia
Last Updated: 2026-04-13