All writing

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.

nothing is written yet Request A reads: free writes Request B reads: free writes time
Both requests look at the same slot, both find it free, and both write. The gap between one request's check and its own write is all the room the other one needs.

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:

PostgresD1
Checks for overlapfirst, under a lockinside the insert
Stops a second rowexclusion constraintwhere not exists
Loser finds out fromthe check, or 23P010 rows changed
Loser is toldthe check’s answer409, slot not available

They differ most in what the rule covers.

Postgres D1 route, other writers EXCLUDE constraint in the schema the table route, other writers where not exists the table dashed: reaches the table with nothing in the way
Every writer on the Postgres side passes through the constraint under the table. On D1 the check is part of the booking route's own insert, and a writer outside that route would need its own.

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.