How do you prevent double booking in a database?

Make the database reject the second booking instead of checking first in application code. In PostgreSQL, add an exclusion constraint such as EXCLUDE USING gist (staff_id WITH =, during WITH &&) on a tstzrange column, with the btree_gist extension installed. In MySQL, lock the staff member's row with SELECT ... FOR UPDATE, check for overlaps, then insert in the same transaction.

Why doesn't a unique index stop double bookings?

A unique index on staff and start time only catches two bookings that start at exactly the same moment. Appointments are intervals: a 10:00 to 11:00 booking and a 10:30 to 11:30 booking overlap but have different start times, so the index allows both.

What is the race condition in a booking system?

Two requests check availability at the same moment, both see the slot as free because neither has committed its insert yet, and both insert. It's called check-then-act, and it happens under default isolation levels in both PostgreSQL and MySQL.

How do you handle capacity for group classes?

Use a conditional update on the class session, for example UPDATE class_session SET seats_taken = seats_taken + 1 WHERE id = $1 AND seats_taken + 1 <= capacity. If no row comes back, the class is full. Concurrent requests for the last seat can't both succeed, because the second update waits and then re-checks the condition.

How long should a booking hold last during checkout?

Long enough for a card payment with 3-D Secure on a phone, and short enough that abandoned carts don't make the calendar look full. Around ten minutes is a common default, with an extension once checkout has actually started. Opencals' Storefront API, for example, has a cart extend endpoint that adds five minutes.

Is a Redis lock enough to prevent double booking?

No. A lock with a timeout can expire while the process holding it is paused, and then two writers think they hold it. Use Redis locks to avoid duplicate work if you like, but keep a database constraint as the final check.

Prevent Double Booking: System Design That Holds (2026)

Q: How long should a booking hold last during checkout?

Long enough for a card payment with 3-D Secure on a phone, and short enough that abandoned carts don't make the calendar look full. Around ten minutes is a common default, with an extension once checkout has actually started. Opencals' Storefront API, for example, has a cart extend endpoint that adds five minutes.

Q: Is a Redis lock enough to prevent double booking?

No. A lock with a timeout can expire while the process holding it is paused, and then two writers think they hold it. Use Redis locks to avoid duplicate work if you like, but keep a database constraint as the final check.

The bug report always reads the same way. Two customers booked the 10:30 with the same stylist, both got a confirmation email, and the salon found out when both of them walked in. The code that let it happen looks perfectly reasonable: check whether the slot is free, and if it is, insert the booking.

That check-then-insert is the whole problem. Run it twice at the same moment and both checks see an empty slot, because neither insert has committed yet. It doesn't need heavy traffic, either. Two people on a Sunday evening tapping the same slot within a few hundred milliseconds of each other is enough, and a booking page that shows "only 2 slots left this week" makes that collision more likely, not less.

This is the system design I'd use to make double booking impossible rather than unlikely. Most of it lives in the database, which is where it belongs.

Double booking is a race between two "is this free?" reads, so no amount of checking in application code fixes it. Make the database refuse the second write: in PostgreSQL, an exclusion constraint on a time range per staff member or room; in MySQL, a row lock on the staff member before you check. Treat group classes as a seat counter updated with a conditional UPDATE, and treat a hold during checkout as a real row with an expiry, because the database can't expire it for you. The last gap is payment: a card can succeed after the hold has gone, so confirming a booking has to be idempotent and able to refund.

The availability check is a read, and reads don't lock anything

Here's the sequence with two requests, A and B, both for Lena at 10:30 on Thursday:

text

A: SELECT ... WHERE staff = lena AND overlaps(10:30–11:30)  -> 0 rows
B: SELECT ... WHERE staff = lena AND overlaps(10:30–11:30)  -> 0 rows
A: INSERT booking (lena, 10:30–11:30)                        -> ok
B: INSERT booking (lena, 10:30–11:30)                        -> ok

Under PostgreSQL's default isolation level, READ COMMITTED, that interleaving is completely legal. Each statement sees data committed before it started, and nothing that A did is visible to B until A commits. MySQL's InnoDB default, REPEATABLE READ, doesn't save you either, because a plain SELECT reads a snapshot and takes no locks.

The first fix most people reach for is a unique index on (staff_id, starts_at). It catches two bookings that start at exactly 10:30. It does nothing for a 10:00–11:00 colour and a 10:30–11:30 cut, which overlap by half an hour and start at different times. Appointments are intervals, and a uniqueness check on a point can't see an interval.

So the rule I'd hold to: the availability endpoint is a cache of the truth, and it's allowed to be wrong for a second. The write is where correctness lives, and the write has to be able to fail.

Let Postgres refuse the overlapping insert

PostgreSQL has had the right tool for this since 9.0, and range types since 9.2: an exclusion constraint. It's a generalised unique constraint where you choose the operator. "Equal staff member and overlapping time range" is exactly the rule you want.

sql

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE booking (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  staff_id   uuid NOT NULL,
  during     tstzrange NOT NULL,
  status     text NOT NULL DEFAULT 'confirmed',
  CONSTRAINT no_overlap_per_staff
    EXCLUDE USING gist (staff_id WITH =, during WITH &&)
    WHERE (status <> 'cancelled')
);

