Preventing Double Bookings: Time Zones, Buffers and Locks

Two customers arrive for the same slot. The booking system showed each of them a free calendar, took both payments and sent both confirmations. Nothing crashed and no error was logged, which is why this bug survives testing and appears in the first busy week.

To prevent double bookings you have to close three separate gaps: two requests racing for one slot, times that mean different things in different places, and outside calendars that learn about changes late. Each has a fix.

Why an availability check alone does not prevent double bookings

Almost every booking flow does the obvious thing: look for an overlapping booking, and if there is none, insert the new one. Run by one user at a time, that is correct. Run by two users in the same half second, both checks finish before either insert, both see an empty slot, and both inserts succeed.

The window is small, so manual testing rarely finds it. A newsletter, a popular class opening for registration or one impatient double-click will. The fix is to make the check and the insert a single step that the database enforces.

Let the database refuse the second booking

A unique index for fixed slots

If every booking occupies exactly one predefined slot (the 10:00 appointment with a given practitioner), add a unique index on the resource and the slot start. The second insert fails with a duplicate key error, and your code turns that error into a polite "that time has just been taken" message. Canceled bookings have to leave the index, by moving to a history table or by indexing active rows only, or the slot can never be rebooked.

A row lock and an overlap check for variable lengths

When bookings have different durations, uniqueness is not enough because 10:00 to 11:00 and 10:30 to 11:30 do not share a start time. Open a transaction, lock the row for the resource being booked, run the overlap check, insert, and commit. A second request for the same resource waits at the lock until the first has finished, then sees the new booking.

START TRANSACTION;

SELECT id FROM resources WHERE id = :resource_id FOR UPDATE;

SELECT COUNT(*) FROM bookings
WHERE resource_id = :resource_id
  AND starts_at < :new_end
  AND ends_at > :new_start
  AND (status = 'confirmed'
       OR (status = 'held' AND hold_expires_at > UTC_TIMESTAMP()));

-- only when the count is 0:
INSERT INTO bookings (resource_id, starts_at, ends_at, status, hold_expires_at)
VALUES (:resource_id, :new_start, :new_end, 'held', :hold_expires_at);

COMMIT;

Two details matter. The comparison treats a booking as including its start and excluding its end, so 10:00 to 11:00 and 11:00 to 12:00 do not collide. And the lock is taken on the resource row, because you cannot lock booking rows that do not exist yet. In Laravel the same thing is DB::transaction() with lockForUpdate() on the resource query.

An exclusion constraint in PostgreSQL

PostgreSQL can enforce "no two rows overlap" directly, so no code path can forget the lock. With the btree_gist extension installed:

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE bookings
  ADD CONSTRAINT bookings_no_overlap
  EXCLUDE USING gist (
    resource_id WITH =,
    tstzrange(starts_at, ends_at, '[)') WITH &&
  )
  WHERE (status IN ('held', 'confirmed'));

Any insert or update that would overlap an active booking for the same resource is rejected by the database. The PostgreSQL manual describes the technique under constraints on range types. A constraint cannot look at the clock, so expired holds have to be deleted or moved to another status by a scheduled job, and cleared for that resource just before a new insert.

If a booking needs several resources at once, such as a room and a practitioner, lock them in a consistent order (lowest ID first). Two requests that lock the same pair in opposite orders will deadlock.

Hold the slot while the customer pays

Payment takes minutes: card details, a bank authentication step, sometimes a redirect. During that time the slot must not be sold to anyone else, and it must not stay blocked if the customer walks away.

1. When the customer picks a time, create the booking with status held and an expiry a few minutes ahead. Use the locking pattern above so only one hold can exist.
2. Show the customer a countdown.
3. When the payment gateway confirms payment, change the status to confirmed. Do this from the gateway's webhook, not only from the browser's return page, because customers close tabs.
4. A scheduled job releases holds that have passed their expiry.

Then handle the awkward case: the payment succeeds after the hold has expired. Re-run the availability check inside a transaction before confirming. If the slot is still free, confirm it. If it has gone, refund automatically and tell the customer. Where the gateway lets you set an expiry on the payment session, make it shorter than the hold so this case becomes rare.

