The Bug Behind Double Bookings

Here's a booking function. It checks whether the room is free for the night, and if it is, it saves the booking.

async function book(roomId: string, night: string, guest: string) {
  const { rows } = await pool.query(
    "SELECT count(*)::int AS taken FROM bookings WHERE room_id = $1 AND night = $2",
    [roomId, night],
  );
  if (rows[0].taken > 0) return `${guest}: sorry, already booked`;

  await pool.query(
    "INSERT INTO bookings (room_id, night, guest) VALUES ($1, $2, $3)",
    [roomId, night, guest],
  );
  return `${guest}: confirmed`;
}

Every line is correct. It passes the tests you'd write for it. And if ten guests try to book the same room for the same night at the same moment, all ten are confirmed.

The bug isn't in any single line. It's in the gap between two of them. It's called a race condition, and this post covers why it happens, why your tests miss it, and three ways to fix it in Postgres.

What it costs

To the business, a double booking isn't a stack trace. It's a guest turned away at check-in, a refund, a support ticket and a bad review. It shows up on your busiest nights, because that's when requests overlap. The same bug sells one seat twice, applies one coupon twice, or takes an account below zero.

The tables

The examples use two tables:

CREATE TABLE rooms (
  id text PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE bookings (
  id bigserial PRIMARY KEY,
  room_id text NOT NULL REFERENCES rooms (id),
  night date NOT NULL,
  guest text NOT NULL,
  guests int NOT NULL DEFAULT 1
);

Concurrency, not parallelism

Two words get mixed up here. Concurrency means several tasks are in progress at once and take turns. Parallelism means several tasks are running at literally the same instant, on different cores or machines.

You don't need parallelism to get this bug. Each await is a point where one request pauses and another can run. So even one Node.js process on one core can interleave two bookings like this:

In production it's worse: you run several servers behind a load balancer, and requests really do arrive together.

The shape to look for: check, then act

This bug has a name: a race condition. Two requests race through the same check, and both win.

The general shape is check-then-act: read something, decide, then write based on what you read. Between the read and the write there's a gap. If anything changes the data inside that gap, the decision was based on stale information.

Once you know the shape, you'll see it everywhere:

• "If this email isn't registered, create the account."
• "If the balance covers it, deduct the amount."
• "If seats are left, sell one."
• "If the coupon hasn't been used, apply it."

Why your tests don't catch it

A test usually makes one request, waits for it, then checks the result. Nothing ever competes for the gap, so the race never happens.

To see it, make the requests overlap on purpose, against a real database. One detail matters more than you'd expect: warm up the connection pool first. Run against a fresh pool, opening the connections spaces the requests out, and only one booking gets through. With the pool warmed up, all ten are confirmed. In ten runs of each against Postgres 17, that held every time.

const guests = ["Asha", "Ravi", "Meera", "Karan", "Priya", "Arjun", "Neha", "Vikram", "Divya", "Rohan"];

// Open the connections first, so the bookings really overlap.
await Promise.all(guests.map(() => pool.query("SELECT 1")));

const results = await Promise.all(guests.map((guest) => book("room_101", "2026-12-31", guest)));
console.log(results); // ten "confirmed", one room

Keep a test like this for every write path where duplicates would hurt. And note what the cold pool shows: a race that depends on timing can pass your tests and still happen in production.

Fix 1: let the database say no

If the rule can be written as "this must be unique", stop checking in application code and let the database enforce it. Postgres checks and saves in one step, so there's no gap left:

ALTER TABLE bookings
  ADD CONSTRAINT one_booking_per_night UNIQUE (room_id, night);

Then just try the insert, and treat a unique violation as "already taken". Postgres reports it with error code 23505 (unique_violation):

async function book(roomId: string, night: string, guest: string) {
  try {
    await pool.query(
      "INSERT INTO bookings (room_id, night, guest) VALUES ($1, $2, $3)",
      [roomId, night, guest],
    );
    return `${guest}: confirmed`;
  } catch (err) {
    if ((err as { code?: string }).code === "23505") return `${guest}: sorry, just taken`;
    throw err;
  }
}

Run the ten-guest test again: one confirmation, nine "sorry, just taken", one row saved.

Two things worth knowing:

• You can skip the error entirely. ON CONFLICT DO NOTHING turns the clash into "no row inserted", which you check without a try/catch:

const { rowCount } = await pool.query(
  `INSERT INTO bookings (room_id, night, guest) VALUES ($1, $2, $3)
   ON CONFLICT (room_id, night) DO NOTHING`,
  [roomId, night, guest],
);
return rowCount === 1 ? `${guest}: confirmed` : `${guest}: sorry, just taken`;

• Inside a transaction, the error ends the transaction. If the insert runs between BEGIN and COMMIT, a 23505 aborts the whole transaction. Every statement after it fails with 25P02 until you roll back. So catch it, roll back, then answer the guest. ON CONFLICT DO NOTHING avoids this, because nothing fails.

This is the simplest fix, so start here whenever the rule fits.

Fix 2: lock the row

Not every rule can be written as "unique". Say the villa can host two small groups a night, but not three. That rule replaces fix 1, so there's no unique constraint on (room_id, night) here. A unique constraint can't count, so you're back to read, decide, save.

This time you make sure nobody else does the same at the same moment, by locking a row inside a transaction with SELECT … FOR UPDATE:

async function book(roomId: string, night: string, guest: string) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query("SELECT id FROM rooms WHERE id = $1 FOR UPDATE", [roomId]);

    const { rows } = await client.query(
      "SELECT count(*)::int AS groups FROM bookings WHERE room_id = $1 AND night = $2",
      [roomId, night],
    );
    if (rows[0].groups >= 2) {
      await client.query("ROLLBACK");
      return `${guest}: sorry, already booked`;
    }

    await client.query(
      "INSERT INTO bookings (room_id, night, guest) VALUES ($1, $2, $3)",
      [roomId, night, guest],
    );
    await client.query("COMMIT");
    return `${guest}: confirmed`;
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

Notice which row is locked: the room, not a booking. You can't lock a booking that doesn't exist yet. So you lock the parent row that every booking for that room has to go through.

When Ravi's request reaches the FOR UPDATE, it waits until Asha's transaction commits. Then its count sees her booking. That last step depends on Postgres's default isolation level, READ COMMITTED, where each statement sees the latest committed data. At REPEATABLE READ, the count still sees the database as it was when the transaction started. Run the same code at that level with ten guests and all ten get booked.

This works for almost any rule, but it has costs:

• Everyone waits in line. All bookings for that room queue behind the lock, so on a busy night requests pile up.
• Deadlocks. If one request locks room A then room B, and another locks B then A, each waits for the other forever. Postgres detects this and cancels one of them with error 40P01. Lock rows in a consistent order, for example by id, to avoid it.
• Keep the lock short. Never charge a card or call another service while holding it. Everyone else is stuck waiting on you.

Fix 3: check nothing changed (optimistic locking)

The same race happens with edits. Two staff members open Asha's booking at once. Meera changes it to two guests, Karan to four. Whoever saves last wins, and the other change disappears without an error. Nobody knows.

The fix is a version number. Remember the version when you read, and only update if it's still the same:

ALTER TABLE bookings ADD COLUMN version int NOT NULL DEFAULT 1;

async function changeGuests(bookingId: number, guests: number, versionRead: number) {
  const { rowCount } = await pool.query(
    "UPDATE bookings SET guests = $1, version = version + 1 WHERE id = $2 AND version = $3",
    [guests, bookingId, versionRead],
  );
  if (rowCount === 0) return "This booking just changed. Reload and try again.";
  return "Saved";
}

Meera saves first and the version moves to 2. Karan's update looks for version 1, matches nothing and changes 0 rows, so he's asked to reload. Nobody waits, and no change is lost silently.

The catch: whoever loses has to redo their work. That's fine when clashes are rare. If many people edit the same thing at once, they keep losing and retrying, and a lock (fix 2) is the better choice.

What about SERIALIZABLE?

Postgres's strictest isolation level catches this race on its own, with no lock and no constraint. Run the original check-then-insert inside a SERIALIZABLE transaction and Postgres lets one booking through. The others fail with error 40001 (serialization_failure). The catch: your code has to retry every transaction that fails that way. Isolation levels get their own video later in this series.

Which fix to use

1. Can the rule be written as "this must be unique"? Use a unique constraint.
2. Do you need to read, decide, then save? Lock the row, as long as traffic on that row isn't too high.
3. Is it an edit that rarely clashes? Use a version column.

When bookings span several nights

A unique constraint on (room_id, night) works when you store one row per night. If you store a stay as a date range instead, Postgres has exclusion constraints, which refuse any row that overlaps an existing one:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE stays (
  id bigserial PRIMARY KEY,
  room_id text NOT NULL,
  stay daterange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, stay WITH &&)
);