btree_gist is there so a plain uuid column can sit in a GiST index next to the range. The WHERE clause means cancelled bookings stop blocking the slot without being deleted. The PostgreSQL range-types documentation uses almost this exact example with meeting rooms, which is a good sign you're using the feature for what it was built for.

Now replay the race. B's insert either fails immediately or, if A hasn't committed yet, waits for A and then fails. It fails with SQLSTATE 23P01, exclusion_violation, which your code catches and turns into the message the customer actually needs: that slot just went, here are the next three.

If you're on PostgreSQL 18, there's a shorter spelling. A primary key or unique constraint can now end with WITHOUT OVERLAPS, as in UNIQUE (staff_id, during WITHOUT OVERLAPS), and the CREATE TABLE docs say it behaves like the equivalent EXCLUDE USING GIST. The partial WHERE for cancelled rows isn't available on that form, so I'd still write the explicit EXCLUDE.

Two details that bite later. Store the range as half-open, [10:30, 11:30), so a booking ending at 11:30 and one starting at 11:30 don't conflict; that's the default for tstzrange. And if a service needs ten minutes of clean-up after it, put the buffer inside the range you constrain. A buffer that only exists in the availability code is a buffer the database will happily let someone book into.

A booking usually occupies more than one thing

A massage needs a therapist and a treatment room. A podcast session needs a studio and, sometimes, an engineer. Constrain only the person and you'll double-book the room.

What's worked for me is splitting "the booking" from "what it occupies":

sql

CREATE TABLE allocation (
  booking_id   uuid NOT NULL REFERENCES booking(id),
  resource_id  uuid NOT NULL,          -- a staff member, room, chair or bike
  during       tstzrange NOT NULL,
  active       boolean NOT NULL DEFAULT true,
  EXCLUDE USING gist (resource_id WITH =, during WITH &&) WHERE (active)
);

One constraint covers every kind of resource. Creating a massage booking inserts one booking row and two allocation rows in a single transaction, so either both the therapist and the room are free or nothing is written. It also lets each allocation have its own range, which matters when the room needs fifteen minutes to turn over and the therapist doesn't.

Seats in a class are a counter, not an overlap

An exclusion constraint answers "can these two things coexist?" A yoga class with twelve mats is the wrong question for it: twelve bookings for the same class are supposed to overlap. What you need there is a count that can't pass a ceiling.

The cleanest version I know is a conditional update on the class session itself:

sql

UPDATE class_session
   SET seats_taken = seats_taken + $2
 WHERE id = $1
   AND seats_taken + $2 <= capacity
RETURNING seats_taken;

If it returns a row, you got the seats; insert the booking in the same transaction. If it returns nothing, the class is full. Two concurrent requests for the last mat can't both win, because in PostgreSQL's READ COMMITTED mode the second UPDATE waits for the first to commit and then re-checks the WHERE clause against the new row. The same shape works in MySQL.

The thing I'd avoid is SELECT count(*) followed by an insert. That's check-then-insert again, wearing a different hat.

Without exclusion constraints: lock the person, then look

MySQL has no exclusion constraints, and neither do most databases that aren't PostgreSQL. You can still make the race impossible; you just have to serialise it yourself. Lock a row that every booking for that resource must touch, then do your overlap check, then insert:

sql

START TRANSACTION;
SELECT id FROM staff WHERE id = ? FOR UPDATE;      -- one at a time per staff member

SELECT COUNT(*) FROM booking
 WHERE staff_id = ? AND status <> 'cancelled'
   AND starts_at < ? AND ends_at > ?;              -- requested end, requested start

-- insert only if the count was 0
INSERT INTO booking (staff_id, starts_at, ends_at) VALUES (?, ?, ?);
COMMIT;

The FOR UPDATE on the staff row means two bookings for Lena queue behind each other while bookings for Marco carry on in parallel. For a service business that's no real cost: a busy salon takes a few hundred bookings a day, not a few hundred a second. For multi-resource bookings, lock every resource row involved, always in the same order (sort by id), or two transactions will deadlock each other.

The other options are worth knowing and I'd pick them less often. SERIALIZABLE isolation in PostgreSQL will detect the conflict and abort one transaction with SQLSTATE 40001, but then every booking path needs a retry loop, and that's easy to forget in the one code path nobody tests. Pre-generating slot rows with a unique key works well when every service is the same length on a fixed grid, like 30-minute tennis courts, and falls apart as soon as a 45-minute and a 60-minute service share a calendar.

Holds expire, and constraints can't tell time

Now add payment. Between choosing 10:30 and the card going through, the slot has to be reserved for this customer, or the second customer can buy it while the first is typing a card number. So the hold is a booking row with status = 'held' and an expiry time, and the exclusion constraint treats it exactly like a confirmed booking. Good.

The trap: you can't put hold_expires_at > now() into the constraint's WHERE clause. PostgreSQL requires that predicate to be immutable, and now() isn't. The constraint can't know a hold has lapsed, so an expired hold keeps blocking the slot until something removes it.

