Closing the gap: safe seat booking with row locks

When I put BookMySeat together, the goal was not another CRUD tutorial. I wanted something you can feel: two browser tabs, one seat, and a backend honest enough to show exactly where concurrency hurts.

The lesson is simple: the seat row is the source of truth, so the booking decision must happen while that row is locked.

The race condition you can feel

The unsafe path is intentionally familiar. It reads a seat, waits long enough for another request to catch up, then writes the booking result.

// Shape of the unsafe handler.
const seat = await Seat.findOne({
  where: { id: seatId, showId },
})

if (!seat || seat.status !== "available") {
  return conflict("Seat is already booked")
}

await sleep(demoDelayMs)

await seat.update({ status: "booked" })
await Booking.create({ seatId, userId })

That is textbook check-then-act. Two in-flight requests can both observe available, sleep through the same window, and each run the update plus insert.

What breaks

The demo does not enforce a unique constraint on bookings.seatId, because hiding the failure would make the lesson weaker. After a stress run, the API can report duplicated booking rows for the same seat.

SELECT "seatId", COUNT(*) AS "bookingCount"
FROM bookings
GROUP BY "seatId"
HAVING COUNT(*) > 1;

The page surfaces those anomalies after unsafe stress runs, so you do not have to tail SQL logs to prove the point.

A transaction is necessary, but not enough

People sometimes hear "use a transaction" and assume the race vanishes. A transaction gives you atomicity and a place to roll back, but a plain read can still be a non-locking snapshot depending on isolation level.

So the important question is not just "transaction yes or no?" It is: which rows represent the decision, and how do you read them right before you mutate?

The safe path: lock before deciding

The safe path uses a single Sequelize-managed transaction. Inside it, the handler loads the requested seats with lock: t.LOCK.UPDATE, which maps to PostgreSQL row locking.

await sequelize.transaction(async (t) => {
  const seats = await Seat.findAll({
    where: {
      id: seatIds,
      showId,
    },
    order: [["id", "ASC"]],
    transaction: t,
    lock: t.LOCK.UPDATE,
  })

  if (seats.length !== seatIds.length) {
    throw new BookingConflict("Some seats were not found")
  }

  const unavailable = seats.find((seat) => seat.status !== "available")

  if (unavailable) {
    throw new BookingConflict("One or more seats are already booked")
  }

  await Promise.all(
    seats.map((seat) =>
      seat.update({ status: "booked" }, { transaction: t })
    )
  )

  await Booking.bulkCreate(
    seats.map((seat) => ({ seatId: seat.id, userId })),
    { transaction: t }
  )
})

What PostgreSQL is doing

At the database level, the important part looks like this:

SELECT *
FROM seats
WHERE "showId" = $1
  AND id IN ($2, $3, $4)
ORDER BY id ASC
FOR UPDATE;

If another request is trying to book the same seat, it has to wait. When it resumes, it re-checks the seat status inside its own transaction and returns a conflict instead of stacking another booking row.

{
  "status": 409,
  "message": "One or more seats are already booked"
}

Keep the critical section tight

Row locks trade raw throughput for correctness on a finite resource. That is the right trade when the resource is a real seat, but the lock should be held for the shortest useful time.

• Lock only the rows that matter for the booking decision.
• Re-check availability while the lock is held.
• Update the seats and create booking rows in the same transaction.
• Avoid external APIs, email, PDFs, payment redirects, or user prompts inside the transaction.

For a full auditorium, contention is usually per seat. That scales much better than one global mutex for the whole show.

Multi-seat carts need lock ordering

If a user can book four seats together, sort the seat IDs before locking them. Stable ordering keeps two transactions from locking the same rows in opposite directions.

const seatIds = requestedSeatIds
  .map(Number)
  .sort((a, b) => a - b)

The rule is boring in the best possible way: every request asks for locks in the same order.

Add a database backstop

The app should try to do the right thing, but the database should still refuse impossible state. In a production version, I would add a uniqueness rule around the booking target.

ALTER TABLE bookings
ADD CONSTRAINT bookings_unique_seat
UNIQUE ("seatId");

If the same physical seat can appear across multiple shows or times, make the constraint match that real identity instead.

ALTER TABLE bookings
ADD CONSTRAINT bookings_unique_show_seat
UNIQUE ("showId", "seatId");

That gives the system a final guardrail even if a future code path forgets the locking story.

Try it locally

Clone the repo, create a Postgres database, copy .env.example into apps/server/.env, seed the database, and start the app.

git clone https://github.com/hemanth5544/BookMySeat
cd BookMySeat
npm install
npm run db:seed -w server
npm run dev

Then open the UI and run the unsafe stress control against one available seat. Refresh the page and watch the anomalies panel light up. Reset the demo, switch to the safe path, and the same kind of contention should resolve as conflicts instead of duplicated bookings.

What to remember

BookMySeat is small on purpose, but the rule generalizes well:

1. Find the row that represents the scarce thing.
2. Read that row inside a transaction with a lock.
3. Re-check the business condition after the lock is acquired.
4. Mutate the state and write related records before commit.
5. Keep a database constraint as the last line of defense.

The implementation in apps/server/src/book.ts is the ground truth next to this narrative.