Designing a Reservation and Booking System: Inventory Holds, Double-Booking Prevention, and Concurrency Control for Time-Slotted Resources

Most teams realize too late how hard a booking system actually is. The first version ships cleanly. Then a double-booking hits production during a promotional spike, or a user completes payment only to find the slot was taken 200ms earlier, or a timezone edge case corrupts availability for an entire day. These failures are not random. They come from skipping specific design decisions that seem unlikely to matter until they do.

This article covers the full design surface: time-slot modeling, concurrency control strategies, short-lived holds, waitlist promotion, timezone correctness, and overbooking compensation. The patterns apply whether you are building a medical scheduling tool, a SaaS meeting room system, a restaurant reservation app, or a rental marketplace.

Time-Slot Data Modeling

The first architectural choice is how you represent availability. Three models exist in practice.

Slot-based: Pre-generate every bookable time unit as a row. A hair salon with 10-minute slots, open 9am-6pm, 5 days a week generates 270 rows per week per resource. This is fine at scale. A million resources would generate 270M rows weekly, which is manageable with proper indexing and partitioning. Query complexity stays low because availability is always a simple WHERE status = 'available' lookup.

Window-based: Store availability as open intervals (start, end) and derive slots at query time. More compact storage, but availability queries become interval arithmetic under concurrent load. Correctness requires careful overlapping interval detection, which is harder to index efficiently.

Rule-based: Store recurrence rules (RRULE from RFC 5545) and expand to slots at runtime. Maximum flexibility for complex schedules, but under booking load you should always expand rules to slots at write time, not at query time. Evaluating rules at query time under concurrency is a reliability liability.

For most systems, start with slot-based. The storage cost is predictable, indexes are straightforward, and adding concurrency controls is simpler when availability is a row with a primary key.

A minimal slot schema:

// database schema (Postgres)
// CREATE TABLE time_slots (
//   id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
//   resource_id   UUID NOT NULL REFERENCES resources(id),
//   starts_at     TIMESTAMPTZ NOT NULL,
//   ends_at       TIMESTAMPTZ NOT NULL,
//   status        TEXT NOT NULL DEFAULT 'available',  -- 'available' | 'held' | 'booked'
//   version       INTEGER NOT NULL DEFAULT 0,
//   held_by       UUID REFERENCES holds(id),
//   held_until    TIMESTAMPTZ,
//   booking_id    UUID REFERENCES bookings(id),
//   CONSTRAINT no_overlap UNIQUE (resource_id, starts_at)
// );

type SlotStatus = "available" | "held" | "booked";

interface TimeSlot {
  id: string;
  resourceId: string;
  startsAt: Date;
  endsAt: Date;
  status: SlotStatus;
  version: number;
  heldBy: string | null;
  heldUntil: Date | null;
  bookingId: string | null;
}

The version column enables optimistic locking. The UNIQUE (resource_id, starts_at) constraint is the last line of defense against double-booking regardless of which locking strategy you use.

The Core Concurrency Problem

Two users load availability at the same time. Both see slot A as available. Both initiate a booking. At the database level, two concurrent UPDATE time_slots SET status = 'booked' statements race. Without concurrency control, both succeed and you have a double-booking.

Three strategies address this, each with different tradeoffs.

Pessimistic Locking with SELECT FOR UPDATE

Lock the row at read time. No other transaction can modify it until you commit or roll back.

