A unique constraint makes the database reject any row whose constrained value combination already exists. It is a durable duplicate-write guard: instead of trusting application timing, let the database enforce the invariant atomically. section 8.5
Preventing a double booking
For a booking endpoint, the server derives a deterministic idempotency key from startTime, endTime, and userId. The first INSERT succeeds; a retried POST produces the same key, triggers the UNIQUE constraint, and lets the handler return the existing booking instead of charging twice. section 8.5
Use the constraint when:
- A natural identifier can represent duplicates, such as an idempotency key. section 8.5
- You want a fully atomic race resolution without holding a lock, while accepting duplicate-key handling in application code. section 8.5
Table 8.4 Strategies for resolving a write race
|
Mechanism
|
How it
resolves the race
|
What it
buys
|
What it
costs
|
|
SELECT … FOR UPDATE
(pessimistic)
|
First
writer locks the row; the second blocks until the first commits
|
Strong,
simple to reason about; no retry logic
|
Holds a
lock across the critical section; serializes writers, lowering throughput
|
|
Version
column (optimistic)
|
Both write
with WHERE version = N;
the loser's update affects 0 rows and retries
|
No lock
held; high throughput when conflicts are rare
|
The app
must detect the 0-row update and retry, more code, worse under contention
|
|
Unique
constraint
|
Both INSERT; the database
rejects the second with a duplicate-key error
|
Cheapest,
fully atomic, leans on a guarantee the DB already enforces
|
Only works
when there's a natural unique identifier to constrain on
|
Compare unique constraints with pessimistic locking and optimistic version checks when choosing a write-race strategy. section 8.5
Figure 8.8 A double-clicked "Confirm" sends two requests with the same idempotencyKey. A UNIQUE constraint lets the first INSERT succeed but rejects the second, creating one booking instead of a double charge.

Trace how two requests with the same idempotency key produce one successful booking. section 8.5







