The double-booking bug I shipped in my Next.js booking app (and how Postgres fixed it)

I build a multi-tenant booking template with Next.js, Drizzle and Postgres. During an audit I found that my "double booking protection" didn't protect anything. Here's the bug, and the fix.

The naive version

This is roughly what I had:

const conflict = await db.select().from(bookings).where(
  and(
    eq(bookings.resourceId, resourceId),
    gte(bookings.startTime, start),
    lte(bookings.endTime, end),
  )
)
if (conflict.length > 0) throw new Error('SLOT_NOT_AVAILABLE')

await db.insert(bookings).values({ resourceId, startTime: start, endTime: end })

It looks fine. It has three bugs.

Bug 1: check-then-insert isn't atomic

Two requests for the same slot arrive at the same time. Both run the SELECT, both see no conflict, both INSERT. On serverless, where many instances run in parallel, this isn't theoretical.

Bug 2: the overlap condition is wrong

start >= A && end <= B only catches bookings fully inside the new one. A booking from 10:15 to 10:45 doesn't block a new one from 10:00 to 10:30. Two intervals overlap when:

existing.start < new.end && existing.end > new.start

Strict comparisons also mean back-to-back slots (10:00–10:30 and 10:30–11:00) are allowed, as they should be.

Bug 3: cancelled bookings still count

No status filter, so a cancelled booking blocked its slot forever.

The fix: a transaction-scoped advisory lock

Postgres advisory locks let you lock an arbitrary key. Locking on the resource ID serialises bookings for that resource only, while other resources keep booking in parallel:

await db.transaction(async (tx) => {
  // Released automatically at commit/rollback
  await tx.execute(sql`select pg_advisory_xact_lock(hashtext(${resourceId}))`)

  const conflict = await tx.select({ id: bookings.id }).from(bookings).where(
    and(
      eq(bookings.resourceId, resourceId),
      ne(bookings.status, 'cancelled'),
      lt(bookings.startTime, end),
      gt(bookings.endTime, start),
    )
  ).limit(1)

  if (conflict.length > 0) throw new SlotNotAvailableError()

  await tx.insert(bookings).values({ resourceId, startTime: start, endTime: end })
})

The second request waits on the lock, then sees the first booking and fails cleanly with a 409 instead of creating a duplicate.

Why not a unique constraint? Bookings are ranges, and two different start times can still overlap, so a unique index on start_time doesn't help. An exclusion constraint with btree_gist is the other solid option. I chose the advisory lock because it needs no extension and keeps the logic readable in one place.

Serverless note: with Supabase's transaction pooler (port 6543), configure postgres.js with prepare: false. A transaction-scoped lock works fine there, because the whole transaction runs on one connection.

Bonus: the timezone bug next door

new Date('2026-10-20T10:00:00') is parsed in the server's timezone, which is UTC on Vercel. A salon in Rome opening at 10:00 was storing 10:00 UTC. The fix was converting the business's wall-clock time to UTC with Intl.DateTimeFormat and the tenant's IANA timezone, including a second pass to handle DST changes.

Takeaway

If your "is this slot free?" logic is a SELECT followed by an INSERT, you have a race condition. Put both in a transaction and lock on what you're protecting.

---

This came out of hardening SlotKit, a Next.js booking template for agencies. There's a live demo if you want to try booking a slot twice.

---

Update (v1.5.2): as Arjun Sharma pointed out in the comments, an unbounded lock wait means one stuck transaction can queue every booking for that resource. The fix is to bound the wait inside the transaction:

await tx.execute(sql.raw(`SET LOCAL lock_timeout = '5000ms'`))
await tx.execute(sql`select pg_advisory_xact_lock(hashtext(${resourceId}))`)

SET LOCAL (not SET) matters behind a transaction pooler: the setting dies with the transaction instead of leaking to the next client. When it fires, Postgres raises 55P03 and the API returns a 503 "try again".

Comments

[Arjun Sharma]

Nice breakdown. The overlap condition bug is the one that surprises most people, since the containment check looks correct until you draw two partially overlapping intervals. One thing I would add: with the advisory lock approach, set a statement timeout around the lock acquisition, so a stuck transaction cannot queue every other booking for that resource behind it.

[SlotKit]

Thanks Arjun, good catch, and you're right: right now the lock wait is unbounded, so one stuck transaction would queue every booking for that resource behind it. I'll add SET LOCAL lock_timeout right before the advisory lock (SET LOCAL rather than SET, so it doesn't leak through the transaction pooler) and return a friendly "try again" response when it fires (error 55P03). I'll update the post once it ships.

[SlotKit]

Shipped in v1.5.2: SET LOCAL lock_timeout before the advisory lock (5s default), and a stuck lock now returns a 503 "try again". There's a test that holds the lock from another connection and checks the booking gives up within the timeout. Thanks again, I've credited you in the post.