Making double booking impossible: a database constraint, not a code check

26 October 2026Majed Alandajani

A booking engine looks simple: list the free slots, take the booking. The hard part hides in a single instant.

The scenario that breaks the check

Two people open the page at the same moment, see the same slot free, and click within the same second of each other.

The naive code:


is the slot taken?  no
is the slot taken?  no      (the second request, before the first writes)
book                        (the first)
book                        (the second)

Both checked, both found it free, both booked. The check itself did its job; at the instant it ran, the slot genuinely was available.

Why retrying does not fix it

Checking again right before the write narrows the race window. It stays open, and two concurrent requests can still land inside whatever sliver remains.

The only correct solution

Put a unique constraint on the slot, in the database itself. However close together the two inserts arrive, the database serialises them: the first succeeds, the second fails with a constraint error.

Application code catches that error and turns it into a sentence a person understands: "that slot was just taken, please pick another."

The real complexity: cancellation

A cancelled booking has to free its slot for the next person. Here is the trap: a plain unique constraint will keep blocking that slot, because the cancelled row still sits in the table.

The cure is a partial unique index that covers active bookings only:


CREATE UNIQUE INDEX ux_slot ON bookings(slot_start) WHERE status != 'cancelled';

A cancelled booking and a new one can now share the same slot, and two active bookings never will.

The test that proves it

Do not believe the constraint works because you wrote it:


book(slot);
assert.throws(() => book(slot), /UNIQUE/);   // the second is refused
cancel(slot);
book(slot);                                   // and after cancelling it is accepted

Mine runs against the real migration files, so the constraint that passes the test is the same constraint running in production.