Skip to content
← All articles

Published on 6 min read

No double bookings: a transactional lock and concurrency tests

How to stop two bookings on one slot: why SELECT followed by INSERT is not enough, the pg_advisory_xact_lock inside Prisma, and tests against real Postgres.

Related project: Salon booking app

Two clients open the same salon's app and tap the same 10:00 slot at the same moment. Both apps show the slot as free, and both send the request. If the server checks that the slot is free and then writes the booking, both requests can pass the check together, and the salon ends up with two appointments at the same time with the same stylist. I am building a booking app for salons, with a multi-tenant NestJS API and an Expo client, and this is one of the problems I have had to get right with the most care.

Why SELECT followed by INSERT is not enough

The naive code looks for a conflicting appointment and, if none exists, creates the new one. It reads correctly, but PostgreSQL defaults to the READ COMMITTED isolation level. Transaction A reads "no conflict", transaction B reads "no conflict" before A commits, and both insert. The check and the write are two separate statements, and nothing ties them together.

SERIALIZABLE with retries would also work. I preferred an explicit lock on the stylist, because what needs protecting is one stylist and one time range, and a lock is easier to reason about than a general retry loop.

The lock on the stylist, inside the transaction

The lock is a transaction-scoped advisory lock: pg_advisory_xact_lock is released at commit or rollback. Its key is a hash of the stylist's ID. Simplified, the code looks like this:

return await this.prisma.$transaction(async (tx) => {
  let chosen: string | null = null;
  for (const staffId of eligible) {
    await tx.$executeRaw`SELECT pg_advisory_xact_lock(hashtext(${staffId}))`;
    const conflict = await tx.appointment.findFirst({
      where: { salonId, staffId, status: 'CONFIRMED', start: { lt: end }, end: { gt: start } },
      select: { id: true },
    });
    if (!conflict) { chosen = staffId; break; }
  }
  if (!chosen) {
    throw new ConflictException({ code: 'SLOT_TAKEN', message: 'This time has just been taken.' });
  }
  return tx.appointment.create({ data: { salonId, staffId: chosen, start, end /* ... */ } });
});

The order matters. The lock must be taken before the check, and in the same transaction. The second request waits on the lock until the first commits. Then it runs its SELECT, which is a new statement, so under READ COMMITTED it sees the committed booking and finds the conflict. If the lock came after the SELECT, the second request would see the same "no conflict" as before.

Where the lock is taken

The lock appears in three paths: a client booking, an appointment created from the admin calendar, and an existing appointment moved in the admin calendar. It is not needed for cancellations, which free a slot. Status transitions cannot re-confirm a cancelled appointment, because the code rejects CONFIRMED as a target state. The database has no exclusion constraint on time ranges: I checked the migrations and found none. So the guarantee lives in the code, and every new path that writes appointments has to take the same lock. That is the rule I care most about enforcing.

When the client does not pick a stylist (ANY), the system tries the eligible stylists in turn and takes the first free one, locking them one at a time. I would change something here, and I explain it in the last section.

Repeated requests: idempotency

A double tap or a network retry must not create two bookings. The app generates an Idempotency-Key for each attempt and reuses it on every retry. The backend has a unique index on (salonId, idempotencyKey), and if the key already exists it returns the existing appointment. The subtle case is the race: two requests with the same key arrive together, the second waits on the lock and finds the slot taken by the first, and in its catch block it looks up the key. If it finds the winner's appointment, it returns that instead of an error. The same applies when the loser hits the P2002 error from the unique index. On the client, the test covering this case checks that retries reuse the same key, and that a SLOT_TAKEN sends the user back to the time-selection step.

Concurrency tests against a real Postgres

The integration tests start a PostgreSQL 18 container with Testcontainers and apply the migrations with prisma migrate deploy, so the tests run against the same schema as production. One case fires parallel requests at the same slot:

const results = await Promise.all(
  Array.from({ length: 8 }, (_, i) =>
    http().post('/v1/appointments').set(bearer(tokens[i % 2])).send({
      serviceIds: [seed.serviceTaglio], staffId: seed.staffA, start,
    }),
  ),
);
expect(results.filter((r) => r.status === 201)).toHaveLength(1);
expect(results.filter((r) => r.status === 409 && r.body.code === 'SLOT_TAKEN')).toHaveLength(7);

After the responses, the test counts the confirmed rows for that slot in the database: there must be exactly one. A parallel test does not prove the absence of race conditions, since it only exercises the interleavings that happen to occur. Its value is as a regression guard: if someone removes the lock, requests really do overlap and the test fails.

A typed client generated from the spec

The mobile app does not write its API types by hand. The backend publishes its OpenAPI document through SwaggerModule, and the client generates schema.d.ts with openapi-typescript, plus a small helper that links path, method, body and response. Rename a path on the server and the app fails to compile. The limits are real, though. A DTO without @ApiProperty becomes Record<string, unknown>, and a response without a schema becomes unknown. In the booking wrapper the body is cast to never, which switches off payload checking exactly where it matters most. The types cover the path and the method, not yet every field.

What I would do differently

First, I would sort the stylist IDs before locking them. The candidate list comes from a findMany with no orderBy, so the order is not guaranteed. Two transactions locking the same stylists in different orders can deadlock: Postgres aborts one of them, and the client does not get a proper 409. I have no test that reproduces this, but a deterministic order costs one line.

Second, I would add an exclusion constraint in Postgres, using the btree_gist extension, on confirmed appointments. It is defence in depth: even a future path that forgets the lock could not overlap two appointments.

Third, I would remove the cast to never on the creation body and add @ApiProperty to the DTOs that degrade today. The client would be typed for the payload too.

The project is still in development and does not have a public version to try yet.

  • PostgreSQL
  • concurrency
  • Prisma
  • NestJS
  • Testcontainers

Have a similar project? Let's talk

If you're facing a similar challenge, get in touch and I'll tell you how I would approach it.

Let's talk