Overbooking guards that actually hold

The classic booking bug is not a logic error. The logic is right. It is that the check and the write are separate, and two requests can both pass the check before either writes.

SELECT count(*) FROM bookings WHERE room_type = ? AND date = ?   -- both see 9 of 10
INSERT INTO bookings ...                                          -- both write, now 11

At 2am on a normal Tuesday this never happens. At 9am on the morning your rates hit a comparison site, it happens repeatedly.

Application locks are not enough

The common fix is a mutex in application code. It works on one process. Run two instances behind a load balancer and it does nothing at all, quietly, which is worse than not having it.

The guarantee has to live where the data lives.

Make the database enforce it

The cleanest version is a counter row you update conditionally, so the check and the write are one statement:

UPDATE inventory
   SET sold = sold + 1
 WHERE room_type = ? AND date = ? AND sold < allotment

Zero rows affected means it was full. There is no window between reading and writing because there is no separate read. The database serialises the updates on that row for you.

For date ranges rather than single nights, a range type with an exclusion constraint pushes the same guarantee down a level:

EXCLUDE USING gist (
  room_id WITH =,
  daterange(check_in, check_out) WITH &&
)

Two overlapping stays for one physical room now cannot exist, regardless of what the application believes.

Deliberate overbooking is a different feature

Hotels overbook on purpose. That is a policy — allotment plus a per-date buffer — not an absence of a constraint. Model it as a number on the inventory row and let the constraint enforce that number. “We allow 3 over on Fridays” is a value. “Sometimes we accidentally sell 11 rooms” is a bug. They should not share an implementation.

Test it the only way that works

A single-threaded test proves nothing here. Fire fifty concurrent requests at the last available room and assert that exactly one succeeded and forty-nine got a clean “unavailable”. Anything less and you are testing that the code compiles.