import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function bookSlotPessimistic(
  slotId: string,
  userId: string,
  client = pool
): Promise<{ success: boolean; bookingId?: string; error?: string }> {
  const conn = await client.connect();

  try {
    await conn.query("BEGIN");

    // Lock the row exclusively. Any concurrent transaction trying to lock the
    // same row will block until this transaction completes.
    const lockResult = await conn.query<TimeSlot>(
      `SELECT id, status, version
       FROM time_slots
       WHERE id = $1
       FOR UPDATE`,
      [slotId]
    );

    if (lockResult.rows.length === 0) {
      await conn.query("ROLLBACK");
      return { success: false, error: "Slot not found" };
    }

    const slot = lockResult.rows[0];

    if (slot.status !== "available") {
      await conn.query("ROLLBACK");
      return { success: false, error: "Slot no longer available" };
    }

    const bookingId = crypto.randomUUID();

    await conn.query(
      `INSERT INTO bookings (id, slot_id, user_id, status, created_at)
       VALUES ($1, $2, $3, 'confirmed', NOW())`,
      [bookingId, slotId, userId]
    );

    await conn.query(
      `UPDATE time_slots
       SET status = 'booked', booking_id = $1, version = version + 1
       WHERE id = $2`,
      [bookingId, slotId]
    );

    await conn.query("COMMIT");
    return { success: true, bookingId };
  } catch (err) {
    await conn.query("ROLLBACK");
    throw err;
  } finally {
    conn.release();
  }
}

Pessimistic locking is correct and simple to reason about. The cost is throughput: every booking attempt serializes at the row level. Under low contention (most resources are booked once or twice concurrently) this is fine. Under high contention (limited-release tickets, flash sales) the lock queue grows and you will see timeout errors.

Use SKIP LOCKED as a variant when you want to fail fast rather than queue:

// Replace FOR UPDATE with FOR UPDATE SKIP LOCKED
// The transaction immediately returns 0 rows if another transaction holds the lock.
// Client retries on a different slot or returns "no availability" immediately.
const lockResult = await conn.query(
  `SELECT id, status FROM time_slots
   WHERE id = $1 AND status = 'available'
   FOR UPDATE SKIP LOCKED`,
  [slotId]
);

Optimistic Locking with Version Columns

Read without locking. Attempt the update only if the version column still matches what you read. If another transaction committed first, the update affects 0 rows and you retry.

async function bookSlotOptimistic(
  slotId: string,
  userId: string,
  expectedVersion: number
): Promise<{ success: boolean; bookingId?: string; error?: string }> {
  const conn = await pool.connect();

  try {
    await conn.query("BEGIN");

    // Update only succeeds if status and version still match what the client read.
    // If a concurrent transaction already modified this row, version will not match
    // and rowCount will be 0.
    const updateResult = await conn.query(
      `UPDATE time_slots
       SET status = 'booked', version = version + 1
       WHERE id = $1
         AND status = 'available'
         AND version = $2
       RETURNING id`,
      [slotId, expectedVersion]
    );

    if (updateResult.rowCount === 0) {
      await conn.query("ROLLBACK");
      return { success: false, error: "Slot was modified concurrently, retry" };
    }

    const bookingId = crypto.randomUUID();

    await conn.query(
      `INSERT INTO bookings (id, slot_id, user_id, status, created_at)
       VALUES ($1, $2, $3, 'confirmed', NOW())`,
      [bookingId, slotId, userId]
    );

    await conn.query(
      `UPDATE time_slots SET booking_id = $1 WHERE id = $2`,
      [bookingId, slotId]
    );

    await conn.query("COMMIT");
    return { success: true, bookingId };
  } catch (err) {
    await conn.query("ROLLBACK");
    throw err;
  } finally {
    conn.release();
  }
}

Optimistic locking scales better under low contention because reads do not block. Under high contention, retry storms can degrade user experience. The retry loop needs exponential backoff with jitter, and you need to decide how many retries are reasonable before returning a failure to the user.

The combination that works well in practice: optimistic locking as the primary path, with the UNIQUE (resource_id, starts_at) constraint as a hard backstop. If an optimistic update somehow races through (e.g., a bug in the version check), the unique constraint at the database level will reject the second insert with a constraint violation rather than silently double-booking.

Serializable Isolation

If you set the transaction isolation level to SERIALIZABLE, the database detects serialization anomalies and aborts conflicting transactions automatically. No explicit locks needed.

await conn.query("BEGIN ISOLATION LEVEL SERIALIZABLE");
// ... read, write, commit ...
// Postgres will raise error code 40001 (serialization_failure) on conflict.
// Retry the entire transaction on 40001.

