A booking system manages time-bounded reservations for finite resources: hotel rooms, airline seats, restaurant tables, or appointment slots. The core challenge is preventing double-booking under concurrent requests while maintaining low latency. The design must handle the entire lifecycle from hold (soft lock) through confirmation and cancellation.
Inventory Model
Resources are modeled as inventory: each seat, room, or slot has a unique ID, a type (class, room type, slot duration), and availability status. For seat-level resources (flights, events): a seat_availability table with columns (resource_id, date, seat_id, status: available/held/booked). For count-based resources (hotel rooms): an inventory table with (property_id, room_type, date, total_rooms, booked_count). Count-based is simpler; seat-level supports seat selection features.
Optimistic Locking for Booking
Prevent double-booking with optimistic locking: check availability (SELECT count), conditionally update (UPDATE … WHERE available_count >= requested AND …, check rowcount = 1). On conflict, retry or return “sold out.” Alternatively, use SELECT FOR UPDATE to lock the row for the duration of the transaction. The latter is simpler but holds a database lock; the former has higher throughput but requires retry logic. For high-concurrency resources (popular seats), pessimistic locking avoids retry loops.
Temporary Hold (Soft Lock)
Users browse and add items to a cart before paying. During this window, inventory should be temporarily reserved to prevent others from booking the same resource. Implement a hold with TTL: INSERT into holds table (hold_id, resource_id, user_id, quantity, expires_at). A background job or database TTL-based mechanism releases expired holds. The booking query considers a resource unavailable if a valid hold exists. TTL (typically 10-15 minutes for flights, 30 minutes for hotels) balances user experience with inventory recirculation.
Distributed Locking for Hot Inventory
For extremely popular resources (concert tickets going on sale, airline flash sales), thousands of users attempt to book simultaneously. Distribute the load with Redis distributed locks: acquire a lock on the specific resource_id before checking/updating inventory; only one process holds the lock at a time. Lock TTL prevents deadlock if the holder crashes. For very high concurrency, use a queue-based approach: a Redis list where users join the queue; a single worker processes bookings serially per resource, eliminating contention.
Idempotent Booking
Network failures can cause the client to retry a booking request that may have already succeeded. Use an idempotency key: the client sends a unique request_id with each booking attempt. The server checks if request_id is already in the bookings table; if so, return the existing booking without creating a new one. Store request_id as a unique constraint in the bookings table to handle concurrent retries at the database level.
Cancellation and Refund Policy
Cancellation releases the held inventory and initiates a refund according to the policy (full refund before N days, partial refund within N days, no refund after). Cancellations are new ledger entries rather than deletes. Increment the available inventory count atomically with the cancellation. The refund is processed asynchronously — create a refund_request record, process via the payment provider API, update status. Cancellation policy enforcement is a business rule applied before the cancellation is accepted.
Overbooking
Airlines overbook intentionally: historically, 5-10% of passengers no-show. The system allows booking up to 110% of capacity and handles the over-sell at check-in by offering compensation for volunteers to take a later flight. Implement overbooking with a configurable overbooking rate per inventory class. The available_count field allows values above the physical count (total_physical * overbooking_rate). Track the overbooking delta separately for yield management analytics.
Calendar and Time Zone Handling
Store all timestamps in UTC. Display in the user's local time zone. A hotel booking for “April 17” means midnight-to-midnight in the hotel's time zone, not the user's. Store check-in and check-out as date (not datetime) with the property's time zone explicitly recorded. A flight departure at “10:00 JFK” should be stored as the UTC equivalent. Time zone bugs cause double-bookings at daylight-saving transitions; test explicitly around DST boundaries.