UNIQUE constraints and the check-then-insert race

Every codebase has this handler somewhere:

if (userRepository.existsByEmail(email)) {
    throw new EmailTakenException();
}
userRepository.save(new User(email));

Check, then insert. It reads correctly and it is wrong: between the check and the insert, another request can insert the same email. Both checks saw "free", both inserts proceed, and you have two accounts with one email. The window is milliseconds wide, which means it never fires in dev, fires rarely in production, and fires reliably the day a signup form double-submits or a retry queue replays.

Only the constraint closes the race

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(320) UNIQUE NOT NULL
);

The UNIQUE constraint is enforced by the index at write time, under locking that application code can't replicate with SELECTs (two transactions genuinely cannot both commit the same value). No amount of existsBy, and no transaction wrapping at the default isolation level, gives you that: the second transaction's check simply doesn't see the first one's uncommitted row. The database is the only place all writers meet.

So is the application check useless?

No, it's UX. The pre-check gives the friendly 409 with a form message in the common case, cheaply. The constraint is the guarantee in the rare case. The bug is treating the check as the guarantee and the constraint as paranoia; the honest shape is both, with the violation handled:

try {
    userRepository.saveAndFlush(new User(email));
} catch (DataIntegrityViolationException e) {
    throw new EmailTakenException();   // lost the race: same answer as the pre-check
}

The flush matters in JPA: without it the INSERT may run at commit time, after your try/catch is gone, and the violation surfaces as a transaction-level error two layers up. In Postgres there's a neater tool when "already exists" is an expected outcome rather than an error:

INSERT INTO users (email) VALUES ($1)
ON CONFLICT (email) DO NOTHING
RETURNING id;

No exception, no retry loop: an empty result means someone got there first. DO UPDATE turns the same statement into an idempotent upsert, which is usually what a retry-safe endpoint wanted all along.

The details that catch people

Case. Alice@x.com and alice@x.com are different values to a UNIQUE index. Either normalize on write, or make the index do it: CREATE UNIQUE INDEX ON users (lower(email)); (expression indexes back constraints just fine).
NULLs don't collide. By default a unique column admits many NULL rows, SQL says NULL isn't equal to NULL. Postgres 15+ has UNIQUE NULLS NOT DISTINCT when you want at most one.
Adding UNIQUE to a full table fails if duplicates already exist, and finding them first is the actual migration work:
```
SELECT email, count(*) FROM users
GROUP BY email HAVING count(*) > 1;
```
One violation aborts the transaction. In Postgres you can't catch-and-continue inside the same transaction without a savepoint; design the write so the conflict is the last thing it does, or use ON CONFLICT.

The generated code should acknowledge the constraint too: Schemint carries UNIQUE into @Column(unique = true) on the entity and flags the column, as a reminder that somewhere an "already exists" path needs handling, in the friendly pre-check and in the authoritative catch.

Paste your schema and Schemint lists every unique column alongside the entity and migration, each one is a check-then-insert race your handlers need to lose gracefully.

UNIQUE constraints and the check-then-insert race

Only the constraint closes the race

So is the application check useless?

The details that catch people

Related