Serializable isolation is the most correct strategy but has the highest overhead. Postgres uses Serializable Snapshot Isolation (SSI), which is non-blocking in most cases, but the false-positive abort rate rises under write-heavy workloads. For booking systems with moderate concurrency, it is a solid choice. For high-throughput ticket sales, the abort and retry cost may become measurable.

Temporary Holds: The Hold-Then-Confirm Pattern

A user selects a slot, then goes through a payment flow that takes 30-120 seconds. The slot must not be available to other users during that window, but it also must not be permanently blocked if the user abandons the flow.

The hold is a first-class entity, not a side-effect. It deserves the same locking guarantees as a booking.

// CREATE TABLE holds (
//   id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
//   slot_id     UUID NOT NULL REFERENCES time_slots(id),
//   user_id     UUID NOT NULL,
//   expires_at  TIMESTAMPTZ NOT NULL,
//   status      TEXT NOT NULL DEFAULT 'active',  -- 'active' | 'converted' | 'expired'
//   created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
// );

interface Hold {
  id: string;
  slotId: string;
  userId: string;
  expiresAt: Date;
  status: "active" | "converted" | "expired";
}

const HOLD_TTL_SECONDS = 600; // 10 minutes, must exceed your P99 payment flow time

async function createHold(
  slotId: string,
  userId: string
): Promise<{ success: boolean; holdId?: string; expiresAt?: Date }> {
  const conn = await pool.connect();

  try {
    await conn.query("BEGIN");

    const lockResult = await conn.query(
      `SELECT id, status
       FROM time_slots
       WHERE id = $1 AND status = 'available'
       FOR UPDATE`,
      [slotId]
    );

    if (lockResult.rowCount === 0) {
      await conn.query("ROLLBACK");
      return { success: false };
    }

    const holdId = crypto.randomUUID();
    const expiresAt = new Date(Date.now() + HOLD_TTL_SECONDS * 1000);

    await conn.query(
      `INSERT INTO holds (id, slot_id, user_id, expires_at)
       VALUES ($1, $2, $3, $4)`,
      [holdId, slotId, userId, expiresAt]
    );

    await conn.query(
      `UPDATE time_slots
       SET status = 'held', held_by = $1, held_until = $2, version = version + 1
       WHERE id = $3`,
      [holdId, expiresAt, slotId]
    );

    await conn.query("COMMIT");
    return { success: true, holdId, expiresAt };
  } catch (err) {
    await conn.query("ROLLBACK");
    throw err;
  } finally {
    conn.release();
  }
}

// Called on successful payment completion
async function confirmHold(holdId: string, userId: string): Promise<boolean> {
  const conn = await pool.connect();

  try {
    await conn.query("BEGIN");

    const holdResult = await conn.query<Hold>(
      `SELECT id, slot_id, expires_at, status
       FROM holds
       WHERE id = $1 AND user_id = $2
       FOR UPDATE`,
      [holdId, userId]
    );

    if (holdResult.rowCount === 0) return false;

    const hold = holdResult.rows[0];

    if (hold.status !== "active" || new Date(hold.expiresAt) < new Date()) {
      await conn.query("ROLLBACK");
      return false;
    }

    const bookingId = crypto.randomUUID();

    await conn.query(
      `INSERT INTO bookings (id, slot_id, user_id, status, created_at)
       VALUES ($1, $2, $3, 'confirmed', NOW())`,
      [bookingId, hold.slotId, userId]
    );

    await conn.query(
      `UPDATE time_slots
       SET status = 'booked', held_by = NULL, held_until = NULL,
           booking_id = $1, version = version + 1
       WHERE id = $2`,
      [bookingId, hold.slotId]
    );

    await conn.query(
      `UPDATE holds SET status = 'converted' WHERE id = $1`,
      [holdId]
    );

    await conn.query("COMMIT");
    return true;
  } catch (err) {
    await conn.query("ROLLBACK");
    throw err;
  } finally {
    conn.release();
  }
}