Store instants in UTC and keep the time zone next to them

A booking's start and end should be stored as UTC. Comparisons then need no conversion. Alongside each resource or location, store its time zone as an IANA name such as America/Chicago or Europe/Berlin. Never store a fixed offset like "+01:00", because the offset for a place changes during the year.

Convert at the edges only: local time to UTC when a booking is created, UTC to local time when it is displayed, in emails as well as on screen. Name the zone in confirmations whenever the customer may be somewhere else, as with any online session.

Two practical traps. In MySQL, a TIMESTAMP column is converted using the connection's time zone while a DATETIME column is stored exactly as given, so set the connection time zone to UTC and use one type throughout. And for appointments far ahead, keep the intended local time and zone as well as the UTC value. Governments change time zone rules, and "9:00 at the clinic" is what the customer was promised.

Daylight saving time breaks slot arithmetic twice a year

On the day clocks go forward, one local hour does not exist. On the day they go back, one local hour happens twice. A day is then 23 or 25 hours long, and code that builds slots by adding 24 hours to yesterday's slots drifts by an hour.

• Generate slots from local opening hours for each calendar date, then convert each slot to UTC with a proper time zone library. Do not add fixed numbers of seconds across days.
• Build recurring bookings ("every Tuesday at 18:00") by local date and time, not by adding seven times 24 hours.
• Decide what happens to a slot that falls in the missing hour (usually it is not offered) and in the repeated hour (offer it once).

Buffers belong inside the availability check

Cleaning time, travel between visits and preparation are part of what a booking occupies. If buffers are applied only when drawing the calendar, every other path into the system (an admin booking by hand, an API call, an import) can ignore them.

Store buffer minutes before and after on the service, and expand the booking's blocked range by those amounts when you check for overlap and when you write the lock or constraint. Keep the customer-facing start and end separate from the blocked range, so the confirmation still says 10:00 to 11:00. Minimum notice ("no bookings within two hours") is the same kind of rule and belongs in the same server-side check.

External calendars are always slightly out of date

Staff block time in Google Calendar or Outlook, and the booking system has to respect it. The risk lies in the delay before one system hears about a change in the other.

Push notifications help but are not a guarantee. Google's documentation for Calendar push notifications says they are not fully reliable and that a notification carries no event details, so you still have to call the API to fetch what changed. Subscribed iCal feeds are slower still, since the subscriber decides how often to refresh.

What works:

• Treat your own database as the source of truth for bookings, and external calendars as a source of busy time.
• Listen for push notifications and also run a periodic incremental sync to catch whatever was missed.
• Query the staff member's free/busy status live at the moment of confirmation.
• Write each confirmed booking to the external calendar straight away and store the event ID it returns, so later changes update the same event.

Prove it before customers do

Add automated tests for the cases that cause real incidents: twenty parallel requests for one slot (exactly one must succeed), back-to-back bookings that share a boundary, a booking across each daylight saving change, and a payment arriving after hold expiry. Run the parallel test against the real database engine, since an in-memory substitute behaves differently under locks.

On a plugin-based site you depend on the plugin's own locking, so run the same parallel test on a staging copy before a busy season. If it fails, talk to the vendor or move the booking logic into code you control. Getting concurrency, time zones and payment timing right together is specialist work, and a large part of what we do when we build booking and scheduling systems, on WordPress or as a custom PHP application. The money side of the same flow is covered in deposits, cancellations and no-shows.

Frequently asked questions

Why do double bookings happen when the calendar showed the slot as free?

Because the calendar is a snapshot. Between the page loading and the customer submitting, someone else may have taken the slot, or two submissions were checked at the same instant. Only a check inside a database transaction, or a constraint enforced at insert time, is reliable.

How long should a slot be held during checkout?

Long enough to complete payment, including a bank authentication step, and short enough that abandoned holds do not hide real availability. Five to fifteen minutes is a common range, with a visible timer.

Can calendar sync alone prevent double bookings?

No. Sync tells you about busy time with a delay, and notifications can be missed. It reduces conflicts with staff members' personal calendars, but bookings made through your own system still need locking in your own database.