Here is solutions for Design Booking System - Follow-up questions.

Main topic:

• Read-write seperate: elastic search for read, booking for write.

• Data-model design: design each product code -> to a record -> same product type only: book.

• Concurrency Booking: optimistic (show availability, handle failures) or pessimistic (real-time checks, slower UX) or distributed lock - when customer starts checkout, immediately reserve with seller API for 5-10 minutes

• Downtime of Payment System/Third Party Provider: handle downtime of payment system -> using Kafka + Temporal (State Machine) for retry and reverted.

• Flash Sale: reservation table + waiting room + idempotency.

Book Seller System:

Hotel Booking System:

Ecomerece System:

Flash Sale:

1. Patterns for Design Booking System

1. Using Elasticsearch for read-heavy: fed by CDC for search isolates OLTP from read-heavy geospatial/text queries and keeps search latency low while scaling independently.

2. Sharding the SQL database by hotel_id: localizes booking transactions and inventory updates to a shard.

3. State-machine - Temporal: Idempotency keys, a booking state machine, and event-driven updates via Kafka show good operational hygiene and support safe retries and downstream notifications.

4. The lock should be: hotel_id + day range (D1, D2, D3) that they book.

5. Persimistic Locking: High-concurrency booking

SELECT * FROM tasks WHERE status = 'pending' LIMIT 1 FOR UPDATE SKIP LOCKED;

- Worker 1 runs: SELECT * FROM tasks WHERE status = 'pending' LIMIT 1 FOR UPDATE -> It grabs and locks Task #1.

- Worker 2 runs the same query -> It sees Task #1 is locked. Instead of waiting, it skips Task #1 and instantly grabs Task #2.

1. Optimistic Locking: client need to retry multiple time -> can not use in high concurrency booking.

2. Different consistency level in third party data + booking data:

   • Stale data process quickly: 1 - 3 seconds.

   • Worker: webhook + async worker -> call to provier -> update ES.

3. Update product with muliple sku(Stock Keeping Unit):
   • Reservation table

   Reservation
    id = R1
    user_id = A
    sku_id = 100
    qty = 1
    status = RESERVED
    expires_at = 13:15
   

   • Phase 1: Reserve inventory (very short transaction)

   BEGIN;
   
   SELECT *
   FROM inventory
   WHERE sku_id = 100
   FOR UPDATE;
   
   available = 3
   
   UPDATE inventory
   SET
       available = available - 1,
       reserved = reserved + 1
   WHERE sku_id = 100;
   
   INSERT INTO reservation (
       order_id,
       sku_id,
       qty,
       expires_at
   );
   
   COMMIT;
   

   • Phase 2: User pays
   • Phase 3A: Payment succeeds

   BEGIN;
   
   UPDATE inventory
   SET
       reserved = reserved - 1
   WHERE sku_id = 100;
   
   UPDATE orders
   SET status = 'PAID';
   
   COMMIT;
   

   • Phase 3B: Payment fails

   BEGIN;
   
   UPDATE inventory
   SET
       available = available + 1,
       reserved = reserved - 1;
   
   UPDATE reservation
   SET status = 'EXPIRED';
   
   COMMIT;
   

   • Phase 4: What if the user never pays?
     • A background job runs every minute:

   Find expired reservations
   
       ↓
   
       available += qty
   
       reserved -= qty
   
       ↓
   
       Reservation expired
   

4. Separating Reservation and Order services: with a reserve-then-pay flow (with a TTL) aligns with preventing oversell and supports low latency

5. Using a Redis Lua script (single-thread): for atomic check-and-reserve on the hot path is a solid way to avoid race conditions in a flash sale
   • Instead of every query request you call MySQL, reuse Redis single-thread.

   UPDATE inventory
   SET available = available - 1
   WHERE sku_id = 100
   AND available > 0;
   

   stock:sku:100
   
   reservation:order123
   

   local stock = tonumber(redis.call("GET", KEYS[1]))
   
   if stock <= 0 then return -1 end
   
   redis.call("DECR", KEYS[1])
   
   return stock - 1
   
   

6. Waiting Room with short‑lived admission tokens: A release loop drains the queue at a rate your backend can handle. Start with a target attempt rate (e.g., 15–20k/sec if you need ~10k/sec successful reservations) and adjust based on p99 latency/error feedback -> a short‑lived, signed admission token (JWT/HMAC) with fields like productId, userId, issuedAt, expiresAt, nonce.