Reservation Availability Checking

Picture two people booking the last seat at a concert the moment tickets go on sale. Both search at the same instant. Both see 1 seat available. Both click "Buy Now." Without careful coordination, the system confirms both purchases, charges both cards, and generates two tickets with the same seat number — and one very unhappy person at the gate.

This is the double-booking problem — a specific form of the lost update race condition applied to finite inventory. Unlike a bank overdraft which can be caught and reversed, a double-booked seat at a sold-out show or a walk-in guest at a fully occupied hotel has no invisible recovery path. You either turn someone away, or you absorb the cost of a last-minute fix.

The root cause is the same as every race condition: a read-check-write cycle that is not atomic. The gap between checking availability and committing the reservation creates a window where another request can invalidate your assumption. Every strategy in this article closes that window in a different way.

The Two-Layer Inventory Model#

Before choosing a concurrency strategy, the data model must be right. Every large reservation platform — Booking.com, Airbnb, Ticketmaster — separates inventory into two distinct layers.

The supply layer holds what guests or buyers search against. For hotels this is a room_type (Deluxe King, Standard Twin) plus a per-day availability_slot tracking a unit counter. For ticketing it is the seat row itself. This layer answers: how many units are available?

The assignment layer holds the specific physical unit — Room 101, Seat G14. In hotels, assignment is deferred to check-in. In ticketing, the exact seat is chosen at booking time. The separation matters because it determines where you place your concurrency controls.

Hotel / OTA model
  room_type → availability_slot (units_total, units_booked) → reservation
                                                                    ↓ at check-in
                                                              physical_room

Ticketing model
  venue → section → seat (status: available | held | sold) → order_item

The hotel model manages a count — you don't care which specific room until check-in. The ticketing model manages individual unit state — every seat is either available or it isn't. These two shapes require different concurrency strategies.

The Availability Check Query#

For both models, the check query answers one question: is there at least one free unit for this request? For the hotel model:

SELECT COUNT(*) AS nights_available
FROM   availability_slot
WHERE  room_type_id = :room_type_id
  AND  date        >= :check_in
  AND  date         < :check_out     -- check-out night is not a stay night
  AND  status       = 'open'
  AND  (units_total - units_booked) >= 1;

If nights_available equals the number of requested nights, the room type is fully open. For ticketing:

SELECT id, section_id, row_number, seat_number
FROM   seat
WHERE  event_id   = :event_id
  AND  section_id = :section_id
  AND  status     = 'available'
LIMIT  :quantity;

This query alone is never safe to act on. It is a read without a lock. By the time your application reaches the write, another transaction may have taken the last unit. The check is useful for search results pages — showing counts to browsing users — but must never be the sole gate on a booking write.