Two ways to stop a double booking
How 2 booking systems stop the same race: one with a database constraint, one with a single guarded write.
2 people, one slot
A dentist opens on Monday morning to 2 patients booked at 9:00. A hotel has one room and 2 families holding it. Both bookings went through, and now somebody has to decide which of the 2 is real.
Every booking system has a function like this somewhere. Check whether the slot is free, and take it if it is.
const taken = await findOverlapping(slot)
if (taken.length > 0) return conflict()
await insertBooking(slot)
It’s correct for one person at a time and wrong for 2. The check and the write are 2 separate statements, or instructions to the database, so a second request can arrive in between, run the same check against the same unchanged table and get the same answer. Both requests are told the slot is free, and both take it. That’s a race condition: a bug that depends on the timing of 2 things running at once.
Nobody notices for months. The window is milliseconds wide, and most bookings are nowhere near each other.
I’ve dealt with this in 2 systems. One is a property booking system on Postgres. The other is a dentist appointment system on Cloudflare’s D1, a hosted database that runs SQLite. The fix is the same idea in both, and it looks nothing alike.
Postgres: say it once, in the schema
Postgres can be told the rule directly, as a constraint: a rule the database checks on every write to a table, whoever makes it. No room type lets 2 stays overlap in the same room, so that becomes the constraint:
ALTER TABLE bookings
ADD CONSTRAINT no_overlapping_room_bookings
EXCLUDE USING gist (
"roomId" WITH =,
tsrange("arrivalDate", "departureDate") WITH &&
)
WHERE ("bookingStatus" IN
('CONFIRMED', 'CHECKED_IN', 'PENDING_PAYMENT', 'PENDING')
AND "roomId" IS NOT NULL);
An exclusion constraint is the general form of a unique constraint. Unique
says no 2 rows may agree on a column. This says no 2 rows may have the same
roomId and overlapping dates. tsrange turns arrival and departure into
a range, one value with a start and an end, and && asks whether 2 ranges
overlap. The index behind it is GiST, a kind of index that can answer overlap
questions. It needs the btree_gist extension, because equality on an integer
and overlap on a range are 2 different index families and this asks for both
in one index.
The WHERE matters as much as the rest. A cancelled booking still has dates
and a room, and it mustn’t block anything, so the constraint only looks at
statuses that actually hold the room. Rows with no room assigned are out too:
a booking can exist before anybody decides which room it gets.
After that one statement, 2 overlapping stays in one room can’t be saved, whichever code tries.
Then why lock at all
The booking code still takes a lock before it checks. It runs inside a transaction, a group of statements the database commits or rolls back as one. The lock is an advisory lock: Postgres locks a number instead of a row, and here the number is an accommodation type’s id, so a second request for the same type waits.
const uniqueTypeIds = Array.from(new Set(ids)).sort((a, b) => a - b)
for (const id of uniqueTypeIds) {
await tx.$executeRaw`SELECT pg_advisory_xact_lock(${id})`
}
The constraint stops the bad row. It doesn’t stop 2 requests each spending a second on price calculations and availability maths before one of them finds out it wasted its time. The lock makes that work take turns per accommodation type, so the second request waits and then reads a table that already has the first booking in it.
3 details in those 4 lines:
_xact_, the transaction variant. A session-level advisory lock is held
by a database connection until something releases it. Behind a connection
pooler, which hands a few open connections to many requests in turn, the
connection you get back in transaction mode isn’t the one you had. A session
lock can outlive the request that took it and be held by a stranger. The
_xact_ lock is released at commit or rollback by definition.
Sorted. A booking can lock 2 accommodation types, in whatever order they came in. If another request books the same 2 in the other order, you get a deadlock: each holds the lock the other is waiting for. Sorting the ids means every request in the system takes them in the same order, and a deadlock needs 2 different orders.
Consistent across paths. The creation path takes the type lock and then a per-group lock. The modify path wants both too, and takes them in the same order for the same reason.
What the loser sees
The guest who loses did nothing wrong: they clicked at the same moment as
somebody else. A constraint violation still arrives as an exception, and by
default that’s a 500, a generic server error. Every Postgres error carries a
5-character code called a SQLSTATE, and an exclusion violation’s is 23P01.
So the code that catches it looks for that, and answers the way it would have
if the check had caught it:
if (meta.code === "23P01") return true
if (typeof meta.constraint === "string" &&
meta.constraint.includes("no_overlap")) return true
D1: put the rule inside the write
The dental system works with different tools. SQLite doesn’t have advisory locks, exclusion constraints or range types, and D1 won’t hold a transaction open across a check and a write while your code makes up its mind. What SQLite does guarantee is that a single statement is atomic: it happens whole, and no other write can land in the middle of it. So the rule stops being something the schema knows and becomes a condition on the write:
const overlapping = db
.select({ id: appointments.id })
.from(appointments)
.where(and(
eq(appointments.dentistId, dentistId),
ne(appointments.status, 'cancelled'),
lt(appointments.dateTime, conflictEnd),
gt(sql`${appointments.dateTime} + (${appointments.duration} * 60)`,
dateTime),
))
const inserted = await db.run(sql`
insert into ${appointments} (...)
select ${id}, ${dentistId}, ${bookedServiceId}, ${dateTime}, ...
where ${notExists(overlapping)}
`)
if (inserted.meta.changes === 0) return conflict()
insert ... select ... where not exists inserts the row only if the select
finds no overlapping appointment. It’s one statement, so the check and the
insert can’t be pulled apart. The 2 requests from the diagram both still
arrive. The first one’s write finds no overlap and inserts a row. The second
one’s write finds that row and inserts nothing. That isn’t an error: it’s a
successful statement that changed 0 rows.
D1 reports how many rows a statement changed in meta.changes. 1 means the
slot is yours, 0 means somebody got there first, and 0 becomes a 409, the HTTP
status for a conflict, saying the time slot isn’t available.
The overlap test is arithmetic rather than a range type:
date_time < requestedEnd AND date_time + duration * 60 > requestedStart.
2 intervals overlap when each starts before the other ends. tsrange(...) &&
says exactly the same thing in a way you can index.
Forcing the race in a test
A fix for a race needs a test that makes the race happen. Sending 2 requests at
once with Promise.all isn’t enough: I tried, and got a pass, and the pass
meant nothing because one request had finished its check and its write before
the other reached its check.
The test that works reaches under the code and holds both requests at the line that matters:
async function holdBack<T>(result: T) {
ran += 1
if (ran === 2) release()
await bothRan
return result
}
Every statement against the appointments table goes through a wrapper that counts arrivals and blocks until 2 have arrived. Neither request can go past its own database call until the other has reached the same point, so the race happens on every run. Then 2 bookings go out at once, and the test checks what the pair produced: one 201 (booked), one 409 (refused) and exactly one row.
Where the rule lives
Both systems now refuse the double booking. Side by side:
| Postgres | D1 | |
|---|---|---|
| Checks for overlap | first, under a lock | inside the insert |
| Stops a second row | exclusion constraint | where not exists |
| Loser finds out from | the check, or 23P01 | 0 rows changed |
| Loser is told | the check’s answer | 409, slot not available |
They differ most in what the rule covers.
The Postgres constraint lives under the table, so it applies to the booking route, the admin screen that assigns a room by hand, a migration, a script somebody runs at 2am, and code that hasn’t been written yet.
The D1 version lives inside one statement in one code path, and it protects that path. Another endpoint that inserts appointments would need the same condition in its own write.
What I’d keep
Check first anyway, even with a constraint or a conditional insert underneath. An availability query gives a fast, clean answer for nearly every booking, where nobody else wants the slot, and it makes the error path rare enough that it can be a little slower.
Give the loser one answer. Whether the refusal came from a check, a constraint or a statement that changed 0 rows, the person on the other end gets the same sentence, and it’s never a 500.
And put the rule as far down as the database allows: on Postgres that’s the schema, and on D1 it’s the write itself.