Preventing double-bookings: race-free slot reservations in Postgres

Two customers open the same salon's booking page at the same moment. Both want the 4:00 PM slot with the same stylist. Both pages show it as free — because it is, for both of them, in the half-second before either confirms. Both tap "book." If your backend just checks availability and then inserts a row, you've now sold one chair twice.

That is the bug behind double-bookings in Postgres, and it never shows up in testing because you test by clicking once. I hit it building Hair Crezz, a configurable salon-booking product each owner deploys as their own branded site — where two customers can never book the same time is a hard product promise, not a nice-to-have. The fix isn't one clever trick. It's a few layers of Postgres, each covering the gap the one above it leaves.

The check-then-act race, precisely

Here's the naive flow, the one that feels obviously correct:

-- Request A and Request B both run this, interleaved
SELECT count(*) FROM bookings
WHERE stylist_id = 7 AND slot = '2026-06-21 16:00';
-- both get 0 → "slot is free"

INSERT INTO bookings (stylist_id, slot, customer_id)
VALUES (7, '2026-06-21 16:00', :customer);
-- both insert → double-booked

The window between the SELECT and the INSERT is the whole problem. Nothing stops request B from reading "free" while request A is mid-flight. This is a time-of-check-to-time-of-use race, and the same shape sells the last unit of inventory twice — exactly the failure I had to design out of Trendverse, where server actions write cart and stock with no race conditions on the count.

You can't fix this in application code alone. Any check you do in your app is just a read; the gap reopens the moment you act on it. The fix has to live where the data actually serializes — the database.

Layer one: a unique constraint as the last line of defense

Before anything clever, make the bad state unrepresentable. If a stylist can hold exactly one booking per slot, say so in the schema:

ALTER TABLE bookings
  ADD CONSTRAINT one_booking_per_slot
  UNIQUE (stylist_id, slot);

Now if both requests race past the check, the second INSERT doesn't double-book — it fails with a unique-violation (23505) you catch and turn into "sorry, just taken." This is the line I'd add first and remove last. Every other technique below is about turning that error into a clean rejection instead of a surprise; the constraint is what guarantees correctness even if every other layer has a bug.

A subtlety: for plain free/busy booking, UNIQUE is enough. If you're reserving ranges — a resource booked 2:00–3:30, no overlaps — reach for an exclusion constraint over a range column with a GiST index:

CREATE EXTENSION IF NOT EXISTS btree_gist;

-- `during` is a tstzrange column: [start, end)
ALTER TABLE bookings ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (
    resource_id WITH =,
    during      WITH &&   -- && = "ranges overlap"
  );

That's a unique constraint's smarter cousin: it rejects overlapping ranges, not just identical ones.

Layer two: do it in one transaction

A constraint protects the row, but you usually need more than one write to be atomic — insert the booking, decrement an availability counter, record a payment intent. Wrap the whole thing so it either all lands or all rolls back. With Supabase I push multi-step writes into a Postgres function called from a server action, so there's no chatty round-trip holding a transaction open across the network:

CREATE FUNCTION book_slot(p_stylist int, p_slot timestamptz, p_customer uuid)
RETURNS bookings AS $$
DECLARE
  result bookings;
BEGIN
  INSERT INTO bookings (stylist_id, slot, customer_id, status)
  VALUES (p_stylist, p_slot, p_customer, 'held')
  RETURNING * INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

A function body runs in a single implicit transaction. If the unique constraint fires, the whole call rolls back — no orphaned counter decrement, no half-booking.

Layer three: locks, so the loser waits instead of erroring

Relying on the constraint means the second request fails. That's correct, but a failure you can avoid is better UX than one you catch. Two tools serialize the contenders so only one ever reaches the insert.

Row locks with SELECT ... FOR UPDATE work when there's an existing row to lock — say, a slots table with one row per bookable time:

-- Inside the transaction: the second caller blocks here until the first commits
SELECT * FROM slots
WHERE stylist_id = p_stylist AND slot = p_slot
FOR UPDATE;
-- now re-check status and insert; the racer waits its turn

The catch: there has to be a row to lock. You can't FOR UPDATE a row that doesn't exist yet, so this fits a pre-seeded slot grid, not free-form inserts.

For the free-form case, advisory locks are the right tool. They let you lock an arbitrary key — a hash of (stylist_id, slot) — with no row required:

-- Held until the transaction ends; serializes everyone targeting this exact slot.
-- hashtextextended() returns bigint, matching the single-arg lock signature.
SELECT pg_advisory_xact_lock(
  hashtextextended(p_stylist || ':' || p_slot::text, 0)
);
-- check-then-insert is now safe: contenders are queued, not interleaved

pg_advisory_xact_lock auto-releases at commit or rollback, so a crashed request can't strand a lock forever. That self-cleanup is why I prefer the _xact_ variant over the session-scoped one, which you have to remember to unlock by hand.

Layer four: isolation level, when reads have to agree

Locks serialize writers to a known key. But some logic depends on a consistent read — "count today's bookings for this stylist, reject if over a cap." Under the default READ COMMITTED, two transactions can each read a count the other is about to change. Bumping the transaction to SERIALIZABLE makes Postgres detect that the two would conflict and abort one with a serialization error you retry:

// Server action: retry once on a serialization failure (sqlstate 40001)
async function withSerializableRetry<T>(fn: () => Promise<T>): Promise<T> {
  try {
    return await fn();
  } catch (e) {
    if (isSerializationFailure(e)) return await fn();
    throw e;
  }
}

SERIALIZABLE is the strongest guarantee and the most expensive — you pay in retries under contention. I reach for it only when correctness depends on multiple rows agreeing, and lean on the cheaper unique-constraint-plus-advisory-lock combo for the common "one slot, one booking" case.

The Next.js and RLS layer around it

All of this lives behind a server action — the client never touches SQL, so there's no trusting the browser to have checked availability. Postgres row-level security then scopes who can do what: in Hair Crezz a customer can only insert a booking as themselves and read their own, while staff and owners get broader policies across the multi-role auth. RLS doesn't prevent the race — two authorized customers still collide — but it ensures the only writes reaching your locks are ones you'd actually allow. Race protection and authorization are different jobs; you want both.

Don't forget the abandoned checkout

A real booking flow has a payment in the middle, and that reopens a quieter version of the race: if you hold the slot the instant someone clicks, a customer who closes the tab freezes a chair forever. In Hair Crezz the slot is held against a Razorpay deposit — the booking row goes in as held, and only the payment webhook flips it to confirmed. A held slot that never gets paid expires and frees up, so an abandoned checkout self-heals. Getting that webhook to fire exactly once is its own fight — I wrote it up in hardening Razorpay payment webhooks with idempotency.

What I'd tell my past self

• Make the bad state unrepresentable first. The unique (or exclusion) constraint is non-negotiable; everything else is ergonomics on top of it.
• Pick the lock that matches your data. Row locks for a seeded grid, advisory locks for free-form keys, SERIALIZABLE only when multiple rows must agree.
• The app check is a UX nicety, never a guarantee. Always assume two requests passed it at once, because eventually two will.
• Hold against a deposit, not against a click. A reservation that can't expire is a slow leak of your inventory.

The outcome: across every Hair Crezz deployment, the same time slot can't be sold twice — not because the app is careful, but because Postgres makes the double-booked row impossible to write.