Hold expiry must run as a background process, not synchronously on the booking path. A pg_cron job or a dedicated worker scans for expired holds every 30-60 seconds:

-- pg_cron job running every minute
SELECT cron.schedule(
  'expire-holds',
  '* * * * *',
  $$
    UPDATE time_slots
    SET status = 'available', held_by = NULL, held_until = NULL, version = version + 1
    WHERE status = 'held' AND held_until < NOW();

    UPDATE holds SET status = 'expired'
    WHERE status = 'active' AND expires_at < NOW();
  $$
);

Waitlist Management

When a slot is fully booked, some systems offer a waitlist. The naive implementation promotes the next person synchronously when a cancellation arrives. This creates two problems: the cancellation endpoint becomes slow (it now does booking logic), and if promotion fails (user no longer wants the slot), you are doing retry logic on the critical cancel path.

Better pattern: the cancellation marks the slot available, then enqueues a promotion job. The job runs asynchronously and is idempotent.

// CREATE TABLE waitlist_entries (
//   id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
//   slot_id         UUID NOT NULL REFERENCES time_slots(id),
//   user_id         UUID NOT NULL,
//   position        INTEGER NOT NULL,
//   status          TEXT NOT NULL DEFAULT 'waiting',
//   notified_at     TIMESTAMPTZ,
//   expires_at      TIMESTAMPTZ,  -- set when notified, null while waiting
//   created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
//   CONSTRAINT unique_waitlist_position UNIQUE (slot_id, position)
// );

const WAITLIST_RESPONSE_TTL_SECONDS = 3600; // 1 hour to respond after notification

async function promoteNextWaitlistEntry(slotId: string): Promise<void> {
  const conn = await pool.connect();

  try {
    await conn.query("BEGIN");

    // Check slot is actually available before promoting
    const slotResult = await conn.query(
      `SELECT id, status FROM time_slots WHERE id = $1 FOR UPDATE`,
      [slotId]
    );

    if (slotResult.rows[0]?.status !== "available") {
      await conn.query("ROLLBACK");
      return;
    }

    // Take the next position in fair FIFO order
    const entryResult = await conn.query(
      `SELECT id, user_id
       FROM waitlist_entries
       WHERE slot_id = $1 AND status = 'waiting'
       ORDER BY position ASC
       LIMIT 1
       FOR UPDATE SKIP LOCKED`,
      [slotId]
    );

    if (entryResult.rowCount === 0) {
      await conn.query("ROLLBACK");
      return;
    }

    const entry = entryResult.rows[0];
    const expiresAt = new Date(
      Date.now() + WAITLIST_RESPONSE_TTL_SECONDS * 1000
    );

    await conn.query(
      `UPDATE waitlist_entries
       SET status = 'notified', notified_at = NOW(), expires_at = $1
       WHERE id = $2`,
      [expiresAt, entry.id]
    );

    // Hold the slot for the notified user
    const holdId = crypto.randomUUID();

    await conn.query(
      `INSERT INTO holds (id, slot_id, user_id, expires_at)
       VALUES ($1, $2, $3, $4)`,
      [holdId, slotId, entry.userId, expiresAt]
    );

    await conn.query(
      `UPDATE time_slots
       SET status = 'held', held_by = $1, held_until = $2, version = version + 1
       WHERE id = $3`,
      [holdId, expiresAt, slotId]
    );

    await conn.query("COMMIT");

    // Notify user outside the transaction
    await sendWaitlistNotification(entry.userId, slotId, expiresAt);
  } catch (err) {
    await conn.query("ROLLBACK");
    throw err;
  } finally {
    conn.release();
  }
}

If the notified user does not confirm within expiresAt, the hold expiry job releases the slot and the promotion job runs again for the next position.

Timezone Handling

Three bugs appear repeatedly in production booking systems that skipped timezone design.

DST transitions: A slot booked at “9am” on a day that crosses a daylight saving boundary can appear at 8am or 10am depending on which side of the transition the query runs. Store everything as TIMESTAMPTZ (UTC-normalized). Never store as TIMESTAMP without timezone.