I'd handle that in two places. A sweeper job that cancels expired holds every minute or so keeps the calendar clean. And the booking transaction clears any expired hold that overlaps the requested range before inserting, so a customer never loses a slot to someone who closed their tab nine minutes ago:

sql

UPDATE booking SET status = 'cancelled'
 WHERE staff_id = $1 AND status = 'held'
   AND hold_expires_at < now()
   AND during && $2;

INSERT INTO booking (staff_id, during, status, hold_expires_at)
VALUES ($1, $2, 'held', now() + interval '10 minutes');

If two customers do this at the same moment, both cancel the stale hold, one insert wins, and the other gets 23P01. The constraint is still the referee.

How long should the hold be? Long enough for a checkout with 3-D Secure on a phone, short enough that abandoned carts don't make the shop look full. Ten minutes is a common default; give the frontend a way to extend it once checkout has actually started rather than making the initial hold longer.

The card can succeed after the hold is gone

This is the case I see teams find in production. The customer reaches the bank's 3-D Secure page, gets distracted, comes back eleven minutes later and approves. The hold expired at ten. Someone else booked 10:30 at minute ten and a half. Stripe now tells you the payment succeeded.

There's no locking trick that makes this go away, because the payment provider doesn't know about your locks. What you can do is make the confirm step honest:

sql

UPDATE booking SET status = 'confirmed', hold_expires_at = NULL
 WHERE id = $1 AND status = 'held'
RETURNING id;

If that returns the row, confirm and send the email. If it returns nothing, the hold was swept, and you refund automatically and tell the customer straight away, with the next free slots in the same message. It's a bad moment for them, and it's still much better than a confirmation email for a chair someone else is sitting in.

The same statement makes your payment webhook safe to receive twice, which it will be: Stripe says outright that an endpoint can get the same event more than once. The second delivery finds no held row and does nothing. Use an idempotency key on the call that creates the charge too; Stripe stores the first response for a key and returns it on retries, and can prune keys once they're 24 hours old, so generate one per checkout attempt, not per customer.

Where a Redis lock fits, and where it doesn't

A short-lived lock in Redis (SET key value NX PX 5000) on lena:2026-11-12 can stop two requests from even attempting the same slot, and it's a reasonable way to keep a slow availability computation from running twice. It is not a replacement for the constraint. A lock with a timeout can expire while the process holding it is paused in garbage collection, and then two writers believe they hold it at once. Martin Kleppmann's 2016 essay on distributed locking walks through that failure and why it needs fencing tokens; for a booking system the fencing token is, in effect, the database constraint. Keep the constraint, and treat any lock in front of it as a performance optimisation.

Shopify's inventory counts units, not hours

If you sell appointments through Shopify, the platform's own guard against overselling is inventory: a variant with a quantity of one can't be bought twice. That's exactly right for a t-shirt and the wrong shape for time. An appointment product doesn't have a quantity that means anything; the same "60-minute massage" variant is sold forty times a week, each one for a different interval, and Shopify's cart has no notion that two of those carts hold the same Thursday at 10:30.

So on Shopify, the overlap check has to live in the booking app, and the hold has to survive Shopify's checkout, which the app doesn't control. I went through what Shopify actually permits inside checkout in the article on embedding booking in Shopify checkout; the short form is to ask any booking app what holds the slot while the customer pays, and what happens if that hold expires mid-checkout.

What we do, and the parts we don't do yet

Opencals is a hosted booking engine, so if you build on it, none of the SQL above is yours to maintain. On the Storefront API, you create the appointment for the chosen slot and put it into a cart. Carts expire on their own, there's a cart extend endpoint that adds five minutes (the "extend once checkout has started" pattern from above), and starting checkout validates the cart's items before a payment is created. On Shopify, the slot is held from the moment it goes into the cart for a window the merchant sets, and a Shopify checkout validation function refuses to complete the order if that hold has expired, so the customer is told to rebook inside Shopify's own checkout rather than by an email afterwards. The Opencals for Shopify page shows that message as it appears.

Now the limits, because they affect how you'd design around us.

Webhooks aren't live. The webhooks guide lists the planned appointment and order events and says plainly that they're not yet available. If you mirror bookings into your own database today, you're polling the API, and your copy can lag behind ours.
Opencals isn't open source, so you can't read our locking code. What's open is the client side: the MIT-licensed Next.js templates such as Haar and the @opencals/storefront-sdk package on npm.
The free tier is a development store in test mode. It's the right place to try the race yourself with two browsers, but it won't take a real payment until you switch it to production.

If you're building this yourself instead, my advice is narrow: write the constraint before you write the availability endpoint. Availability is where the interesting algorithm lives, and it's tempting to start there, but the constraint is the part that stops the salon from finding out about your bug when two customers walk in. Once the database refuses the second booking, the rest is a matter of telling the customer nicely. For the rest of what a booking system asks of you after this, the developer's guide to booking systems covers reschedule links, refunds and reminders.

How to prevent double booking: the system design, from the database up