Why hotels get double bookings, and how to design them out
By Validure team · · 17 min read
A technical look at how one room gets sold twice. The inventory model, the race between two sources, a database constraint that makes it impossible, channel sync lag with worked numbers, and the audit query to run every evening.

It's 7 pm on a Friday. A 40-room hotel has one Deluxe room left for the nights of 14 and 15 November. A guest on the hotel's website is typing in card details. At the same moment a regular customer is on the phone with the front desk, asking for the same room. The desk checks the screen, sees one room free, and says yes.
Forty seconds later both bookings exist. On the 14th two people arrive for Room 204.
Nobody in that story was careless. Each person looked at the system, saw the room was free, and acted. The failure is in the design: checking and booking were two separate steps, and a second booking slipped between them. This post takes that apart. It covers how to model inventory, why the race happens, how to make it impossible at the database level, how channel lag adds a second source of trouble, and the checks to run every evening. It's written for hotel owners who want to ask better questions and for whoever builds or buys the system.
Rooms are two things: a type you sell and a room you hand over
Guests book "a Deluxe room". They sleep in Room 204. A model that confuses the two is where most trouble starts, so keep both.
| Table | Columns | What it holds |
|---|---|---|
| room_types | id, hotel_id, name, base_rate | What you sell |
| rooms | id, hotel_id, room_type_id, room_no | What you hand over |
| stays | id, room_id (nullable), room_type_id, stay daterange, status, kind | Every booking and every block |
The stay column holds a date range with an exclusive end. A guest who arrives on the 14th and leaves on the 16th occupies the nights of the 14th and 15th. In PostgreSQL that is daterange('2026-11-14', '2026-11-16'), and the half-open convention is built in: the range includes its start and excludes its end. That's what you want, because a guest leaving on the 16th and another arriving on the 16th must not clash.
Rooms also leave inventory without a guest. A leaking ceiling, a repainting job, a broken AC. Model that as another row in the same table with kind = 'ooo' (out of order) and a date range. If blocks and bookings live in different tables, someone will eventually sell a room that's being repainted. Terms vary by property: some hotels separate "out of order" from "out of service", where the first removes the room from sellable stock and the second doesn't. Pick your definitions and write them down.
The race, as a timeline
Here's the Friday evening from above, step by step. This is an example, but every system that checks first and writes second behaves this way.
| Time | Website | Front desk | Rooms free for 14 to 16 Nov |
|---|---|---|---|
| 19:02:10 | Guest searches, sees 1 Deluxe free | 1 | |
| 19:02:31 | Caller asks, desk sees 1 Deluxe free | 1 | |
| 19:02:40 | Guest pays, booking saved for Room 204 | 0 | |
| 19:02:52 | Desk saves booking for Room 204 | -1 |
The code behind each step usually looks like this:
free = count rooms of this type with no overlapping stay
if free > 0:
insert the booking
The read and the write aren't one atomic action. Between them, another transaction can commit. Under PostgreSQL's default isolation level, two transactions can both read "1 free" before either has written, and both then insert. You can fix this in application code with locks, or by running everything at serializable isolation and retrying on failure, but those protect only the paths that remember to use them. The phone screen, the import script and the channel connector are all separate paths.
The better rule is to let the database refuse the second write, whoever sends it.
Make it impossible: an exclusion constraint
PostgreSQL has a constraint type built for exactly this. An exclusion constraint says that no two rows may satisfy a given set of operators at once: here, same room and overlapping dates. It needs the btree_gist extension so the equality check on room_id can sit in the same index as the range check.
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE room_types (
id serial PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE rooms (
id serial PRIMARY KEY,
hotel_id int NOT NULL,
room_type_id int NOT NULL REFERENCES room_types(id),
room_no text NOT NULL,
active boolean NOT NULL DEFAULT true,
UNIQUE (hotel_id, room_no)
);
CREATE TABLE stays (
id bigserial PRIMARY KEY,
hotel_id int NOT NULL,
room_type_id int NOT NULL REFERENCES room_types(id),
room_id int REFERENCES rooms(id), -- null until a room is assigned
stay daterange NOT NULL,
kind text NOT NULL CHECK (kind IN ('stay', 'ooo')),
status text NOT NULL CHECK (status IN ('confirmed', 'in_house', 'cancelled', 'completed')),
source text NOT NULL,
CHECK (kind = 'stay' OR room_id IS NOT NULL),
EXCLUDE USING gist (room_id WITH =, stay WITH &&)
WHERE (status IN ('confirmed', 'in_house'))
);
Two details matter. The WHERE clause lets cancelled and completed stays keep their history without blocking the room. And rows with a null room_id never conflict, because null isn't equal to null. That makes the constraint safe for bookings that don't have a room yet.
Now replay the Friday evening against the table.
-- 19:02:40, website
INSERT INTO stays (hotel_id, room_type_id, room_id, stay, kind, status, source)
VALUES (1, 2, 204, daterange('2026-11-14', '2026-11-16'), 'stay', 'confirmed', 'website');
-- INSERT 0 1
-- 19:02:52, front desk
INSERT INTO stays (hotel_id, room_type_id, room_id, stay, kind, status, source)
VALUES (1, 2, 204, daterange('2026-11-14', '2026-11-16'), 'stay', 'confirmed', 'front_desk');
-- ERROR: conflicting key value violates exclusion constraint
The second insert fails with SQLSTATE 23P01, and the desk screen can say "that room was just sold, here are the alternatives". A booking for daterange('2026-11-16', '2026-11-18') on the same room succeeds, so same-day turnover still works.
When you sell by room type and the guest hasn't chosen a room, let the database pick one and move on if it's taken:
CREATE FUNCTION book_room(p_hotel int, p_type int, p_stay daterange, p_source text)
RETURNS int LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
FOR r IN SELECT id FROM rooms
WHERE hotel_id = p_hotel AND room_type_id = p_type AND active
ORDER BY room_no
LOOP
BEGIN
INSERT INTO stays (hotel_id, room_type_id, room_id, stay, kind, status, source)
VALUES (p_hotel, p_type, r.id, p_stay, 'stay', 'confirmed', p_source);
RETURN r.id;
EXCEPTION WHEN exclusion_violation THEN
CONTINUE; -- someone took this one, try the next
END;
END LOOP;
RETURN NULL; -- sold out for those dates
END $$;
At 40 rooms that's fast enough, and a NULL return is an honest "sold out".

Selling by type: a counter that cannot go past capacity
Sometimes you don't want to assign a room at booking time (more on that below). Then the thing to protect is a count per room type per night, not a room. The same idea works with a plain table and a CHECK:
CREATE TABLE type_night (
hotel_id int NOT NULL,
room_type_id int NOT NULL REFERENCES room_types(id),
night date NOT NULL,
capacity int NOT NULL,
sold int NOT NULL DEFAULT 0,
PRIMARY KEY (hotel_id, room_type_id, night),
CHECK (sold <= capacity)
);
-- sell one Deluxe for the nights of 14 and 15 November; expect 2 rows updated
UPDATE type_night
SET sold = sold + 1
WHERE hotel_id = 1 AND room_type_id = 2
AND night >= '2026-11-14' AND night < '2026-11-16';
Updates to the same row queue up behind each other, and the CHECK fails the whole statement if any night would go over capacity. Your code must verify the row count equals the number of nights, otherwise a missing row looks like success. This table also gives you the right number to show on a website: capacity - sold.
When a room goes out of order, reduce that night's capacity by one. If the hotel is already sold out, the CHECK stops you, and that failure is useful: it tells you that before maintenance starts someone must move a guest.

Channel managers: the second source of trouble
Everything so far is about writes that reach your database. OTAs hold their own copy of your availability, and a channel manager pushes updates to them. Between your sale and their update, the OTA can sell a room you no longer have.
How big is the window? It depends on how each channel is connected. Some accept pushes within seconds. Others are refreshed on a schedule. Check the actual behaviour of the channels you use, with your channel manager's documentation.
A worked example, with invented numbers. The last Deluxe room on a busy night just sold direct. OTA B shows availability for 15 more minutes before it catches up. On a hot night, say bookings for that room type arrive on OTA B at 4 per hour while the room is showing.
The chance of at least one OTA booking arriving in the stale window follows from a Poisson arrival rate: 1 - e^(-rate x window).
| Sync lag | Rate x window | Chance of a clash |
|---|---|---|
| 15 minutes | 4 x 0.25 = 1.0 | 1 - e^-1.0 = 63.2% |
| 3 minutes | 4 x 0.05 = 0.2 | 1 - e^-0.2 = 18.1% |
Now put money on it. Say a double booking costs you 12,900 (worked out in the walk section below). The expected cost per last-room direct sale is 0.632 x 12,900, about 8,150 for the 15-minute lag, and 0.181 x 12,900, about 2,340 for the 3-minute lag.
Compare that with a buffer: keep the last room of each type off the slow channel on high-demand nights. If the room would have sold there 30 per cent of the time at a rate of 4,500, the buffer costs 0.30 x 4,500 = 1,350 in expected lost sales. In both cases the buffer is cheaper than the clash. The advantage shrinks as sync gets faster, and at some point it flips. That's the whole logic of overbooking and stop-sell settings: they're a bet on lag and demand, and you can price them.
Intentional overbooking is a different bet: you sell above capacity because you expect no-shows. If you do that, price it and write the walk plan first. Never let channel lag create overbooking you didn't decide to take.
Assign the room at booking, or at check-in?
Both are legitimate. They fail differently.
| Assign at booking | Assign at check-in or the day before | |
|---|---|---|
| What the database guards | Each room, exclusion constraint | Each type per night, counter |
| Guest requests (floor, view, connecting rooms) | Easy to honour early | Need a note and a reliable process |
| Flexibility to reshuffle | Low; moving a stay means rewriting a room | High |
| Failure mode | A long stay blocks a room that a small gap could have filled | Arrival day shortfall: count is fine, but the right rooms are not ready |
| Best for | Small properties, few room types, specific-room requests | Chains, many OTA bookings, busy-night churn |
My position, for a 30 to 80 room property: sell by type with the counter as the commit, and assign physical rooms the day before arrival, except where a guest has asked for a particular room. Keep the per-room constraint on regardless. The counter guarantees you never sell more than you own. The constraint guarantees no room holds two stays. You need both because they catch different mistakes.
If you run a dozen rooms, one channel and a diary, none of this applies. You can see the whole hotel at a glance.
Housekeeping and maintenance feed availability
There are two different questions that people mix up: is the room free to sell for those nights, and is the room ready for a guest right now?
Free-to-sell comes from stays and out-of-order blocks. Ready-now comes from housekeeping status: dirty, being cleaned, inspected, ready. A dirty room is still sellable for tonight, because it will be cleaned before check-in. An out-of-order room is not sellable, and shouldn't be, even though it might be perfectly clean.
The fix is to make a maintenance block a row in the same table as a stay, under the same constraint. If a guest is booked into Room 204 from the 20th to the 23rd, the block is rejected and someone must decide, instead of finding out on the 20th.
INSERT INTO stays (hotel_id, room_type_id, room_id, stay, kind, status, source)
VALUES (1, 2, 204, daterange('2026-11-20', '2026-11-23'), 'ooo', 'confirmed', 'maintenance');
-- fails if the room is already booked in that range
This is the part of Validure HMS we built for the same reason: rooms, room types and housekeeping status sit beside bookings and availability, and a second booking for a room that's already taken is blocked, not just warned about. The status is something a housekeeping supervisor changes on a phone screen, and the desk sees it without calling anyone.
Early checkouts and late departures
Two small events that quietly create double bookings in systems where the calendar and the guest drift apart.
An early checkout: a guest booked from 14 to 18 November leaves on the morning of the 16th. If nobody shortens the stay, the room sits blocked for the nights of the 16th and 17th, and you turn away sales. Shorten the range and set the status in one statement:
UPDATE stays
SET stay = daterange(lower(stay), DATE '2026-11-16'),
status = 'completed'
WHERE id = 9001;
The nights from the 16th are now free. In a setup with a channel manager, remember that freeing inventory is also an update that has to travel out.
A late departure is the opposite. A guest asks to stay one more night, until the 17th, while somebody arrives on the 16th into the same room. Extending the range is just another update, and the constraint rejects it:
UPDATE stays SET stay = daterange(lower(stay), DATE '2026-11-17') WHERE id = 9002;
-- ERROR: conflicting key value violates exclusion constraint
That is the correct outcome. The desk now has to choose between moving the arriving guest, offering the staying guest another room, or saying no. Staying until 4 pm instead of noon is different: it doesn't change a night, so it's a housekeeping and arrival-time matter, not an inventory one.
When it happens anyway: a walk policy with numbers
Even with the controls above, double bookings can come from outside the database: a channel lag, a guest who stays on, a room that's discovered unusable at 11 pm. Decide what you do before it happens, in writing: who decides, which nearby property you call first, what you pay.
Some large chains cover at least a first night at the replacement hotel, but there's no universal rule. Check your own obligations with a lawyer.
An example cost, with invented prices. A guest booked at 4,500 a night for two nights is relocated to a comparable hotel nearby.
| Item | Amount |
|---|---|
| Replacement room, 2 nights at 5,500 | 11,000 |
| Taxi both ways | 900 |
| Goodwill credit toward a future stay | 1,000 |
| Total cost of the walk | 12,900 |
The original booking was worth 9,000. So the walk costs more than the booking was ever going to bring in, before counting a review or a guest who doesn't come back. This is the number to put against the cost of an empty room when you decide on buffers and overbooking.
Walk the guest with the least to lose (shortest stay, cheapest channel), and confirm the other room before they leave your lobby.
The arrivals audit, every evening
Controls fail quietly. A person should look at tomorrow's arrivals the evening before, while there's still time to act. Run these four checks in order.
- Are any two active stays overlapping on a room? (Zero if the constraint is in place. Run it anyway after any data import.)
- Are tomorrow's unassigned arrivals more than the free rooms of their type?
- Is anyone still marked in-house when they were due out before today?
- Are the rooms they'll go to clean, or at least scheduled for cleaning?
The first two as SQL:
-- 1. overlapping active stays on one room (should return no rows)
SELECT a.id, b.id AS other_id, a.room_id, a.stay * b.stay AS clash
FROM stays a
JOIN stays b
ON a.room_id = b.room_id
AND a.id < b.id
AND a.stay && b.stay
WHERE a.status IN ('confirmed', 'in_house')
AND b.status IN ('confirmed', 'in_house');
-- 2. tomorrow's unassigned arrivals versus free rooms, by type
SELECT t.name,
a.arriving,
COALESCE(f.free_rooms, 0) AS free_rooms,
a.arriving - COALESCE(f.free_rooms, 0) AS short_by
FROM (SELECT room_type_id, count(*) AS arriving
FROM stays
WHERE hotel_id = 1 AND kind = 'stay' AND status = 'confirmed'
AND room_id IS NULL AND lower(stay) = DATE '2026-11-15'
GROUP BY room_type_id) a
JOIN room_types t ON t.id = a.room_type_id
LEFT JOIN (SELECT r.room_type_id, count(*) AS free_rooms
FROM rooms r
WHERE r.hotel_id = 1 AND r.active
AND NOT EXISTS (
SELECT 1 FROM stays s
WHERE s.room_id = r.id
AND s.status IN ('confirmed', 'in_house')
AND s.stay && daterange('2026-11-15', '2026-11-16'))
GROUP BY r.room_type_id) f
ON f.room_type_id = a.room_type_id
WHERE a.arriving > COALESCE(f.free_rooms, 0);
Query 2 only looks at one night, so for multi-night unassigned stays run it for each night or use the type counter instead. Check 3 is one line:
SELECT room_id, upper(stay) AS was_due_out
FROM stays
WHERE status = 'in_house' AND upper(stay) < CURRENT_DATE;
Each row is a guest who should have left: either they've extended or the desk forgot to check them out.
Any row from these queries is tomorrow's problem delivered a day early. That's the point of running them.
Questions for any vendor
Does the system block a second booking at the database, or only warn on screen? What happens when two people click at once? How fast do channel updates go out, and how do you see failures? Does an early checkout free the nights? We're building Validure HMS (early access, 30 to 80 room hotels and chains) and I'd ask it the same things.
Sources and checks
The constraint syntax and range behaviour are documented by PostgreSQL: see the PostgreSQL documentation for range types, exclusion constraints and the btree_gist extension. Run the SQL above on a test database before relying on it. Prices, lag times and arrival rates in this post are invented for illustration. Take the real figures from your own booking history and your channel manager's documentation, and check any relocation obligations with a lawyer.
Frequently asked questions
What causes double bookings in hotels?
Two or more sources sell the same room before they see each other's sale: the desk, the website, phone, and online travel agents. Check-then-write logic, channel sync lag and rooms sold while out of order are the usual mechanisms.
Can software prevent double bookings completely?
Inside one database, yes: an exclusion constraint on room and date range makes a clash impossible for any writer. Third-party channels hold their own copy of your availability, so the delay on those has to be managed with fast sync, stop-sell rules or a buffer.
Should I assign a room at booking or at check-in?
For small properties with few room types, assigning early is simpler. For busier hotels with many OTA bookings, count by type when selling and assign the day before arrival. Keep a per-room constraint either way.
How much should I overbook?
It depends on your no-show and cancellation history, the cost of a walk and the value of an empty room. Published figures vary and mostly come from vendors, so use your own data. If a walk costs more than a night's rate, a small buffer usually beats a large overbooking margin.
What should a hotel do if a guest is double-booked?
Apologise, move the guest to an equal or better room at a nearby property at your cost, confirm the room before they leave, and find out how it got through. Have the policy written down before it happens.
Do I need software for this?
Not for a handful of rooms and one booking source. The case builds with room count, with the number of channels and with how many people can change the calendar at once.