Resource timezone vs user timezone: The resource (a meeting room in Berlin) has a local timezone. The user (connecting from São Paulo) has a different one. Availability display should convert from resource-local to user-local. Booking storage should always be UTC.

All-day events: An all-day event is a date, not a timestamp. Store it as a DATE type, not as 2024-03-15 00:00:00 UTC. Treating all-day events as timestamps causes them to shift by timezone offset when displayed to users in other zones.

import { fromZonedTime, toZonedTime, format } from "date-fns-tz";

// Resource lives in Berlin. User is viewing from São Paulo.
function formatSlotForUser(
  slotStartsAt: Date,     // stored as UTC
  resourceTimezone: string,  // 'Europe/Berlin'
  userTimezone: string       // 'America/Sao_Paulo'
): { resourceLocal: string; userLocal: string } {
  return {
    resourceLocal: format(
      toZonedTime(slotStartsAt, resourceTimezone),
      "yyyy-MM-dd HH:mm zzz",
      { timeZone: resourceTimezone }
    ),
    userLocal: format(
      toZonedTime(slotStartsAt, userTimezone),
      "yyyy-MM-dd HH:mm zzz",
      { timeZone: userTimezone }
    ),
  };
}

// When creating slots from a resource's weekly schedule:
// Input is always resource-local time. Convert to UTC for storage.
function slotFromResourceLocalTime(
  dateStr: string,  // '2024-06-15'
  timeStr: string,  // '09:00'
  resourceTimezone: string
): Date {
  return fromZonedTime(`${dateStr}T${timeStr}:00`, resourceTimezone);
}

The rule: store in UTC, convert for display only. Never store display-format times. Never store without timezone awareness.

Overbooking Strategies

Some systems intentionally accept more bookings than capacity allows. Airlines do it because no-show rates are predictable. The strategy only works when the no-show model is accurate, the compensation flow is implemented before going live, and the business accepts the reputational cost of bumping.

interface OverbookingPolicy {
  resourceId: string;
  capacity: number;
  overbookingFactor: number;  // 1.1 = accept 10% over capacity
  compensationBudgetCents: number;
}

async function canAcceptBookingWithOverbooking(
  resourceId: string,
  slotStartsAt: Date,
  policy: OverbookingPolicy
): Promise<boolean> {
  const result = await pool.query(
    `SELECT COUNT(*) AS confirmed_count
     FROM bookings b
     JOIN time_slots s ON s.id = b.slot_id
     WHERE s.resource_id = $1
       AND s.starts_at = $2
       AND b.status = 'confirmed'`,
    [resourceId, slotStartsAt]
  );

  const confirmedCount = parseInt(result.rows[0].confirmed_count, 10);
  const maxAccepted = Math.floor(
    policy.capacity * policy.overbookingFactor
  );

  return confirmedCount < maxAccepted;
}

// Compensation when more users show up than capacity allows.
// Run at check-in time, not at booking time.
async function compensateOverbookedBooking(
  bookingId: string,
  compensationAmountCents: number
): Promise<void> {
  await pool.query("BEGIN");

  try {
    await pool.query(
      `UPDATE bookings
       SET status = 'compensated', compensation_amount_cents = $1,
           compensated_at = NOW()
       WHERE id = $2`,
      [compensationAmountCents, bookingId]
    );

    // Initiate refund via payment processor, send compensation offer email, etc.
    // These run outside the transaction.

    await pool.query("COMMIT");
  } catch (err) {
    await pool.query("ROLLBACK");
    throw err;
  }
}

If you implement overbooking: decide the policy before writing a line of code. The technical implementation is 20 lines; the business logic for which booking gets bumped and what the compensation is takes more thought and will vary by domain.

Idempotency on Booking Endpoints

Clients retry on network errors. Without idempotency, a retry after a successful but timed-out request creates a duplicate booking. The Idempotency-Key header pattern solves this at the API layer.

