Check-then-insert: the race

An application check does not replace a uniqueness constraint. In this READ COMMITTED schedule, with no UNIQUE email constraint, both checks see zero rows and both requests insert. It does not test every isolation level or every check-and-insert protocol.

Watch the duplicate land

A

B

any bob?→ 0

any bob?→ 0

INSERT bob

COMMIT

INSERT bob (check already passed)

COMMIT

count bobs→ 2 — the "impossible" duplicate

The same person double-clicks 'Sign up'. Two app servers each run: check, then insert.

A> BEGIN;
BEGIN

A> SELECT count(*)::int AS existing FROM signups WHERE email = 'bob@example.com';
 existing 
----------
        0 
(1 row)

B> BEGIN;
BEGIN

B> SELECT count(*)::int AS existing FROM signups WHERE email = 'bob@example.com'; -- B's check also passes — A hasn't committed anything
 existing 
----------
        0 
(1 row)

Both checks passed, so both insert. Nothing blocks, nothing errors.

A> INSERT INTO signups (email) VALUES ('bob@example.com');
INSERT 0 1

A> COMMIT;
COMMIT

B> INSERT INTO signups (email) VALUES ('bob@example.com');
INSERT 0 1

B> COMMIT;
COMMIT

A> SELECT count(*)::int AS bobs FROM signups WHERE email = 'bob@example.com'; -- the "impossible" duplicate
 bobs 
------
    2 
(1 row)

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

The final count asserts two copies of the email. No waiting or error is observed in this schedule. The conclusion applies to the shown protocol, not a claim that arbitrary INSERT statements can never wait.

The fix is a constraint, not cleverer code

A

B

INSERT bob

INSERT bob (duplicate)→ ⏳ waits

COMMIT

⏵ INSERT bob (duplicate)← 23505 duplicate key value violates unique constraint "signups_email_key"

Same double-click, but now the table has UNIQUE (email).

A> BEGIN;
BEGIN

A> INSERT INTO signups (email) VALUES ('bob@example.com');
INSERT 0 1

B's insert can't decide yet — the winner hasn't committed. It waits on A's transaction.

B> INSERT INTO signups (email) VALUES ('bob@example.com');
⏳ B is waiting for a lock…

A> COMMIT;
COMMIT

⏵ B's blocked statement fails:
ERROR:  23505: duplicate key value violates unique constraint "signups_email_key"

unique_violation — the race is now loud instead of silent.

ON CONFLICT DO NOTHING absorbs the duplicate instead of erroring:

B> INSERT INTO signups (email) VALUES ('bob@example.com')
   ON CONFLICT (email) DO NOTHING; -- INSERT 0 0
INSERT 0 0

And DO UPDATE makes it an upsert — here just to read back the existing row's id:

B> INSERT INTO signups (email) VALUES ('bob@example.com')
   ON CONFLICT (email) DO UPDATE SET email = EXCLUDED.email
   RETURNING id, email;
 id |      email      
----+-----------------
  1 | bob@example.com 
(1 row)

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

With UNIQUE on the non-NULL email, B waits for A's insertion to commit, then fails with 23505. DO NOTHING affects zero rows. DO UPDATE returns the existing row with id 1. These are Demonstrated behaviors.

The PostgreSQL 18 INSERT contract defines the alternative conflict actions and RETURNING rows. It documents an atomic insert-or-update outcome for DO UPDATE when there is no independent error; that does not promise no waits or no serialization failures. Scope the constraint to the business key, including its NULL and comparison semantics. See the constraint contract.

A check-first query can improve the user message, but UNIQUE arbitrates concurrent conflicting inserts. ON CONFLICT handles the selected conflict; other failures still need recovery. The same mechanism gates database-local idempotency.

Further reading

• PostgreSQL 18: INSERT
• The same lesson on MySQL

The reconstructed support assessment records the exact manual support and execution limits for this lesson.