Two stays for the same room (=) whose ranges overlap (&&) can't both exist. The losing insert fails with error 23P01 (exclusion_violation), which you handle the same way as 23505. Back-to-back stays are fine, because a range like [2026-12-30, 2027-01-02) ends before the next one starts.

Retries: the same request, twice

There's one more way to get a duplicate, and no other guest is involved. A client times out and retries, a queue redelivers a message, or a background job runs again, and the same booking request arrives twice.

The fix is an idempotency key: a unique id the client generates once per booking attempt and sends with every retry. Store it under a unique constraint, and a repeat returns the first result instead of booking again:

ALTER TABLE bookings ADD COLUMN request_id uuid UNIQUE;

async function book(roomId: string, night: string, guest: string, requestId: string) {
  const inserted = await pool.query(
    `INSERT INTO bookings (room_id, night, guest, request_id) VALUES ($1, $2, $3, $4)
     ON CONFLICT DO NOTHING
     RETURNING id`,
    [roomId, night, guest, requestId],
  );
  if (inserted.rowCount === 1) return `${guest}: confirmed`;

  // Nothing inserted: either this request was seen before, or the room is taken.
  const { rows } = await pool.query("SELECT guest FROM bookings WHERE request_id = $1", [requestId]);
  if (rows.length === 1) return `${rows[0].guest}: confirmed`;
  return `${guest}: sorry, just taken`;
}

The ON CONFLICT has no target on purpose. With fix 1's unique constraint also in place, a repeated request clashes with both constraints. With ON CONFLICT (request_id), retries arriving at the same moment fail with 23505 from the other constraint. Without a target, any clash means "nothing inserted", and the lookup tells the two cases apart.

AI agents, background jobs and workers act on the same records your users do, often in parallel and often with retries. Every fix here applies to them too.

Start with the database

Most check-then-act code can be replaced by a rule the database enforces: a unique constraint, an exclusion constraint, a conditional update. Reach for a lock only when the rule can't be written that way, and test with requests that really overlap.

I built this example and ran all three fixes with ten guests in the video:

Originally published on the Tech Swamy blog. If your startup is growing fast and bugs like this are starting to show up (double bookings, lost updates, things that only break on your busiest days), that's the work we do at Tech Swamy.