Two of your users open the same page at the same moment and pick the last free desk. Both requests read “available”. Both write. One of them finds out on Tuesday, by travelling in and finding somebody at the desk.
Your service checked. The check was correct when it ran, and it ran before either request had written anything, which is the whole problem: a check before a write is a read, and a read tells you about the past.
Two guarantees, not one rule written twice
The advice you will hear is that a unique constraint duplicates the validation you already have. That is true about the sentence and false about what the two things guarantee.
| the service check | the constraint | |
|---|---|---|
| answers | was this free a moment ago | is this true now |
| under a race | wrong | right |
| failure message | names the desk and the date | a driver exception |
| when it runs | before the write | at the write |
| what it costs | one query | nothing you did not already pay |
Remove the check and every conflict, including the ordinary one where somebody took the desk an hour ago, arrives as a constraint violation to be translated. Remove the constraint and the rule is true only when nobody is in a hurry.
The part that decides whether this works
If the two produce different failures you have not added safety, you have swapped a double booking for a 500 and a support ticket.
So the adapter that owns the write translates the violation into the same failure
the service raises. In the reference implementation the service throws
DeskAlreadyBookedException after its check, and the persistence adapter catches
the duplicate key and throws the same thing:
try {
jdbc.sql("insert into bookings ...").update();
}
catch (DuplicateKeyException ex) {
throw new DeskAlreadyBookedException(booking.deskId(), booking.date());
}
The API answers 409 either way. The client cannot tell which guard fired, and
should not care: it asked for a desk that was gone.
The test that keeps this honest asserts the database’s answer, not the service’s. Two rules that can disagree need something that fails when they do.
The detail that will bite you
Write the obvious constraint and cancelling breaks.
-- wrong: a cancelled booking still holds the desk forever
create unique index on bookings (desk_id, booked_on);
-- right: only a confirmed booking holds it
create unique index bookings_one_confirmed_per_desk_per_day
on bookings (desk_id, booked_on)
where status = 'CONFIRMED';
A member cancels at nine and rebooks the same desk at ten, which is a thing people do constantly, and the plain constraint makes it impossible. The partial index is not a clever optimisation, it is the difference between the rule you meant and the rule you wrote.
Where else this applies
The pattern is not about databases. It is about a check that reads state and a
guard that owns it, and the pairs are everywhere once you look:
a validation and a NOT NULL, a permission check and a row-level policy, a
uniqueness check and an index, a balance check and a conditional update.
In each case the first exists for the message and the common path, and the second is what makes the sentence true. It is the same split as a deterministic core with reasoning at the edges, applied to a race rather than to a judgement: one half can be wrong about what happens next, and the other half cannot.
If your team is arguing about whether a constraint duplicates a validation, that’s the work I do.