async function createBookingIdempotent(
  req: Request
): Promise<Response> {
  const idempotencyKey = req.headers.get("Idempotency-Key");

  if (!idempotencyKey) {
    return Response.json({ error: "Idempotency-Key header required" }, { status: 400 });
  }

  // Check if this key already produced a result
  const existing = await pool.query(
    `SELECT response_body, response_status
     FROM idempotency_keys
     WHERE key = $1 AND created_at > NOW() - INTERVAL '24 hours'`,
    [idempotencyKey]
  );

  if (existing.rowCount > 0) {
    // Return the cached response exactly as it was
    return Response.json(
      JSON.parse(existing.rows[0].response_body),
      { status: existing.rows[0].response_status }
    );
  }

  // Process the booking...
  const result = await processBooking(req);

  // Store the result keyed to the idempotency key
  await pool.query(
    `INSERT INTO idempotency_keys (key, response_body, response_status, created_at)
     VALUES ($1, $2, $3, NOW())
     ON CONFLICT (key) DO NOTHING`,
    [idempotencyKey, JSON.stringify(result.body), result.status]
  );

  return Response.json(result.body, { status: result.status });
}

Concurrency Strategy Tradeoffs

Strategy	Throughput	Correctness	Retry Complexity	Best For
Pessimistic (SELECT FOR UPDATE)	Medium	High	Low (no retry needed)	Low-to-medium concurrency, simple implementation
Pessimistic + SKIP LOCKED	High	High	Medium (client retries fast)	Queue-style processing, slot pools
Optimistic (version column)	High	High (with constraint backstop)	High (retry loop with backoff)	Low contention, read-heavy workloads
Serializable isolation	Medium	Highest	Medium (retry on 40001)	Complex multi-row correctness requirements
Application-level mutex (Redis SETNX)	High	Medium (requires fencing)	Medium	Distributed systems, cross-region locking
Queue-based serialization	Very High	High	Low (guaranteed ordering)	Flash sales, limited-release inventory

The practical default for most SaaS booking systems: pessimistic locking with SELECT FOR UPDATE and the unique constraint as a safety net. Simple to reason about, correct under all realistic contention levels, and only becomes a bottleneck when you are measuring lock wait times in monitoring.

Production Considerations

Slot generation: Pre-generate slots in advance (90 days, 6 months, whatever your booking horizon is). Run generation as a scheduled job, not on the read path. Index (resource_id, starts_at, status) to cover the availability query efficiently.

Hold expiry monitoring: Track the lag between held_until and the actual expiry job run time. If holds are expiring 5 minutes late during peak hours, your expiry job is falling behind. Alert on this: delayed hold release blocks availability for users.

Lock contention monitoring: In Postgres, pg_stat_activity and pg_locks expose active waits. A spike in lock wait time during a booking window often means your connection pool is too small or your transactions are too long. Keep booking transactions short: no payment calls, no email sends inside the transaction.

Booking confirmation emails: Send outside the transaction. Never call an external service (Stripe, SendGrid, Twilio) while holding a database row lock.

Slot capacity for multi-capacity resources: If a resource can accommodate N concurrent bookings (a room that fits 10 people, a van with 8 seats), track capacity and confirmed_count on the slot rather than treating status as binary. The locking patterns above apply to the count check, not just status.

Database partitioning: Partition time_slots by starts_at month. Older partitions become cold immediately after their time window closes, queries only scan the current and upcoming partitions, and purging old data is a partition drop rather than a delete scan.

Sequence consistency for waitlists: Assign position using SELECT COALESCE(MAX(position), 0) + 1 ... FOR UPDATE on the waitlist entries for that slot. Never use a sequence for waitlist position: sequences are non-transactional in Postgres and will produce gaps under rollback.

Correctness problems in booking systems are not subtle. The systems that fail double-booked users or stranded them mid-payment did so because edge cases seemed unlikely at design time. The holds expire. The waitlist doesn’t promote. The timezone shifts by one hour after DST. These are predictable failure modes. Build the handling before launch.