The probe
The inventory reservation problem. A listing is available on specific dates, and two users trying to book the same listing for overlapping dates cannot both succeed. The hard problem: prevent double-booking without making the availability check and reservation a single-threaded bottleneck.
Step 1 — Clarify
- Single room per listing (Airbnb) or multiple rooms (hotel)?
- Search: by location + date range + filters?
- Booking flow: instant book (reserve immediately) or request-to-book (host approves)? - How to handle the “someone else just booked it” race condition?
Step 2 — Data Model
listings: listing_id, host_id, title, location, price_per_night, instant_book (bool) availability: listing_id, date, status (available/blocked/reserved), booking_id (nullable) PRIMARY KEY: (listing_id, date) — one row per listing per night
bookings: booking_id, listing_id, guest_id, check_in, check_out, status
(pending/confirmed/cancelled),
total_price, idempotency_key, created_at
The availability table with one row per listing per night is the key data model decision. Date range availability check = SELECT COUNT(*) FROM availability WHERE listing_id=X AND date BETWEEN check_in AND check_out AND status=’available’. If count = (check_out - check_in) in days, all nights are available.
Step 3 — Reservation flow (preventing double booking) BEGIN;
-- Check all nights available and lock them
SELECT COUNT(*) FROM availability
WHERE listing_id=:lid AND date BETWEEN :check_in AND :check_out
AND status=’available’
FOR UPDATE; -- locks these rows
-- If count != nights_requested: ROLLBACK (someone else has them)
-- Reserve all nights atomically
UPDATE availability SET status=’reserved’, booking_id=:booking_id
WHERE listing_id=:lid AND date BETWEEN :check_in AND :check_out; -- Create booking record
INSERT INTO bookings VALUES (:booking_id, ...);
COMMIT;
The FOR UPDATE lock on the availability rows means only one transaction can proceed for any given listing+date combination. The second concurrent booking attempt blocks, then fails the count check when it finally runs.
