Booking systems look simple until two customers try to
book the same slot at the same millisecond.
You check availability. Slot is free. You create the booking.
Meanwhile, another user did the exact same thing — same slot,
same resource, same moment. Now you have a double booking, an
angry customer, and a support ticket you didn't need.
This is a race condition, and it's one of the most common bugs
in booking systems built by developers who haven't dealt with it
before. Here's how to solve it correctly with Drizzle ORM and
PostgreSQL.
I ran into this building SlotKit — a booking SaaS template
for agencies. After the third time debugging a double booking
in staging, I stopped patching and fixed it properly.
Why the naive approach fails
The naive approach looks like this:
// ❌ This is wrong
async function createBooking(input: CreateBookingInput) {
// Step 1: check if slot is available
const conflict = await db
.select()
.from(bookings)
.where(
and(
eq(bookings.resourceId, input.resourceId),
gte(bookings.endTime, startDateTime),
lte(bookings.startTime, endDateTime)
)
)
if (conflict.length > 0) {
throw new Error('SLOT_NOT_AVAILABLE')
}
// Step 2: create the booking
await db.insert(bookings).values({ ... })
}
The problem: between Step 1 and Step 2, another request can
sneak in. Both requests pass the availability check. Both
create a booking. Double booking confirmed.
This isn't a theoretical problem — it happens in production
whenever your app gets moderate traffic, especially during
popular booking windows like Monday mornings or post-holiday
reopenings.
The correct solution: database transactions
The fix is to wrap the availability check and the booking
creation in a single database transaction with proper conflict
detection:
// ✅ This is correct
export async function createBooking(input: CreateBookingInput) {
const startDateTime = new Date(`${input.date}T${input.startTime}:00`)
const endDateTime = new Date(
startDateTime.getTime() + input.duration * 60 * 1000
)
return await db.transaction(async (tx) => {
// Re-check availability INSIDE the transaction
const conflict = await tx
.select()
.from(bookings)
.where(
and(
eq(bookings.resourceId, input.resourceId),
gte(bookings.endTime, startDateTime),
lte(bookings.startTime, endDateTime),
ne(bookings.status, 'cancelled')
)
)
.limit(1)
if (conflict.length > 0) {
throw new Error('SLOT_NOT_AVAILABLE')
}
// Create booking inside the same transaction
const [newBooking] = await db
.insert(bookings)
.values({
tenantId: input.tenantId,
resourceId: input.resourceId,
serviceId: input.serviceId,
customerName: input.customerName,
customerEmail: input.customerEmail,
startTime: startDateTime,
endTime: endDateTime,
status: 'confirmed',
paymentStatus: 'unpaid',
})
.returning()
return newBooking
})
}
When two requests arrive simultaneously, PostgreSQL's transaction
isolation ensures only one succeeds. The other hits a conflict and
gets a clean error to surface to the user.
The overlap detection logic
The conflict query deserves a closer look. You need to catch
every possible overlap between the requested slot and existing
bookings:
and(
eq(bookings.resourceId, input.resourceId),
gte(bookings.endTime, startDateTime), // existing booking ends after new one starts
lte(bookings.startTime, endDateTime), // existing booking starts before new one ends
ne(bookings.status, 'cancelled') // ignore cancelled bookings
)
The overlap condition endTime >= newStart AND startTime <= newEnd
catches all four overlap scenarios:
Scenario 1: New slot completely inside existing booking
Existing: [====existing====]
New: [=new=]
Scenario 2: Existing booking completely inside new slot
Existing: [=existing=]
New: [======new======]
Scenario 3: New slot overlaps start of existing booking
Existing: [====existing====]
New: [====new====]
Scenario 4: New slot overlaps end of existing booking
Existing: [====existing====]
New: [====new====]
All four are caught by the single condition above.
Handling the error in the API route
The transaction throws SLOT_NOT_AVAILABLE when a conflict
is detected. Catch it in your API route and return a clean
response to the client:
export async function POST(request: NextRequest) {
try {
const body = await request.json()
const booking = await createBooking(body)
return NextResponse.json({ booking }, { status: 201 })
} catch (error) {
if (error instanceof Error &&
error.message === 'SLOT_NOT_AVAILABLE') {
return NextResponse.json(
{ error: 'This slot is no longer available. Please choose another time.' },
{ status: 409 }
)
}
return NextResponse.json(
{ error: 'Something went wrong' },
{ status: 500 }
)
}
}
HTTP 409 Conflict is the correct status code here —
the request was valid, but conflicted with the current
state of the resource.
The availability calculation layer
The transaction handles the write-side protection. But you
also need the read-side: showing users which slots are
actually available before they try to book.
export async function getAvailableSlots(
resourceId: string,
date: string,
duration: number
): Promise<TimeSlot[]> {
const dateObj = new Date(date + 'T00:00:00')
const dayOfWeek = dateObj.getDay()
// Get working hours for this resource on this day
const workingHours = await db
.select()
.from(availability)
.where(
and(
eq(availability.resourceId, resourceId),
eq(availability.dayOfWeek, dayOfWeek),
eq(availability.isActive, true)
)
)
if (workingHours.length === 0) return []
// Get existing bookings for this day
const dayStart = new Date(date + 'T00:00:00')
const dayEnd = new Date(date + 'T23:59:59')
const existingBookings = await db
.select({
startTime: bookings.startTime,
endTime: bookings.endTime,
})
.from(bookings)
.where(
and(
eq(bookings.resourceId, resourceId),
gte(bookings.startTime, dayStart),
lte(bookings.startTime, dayEnd),
ne(bookings.status, 'cancelled')
)
)
// Generate available slots
const availableSlots: TimeSlot[] = []
for (const hours of workingHours) {
const workStart = timeToMinutes(hours.startTime)
const workEnd = timeToMinutes(hours.endTime)
for (
let slotStart = workStart;
slotStart + duration <= workEnd;
slotStart += duration
) {
const slotEnd = slotStart + duration
const isOccupied = existingBookings.some(booking => {
const bookingStart =
booking.startTime.getHours() * 60 +
booking.startTime.getMinutes()
const bookingEnd =
booking.endTime.getHours() * 60 +
booking.endTime.getMinutes()
return slotStart < bookingEnd && slotEnd > bookingStart
})
if (!isOccupied) {
availableSlots.push({
startTime: minutesToTime(slotStart),
endTime: minutesToTime(slotEnd),
})
}
}
}
return availableSlots
}
Why not use pessimistic locking?
You might wonder why not lock the row with SELECT FOR UPDATE
instead of using a transaction with conflict detection.
Pessimistic locking works but has downsides:
- Locks the row for the entire duration of the transaction
- Under high load, requests queue up waiting for the lock
- More complex to implement correctly across all edge cases
The conflict detection approach is optimistic — it assumes
most requests won't conflict, and only handles the rare case
where they do. This scales better and is simpler to reason about.
What I learned
The gap between check and insert is where bugs live.
Any code that reads state and then writes based on that
state has a potential race condition. The database transaction
is the only reliable way to close that gap.
Idempotency matters. If your client retries on failure,
make sure duplicate booking attempts are handled gracefully —
either by returning the existing booking or a clear conflict error.
Test with concurrent requests. Use a simple script to
fire 10 simultaneous POST requests for the same slot.
Only one should succeed. If more than one does, you have a bug.
This availability engine and race condition protection is
built into SlotKit — a production-ready booking SaaS
template for agencies. If you're building booking
functionality for clients, you don't have to solve this
from scratch every time.
Top comments (0)