PostgreSQL — duplicate key value violates unique constraint Despite a Check

Explain duplicate key violations under concurrency despite check-then-insert code, use ON CONFLICT for atomic upserts, and handle serialization failures correctly.

intermediatedebuggingpostgresqlconcurrencyupsert

Symptoms

ERROR: duplicate key value violates unique constraint "users_email_key" although the code checked for existence first
Failures cluster under concurrent retries, webhooks, or multi-tab submissions of the same logical entity
The rate rises exactly when duplicate submissions become likely (double-click, at-least-once delivery)

Possible causes

Two transactions both run the existence check before either commits, both see absence, both insert
The check and the insert run in separate statements without a transaction or unique constraint coordination
A retry after a serialization failure re-inserts an already-committed row
At-least-once webhook or queue delivery creates intentional duplicate requests the code treats as errors

Diagnosis steps

Reproduce the interleaving deterministically

psql -c "BEGIN; SELECT * FROM users WHERE email = '[email protected]';" # session A open; psql -c "BEGIN; SELECT ... same; INSERT ...;" in session B, then commit A

Interpretation

Two open transactions both reading absence before either writes reproduces the race in seconds, proving the check is not a lock.

Audit check-then-insert call sites

grep -Rn "if not exists\|SELECT .* WHERE .*;" src/ | grep -i "insert" | head

Interpretation

Every existence-check followed by a separate INSERT is a candidate race; the fix is one atomic statement.

Classify the duplicate intent

grep -Rn "webhook\|at-least-once\|redeliver" src/ | head -5

Interpretation

When duplicates are by design (at-least-once delivery), the answer is idempotent upserts, not tighter locking.

Root cause

A plain SELECT takes no lock that prevents another transaction from inserting the same key. Two concurrent transactions can both observe absence and both insert; the unique constraint then correctly rejects the second commit with a duplicate key violation. The check-then-insert pattern is a TOCTOU race against the constraint that exists precisely to catch it.

Fix

Replace check-then-insert with INSERT ... ON CONFLICT (key) DO UPDATE or DO NOTHING, use INSERT ... SELECT ... WHERE NOT EXISTS with a unique constraint as the backstop, and treat the constraint error as a signal for idempotent handling, not a bug.

Risk note

ON CONFLICT DO UPDATE turns every duplicate into an update — only correct if the update is truly idempotent. Otherwise DO NOTHING plus a follow-up read matches intent.

Verify the fix

A concurrency test firing N parallel submissions of the same logical entity produces exactly one row and no unhandled errors; webhook redelivery tests confirm idempotency.

Symptom walkthrough

The contradiction dissolves once you see the interleaving:

T1: SELECT ... WHERE email = '[email protected]'  -> 0 rows
T2: SELECT ... WHERE email = '[email protected]'  -> 0 rows
T1: INSERT ...                           -> ok
T2: INSERT ...                           -> ERROR: duplicate key

Both checks ran before either insert committed. A plain SELECT acquires no lock that blocks the other writer, so the check is an observation, not a reservation. The duplicate rate tracking at-least-once delivery (webhooks, queue retries, double-clicks) is the fingerprint: the system is working as delivered, and the code is treating designed duplicates as corruption.

Fix in practice

One atomic statement replaces the race:

-- idempotent insert:
INSERT INTO users (email, name)
VALUES ('[email protected]', 'Ada')
ON CONFLICT (email) DO NOTHING;

-- true upsert when overwrite is the intent:
INSERT INTO users (email, name)
VALUES ('[email protected]', 'Ada')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

For read-back semantics after DO NOTHING:

INSERT ... ON CONFLICT DO NOTHING RETURNING id;
-- empty return: SELECT id FROM users WHERE email = '[email protected]';

And when a transaction fails with serialization (40001) and retries, the retry must re-run the whole transaction — including re-reading current state — or it re-creates the very duplicate it just committed.