I added a Postgres constraint to stop double bookings. It deadlocked instead.

I'm building a dental clinic booking API (NestJS, Drizzle, Postgres) as a portfolio project. The rule I care most about is that two patients can never have the same doctor at the same time.

I knew the textbook answer. If the app checks "is this slot free?" and then inserts, two requests can both check, both see "free", and both insert. So the database has to enforce the rule itself. Postgres has exclusion constraints for exactly this:

CREATE EXTENSION IF NOT EXISTS btree_gist; -- lets the index compare uuids with =

ALTER TABLE appointments ADD CONSTRAINT appointments_no_overlap
  EXCLUDE USING gist (doctor_id WITH =, tstzrange(starts_at, ends_at) WITH &&)
  WHERE (status <> 'cancelled');

In plain words: no two appointments that aren't cancelled may have the same doctor and overlapping times. An insert that breaks the rule fails with error code 23P01, which my API turns into a 409 "slot taken" response. Every code path gets the check for free, and nobody can forget it. Done, I thought.

What broke

I wrote a script that fires ten booking transactions for the same slot straight at the database, all at the same moment, and ran it ten times. I expected one winner and nine quick 23P01 errors per round, which the API would turn into 409s.

Each round did get exactly one winner. But 36 of the 90 losers never got a 23P01. They got a deadlock error (40P01) instead, and the slowest request took 9 seconds. Retrying on deadlock made things worse, because the retries collided with each other again.

The data was always correct, never more than one booking per slot. But a patient who waits nine seconds for an error page does not care about that.

Why it happens

A unique index checks for duplicates and writes its entry in one step, so two inserts can't both pass the check. An exclusion constraint works the other way round. The Postgres source (execIndexing.c) says it in one line: "we always insert first and then check".

So when two transactions, A and B, insert overlapping rows at nearly the same time:

1. A writes its row. B writes its row.
2. A checks for conflicts, finds B's unfinished row, and waits to see if B commits.
3. B checks, finds A's unfinished row, and waits for A.
4. Each waits for the other. After deadlock_timeout (1 second by default), Postgres notices and kills one of them.

With ten transactions at once, these waits chain into multi-second stalls. The same source file calls this deadlock "fairly harmless", since one of the two was going to fail anyway, and adds that you just get a different error message. That different error message, after a nine-second wait, is exactly the problem.

The fix: one line before the insert

The booking transaction now starts by locking the doctor's row:

SELECT id FROM doctors WHERE id = $1 FOR UPDATE

Only one transaction can hold that lock at a time, so bookings for the same doctor now wait in line. The next one gets the lock only after the previous one has finished. By then the earlier booking is committed, so the new insert meets a committed overlapping row and fails at once with 23P01. Two unfinished overlapping inserts never exist at the same time, so the deadlock can't form. Bookings for different doctors lock different rows and never wait on each other. The transaction still does its other checks, like whether the doctor offers that slot at all; the constraint only guards overlaps.

Same script, same ten rounds, Postgres 17 in Docker on my laptop. Your numbers will differ; the shape should not:

Variant	Slowest request	What the 90 losers got
Constraint only	9,079 ms	54 × 23P01, 36 × deadlock
Constraint + retry 3× on deadlock	11,051 ms	27 × 23P01, 63 × deadlock
Constraint + lock doctor row first	20 ms	90 × 23P01

What I took from it

Keep both the constraint and the lock, because they do different jobs. The constraint is the guarantee: if some future code path forgets the lock, the worst case is a slow, ugly error, never a double booking. The lock makes losing fast and clean. The reverse, a lock with no constraint, is dangerous, because code that forgets the lock double-books silently.

And a concurrency test that passes once proves nothing. An earlier script of mine tested plain check-then-insert and looked safe, only because the ten transactions happened to run almost one after another. Adding a 20 ms sleep between the check and the insert, to stand in for real work, let all ten book the same slot.