---
title: "Überbuchung ist ein Datenbankproblem"
description: "Zeilen zu zählen, bevor man eine einfügt, ist keine Kapazitätsprüfung. Wie stattdessen ein Unique Index, eine Constraint-Verletzung und ein NULL die Arbeit übernommen haben."
url: https://riteshkc.com.np/de/blog/overbooking-is-a-database-problem
source: https://riteshkc.com.np/de/blog/overbooking-is-a-database-problem.md
updated: 2026-07-29
site: "Ritesh KC"
---
# Überbuchung ist ein Datenbankproblem
Zeilen zu zählen, bevor man eine einfügt, ist keine Kapazitätsprüfung. Wie stattdessen ein Unique Index, eine Constraint-Verletzung und ein NULL die Arbeit übernommen haben.
- Published: 2026-07-29
- Tags: PostgreSQL, Concurrency, Prisma
- Author: Ritesh KC (https://riteshkc.com.np)
I was building a consultation booking flow. Three seats per time slot, seven slots a day, a public wizard anyone can walk through without an account. Not a hard feature. I wrote the obvious version in about twenty minutes:
```ts
const booked = await prisma.appointment.count({
where: { slotDate, slotTime, status: { not: 'CANCELLED' } },
});
if (booked >= CAPACITY) throw new ConflictException('Slot is full');
return prisma.appointment.create({ data });
```
Read it. It looks right. It reads like the sentence you'd say out loud: *check if the slot is full, and if it isn't, book it.*
It's wrong, and it's wrong in a way that no amount of testing on my laptop was ever going to show me.
## The gap between the count and the insert
Postgres runs at Read Committed by default. Prisma doesn't change that. So each of those two statements sees a snapshot of the database taken at the moment that statement began, not at the moment the request began.
Two people click "book" on the last remaining seat, eighty milliseconds apart. Both counts run before either insert commits. Both see two rows. Both conclude there's one seat left. Both insert.
Now you have four bookings in a three-seat slot, two of them belonging to people who each think they got the last one.
The window is small. That's precisely what makes it nasty. It will not show up in development, it will not show up in a smoke test, and when it does show up you'll get a support email that reads "we had four people on a call meant for three" and no error anywhere in your logs. Nothing failed. The code did exactly what it said.
I want to be honest about how I found this, because the version where I discovered it in production would make a better story and it isn't what happened. I found it while writing the *availability* endpoint: the one that tells the wizard which slots to grey out. I was writing a `groupBy` to count bookings per slot, and it occurred to me that I was about to use the same count for two completely different jobs: deciding what to show a user, and deciding whether a write is legal.
Those are not the same job. One of them is allowed to be a little stale. The other one absolutely is not.
## The fixes I didn't take
**Serializable isolation.** This does work. Postgres would detect the conflict and abort one of the transactions. But it raises the isolation level for a whole transaction to solve one constraint, and it hands you serialization failures that you have to catch and retry anyway. You end up writing retry logic regardless, so you may as well write retry logic against something narrower.
**`SELECT ... FOR UPDATE` on the slot.** There's no slot row to lock. Slots aren't entities in this schema; they're a date plus a label from a constant array. I could have created a `Slot` table purely to have something to lock, and then every booking serializes on that row. That's a table whose only purpose is to be a mutex. I've built that before. It's fine until someone adds a second reason to touch the table.
**Advisory locks.** `pg_advisory_xact_lock(hashtext(...))` on the slot key. Genuinely a good option, and I'd use it if the constraint were more complicated than "at most N rows." It's also invisible: nothing in the schema tells the next person the lock exists. Delete one line and the guarantee is gone with no error, no failing test, nothing.
That last point is what pushed me. I wanted the rule to live somewhere it couldn't be casually removed.
## Give each seat a name
The move is to stop thinking about *how many* bookings exist and start thinking about *which seat* each booking occupies.
A slot with capacity 3 has seats 0, 1, and 2. A booking doesn't increment a counter: it claims a specific seat. And "one booking per seat" is a thing a database can enforce on its own:
```prisma
model Appointment {
slotDate DateTime @map("slot_date") @db.Date
slotTime String @map("slot_time")
// Capacity N occupies seats 0..N-1. Set to NULL on cancel to release the
// seat: Postgres treats NULLs as distinct in a unique index, so cancelled
// rows never block a rebooking.
seatIndex Int? @map("seat_index")
@@unique([slotDate, slotTime, seatIndex])
@@index([slotDate, slotTime])
}
```
That's the whole guard. Not a service method: an index.
The capacity check is now structural. Two concurrent requests can both decide seat 1 is free; only one of them gets to commit a row that says so. The other one gets a unique-constraint violation, which Prisma surfaces as error code `P2002`.
## P2002 is not an error here
This is the part that felt wrong to write and turned out to be the whole idea.
`P2002` normally means something went wrong: you tried to register an email that already exists, you double-submitted a form. Here it means something completely mundane: *that seat is taken, try the next one.* It's not an exception in the "exceptional" sense. It's the answer to a question.
So the booking path is a loop over the seats, and a constraint violation is how you advance it:
```ts
/**
* Claims the first free seat in the slot. A plain count-then-create
* overbooks under Read Committed (two racers both see capacity-1) so the
* unique index on (slotDate, slotTime, seatIndex) is the actual guard and
* P2002 just means "seat taken, try the next one".
*
* Each attempt is its own transaction: Postgres aborts a transaction on the
* first failed statement, so retrying a seat inside one would only produce
* "current transaction is aborted" on every subsequent create.
*/
private async createWithFreeSeat(
data: Omit,
actorId: string | null,
) {
for (let seat = 0; seat < APPOINTMENT_SLOT_CAPACITY; seat++) {
try {
return await this.prisma.$transaction(async (tx) => {
const created = await tx.appointment.create({
data: { ...data, seatIndex: seat },
});
await this.audit.record(tx, {
actorId,
action: 'appointment.create',
entityType: 'Appointment',
entityId: created.id,
});
return created;
});
} catch (err) {
const isSeatTaken =
err instanceof Prisma.PrismaClientKnownRequestError &&
err.code === 'P2002';
if (!isSeatTaken) throw err;
}
}
throw new ConflictException(
'That time slot is fully booked. Please pick another time.',
);
}
```
No lock. No isolation-level change. No counting.
The loop runs at most `CAPACITY` times, and it only iterates when a seat is genuinely occupied, which, with three seats, means the pathological case is three round trips. If capacity were 500 this would be a bad design and I'd go back to advisory locks. It's 3. I'll take the three round trips.
## The bug inside the fix
The first version of that loop had one transaction wrapping the whole thing, with the retry inside it. It looked tidier. It also failed on every seat after the first, with an error I hadn't seen before:
```text
current transaction is aborted, commands ignored until end of transaction block
```
Postgres aborts a transaction on the *first* failed statement. Once seat 0 raises a unique violation, that transaction is dead: every subsequent statement inside it fails with the same message regardless of what it is. Catching the error in application code doesn't resurrect anything. The connection is sitting in a poisoned state waiting for a `ROLLBACK`.
(Savepoints would let you recover: `SAVEPOINT` before each attempt, `ROLLBACK TO` on failure. That works and is what I'd reach for if the surrounding transaction had to stay open for other reasons. Here it didn't, so a fresh transaction per attempt is simpler and I don't have to explain savepoint semantics to whoever reads this next.)
The audit write is inside the transaction on purpose. If the appointment row commits, the audit row commits with it, or neither does. An audit log that can silently miss entries during a retry loop is worse than no audit log, because you'll trust it.
The thing I'd tell my past self: when a retry loop lives inside a transaction, the transaction is usually the thing that needs to move, not the loop.
## Cancellation, and a NULL doing real work
Then the obvious follow-up question: what happens when someone cancels?
A cancelled booking is still a row. If that row keeps `seatIndex = 1`, seat 1 is occupied forever and the slot quietly shrinks to two seats. Deleting the row is not an option: cancellations are history, and staff need to see them.
The answer is a single field assignment:
```ts
// Cancelling frees the seat; the unique index treats NULL seats as
// distinct, so the slot immediately reopens.
if (dto.status === AppointmentStatus.CANCELLED) {
data.seatIndex = null;
data.cancelledAt = new Date();
}
```
In a Postgres unique index, `NULL` is not equal to `NULL`. Two rows with a NULL in the indexed column do not collide. So every cancelled booking for a slot can carry `seatIndex = null`, all of them coexisting happily, none of them blocking a new booking from claiming that seat number.
The free list is the absence of a value. No status column in the index, no partial index, no cleanup job. Cancel a booking and the seat is back in circulation on the next insert.
I like this more than I probably should. It's also the part I'd flag hardest in review, because it depends on a piece of SQL semantics that reads as a footnote until it's load-bearing. Postgres 15 added `UNIQUE NULLS NOT DISTINCT`: opt-in, so the default behaviour is unchanged, but it means the guarantee this design rests on is now a thing someone can switch off in a migration. The comment sitting directly above the constraint is not decoration.
## Two counts, two jobs
The availability endpoint still counts rows. That's fine, because it answers a different question:
```ts
const grouped = await this.prisma.appointment.groupBy({
by: ['slotTime'],
where: { slotDate, status: { notIn: RELEASED_APPOINTMENT_STATUSES } },
_count: { _all: true },
});
```
This drives the wizard's slot picker, which times render as available, which render as full. It's allowed to be a few hundred milliseconds stale, because being stale here costs a user one failed booking attempt and a clear error message. Being stale in the write path costs you a fourth person on a three-person call.
Worth noting the two views agree only because cancelled rows are excluded from the count *and* carry a NULL seat. Those two facts have to stay in sync. `COMPLETED` and `NO_SHOW` keep their seat and keep being counted, which is correct: a no-show still consumed the slot.
## What I'd carry to the next one
**Constraints beat checks.** A check is a statement about a moment. A constraint is a statement about the data. If the rule matters, put it where it survives someone rewriting your service method.
**Name the resource you're allocating.** Almost every "N at a time" problem gets easier when you stop counting the things and start identifying the slots they go into. Seats, ports, shard ids, worker lanes. Once each one has a name, uniqueness does the coordinating.
**Some errors are answers.** Treating `P2002` as control flow felt like a hack for about a day. It isn't. The database is telling you the current state of the world faster and more reliably than any query you could have written to ask.
The version of this code I'm happiest about is the one that's mostly not code. Twelve lines of loop, one line of schema, and a constraint the database enforces whether or not the next person to touch this file understands why it's there.
That last part is the only kind of correctness that survives contact with a team.
---
*Backend on this is NestJS, Prisma, and PostgreSQL 15: the [Gradsy](https://riteshkc.com.np/work/gradsy) build. Slot capacity is 3, seven slots a day, and yes, all of it is wall-clock time in a timezone that's 45 minutes off the hour. That's a different post.*
## Related work
- [Gradsy](https://riteshkc.com.np/de/work/gradsy): Der gesamte Betrieb einer Auslandsstudien-Beratung über zwei Repos. Studierende bewerben sich, Berater bewegen Bewerbungen durch Zustände, Admins machen den Rest. Viel davon war Nebenläufigkeit.