Check-then-insert: the race you've already shipped
"Check if the email exists; if not, insert it." Every codebase has this somewhere — a SELECT followed by a conditional INSERT, usually via an ORM's find_or_create. Under concurrency it's wrong, and the failure needs no exotic interleaving:
The same person double-clicks 'Sign up'. Two app servers each run: check, then insert.
A> BEGIN;
Query OK
A> SELECT count(*) AS existing FROM signups WHERE email = 'bob@example.com';
existing
----------
0
(1 row)
B> BEGIN;
Query OK
B> SELECT count(*) 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');
Query OK, 1 row affected
A> COMMIT;
Query OK
B> INSERT INTO signups (email) VALUES ('bob@example.com');
Query OK, 1 row affected
B> COMMIT;
Query OK
A> SELECT count(*) AS bobs FROM signups WHERE email = 'bob@example.com'; -- the "impossible" duplicate
bobs
------
2
(1 row)Verified against MySQL 8.4.10 · Run it yourself · Scenario source
A check in code is not a constraint
This race ships silently — no error, no lock wait, nothing in the logs — and surfaces as "impossible" duplicate rows weeks later. If uniqueness matters, declare it UNIQUE.
Both transactions told the truth: at the moment each SELECT ran, no bob@example.com existed. The check and the insert are two statements; the world may change between them, and no isolation knob makes two statements one. Only the database can close that gap, with a constraint that's checked at the insert:
UNIQUE + ON DUPLICATE KEY: the fix
Same double-click as the previous lesson, but now the table has UNIQUE (email).
A> BEGIN;
Query OK
A> INSERT INTO signups (email) VALUES ('bob@example.com');
Query OK, 1 row affectedB'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;
Query OK
⏵ B's blocked statement fails:
ERROR 1062 (23000): Duplicate entry 'bob@example.com' for key 'signups.email'ER_DUP_ENTRY — the race is now loud instead of silent.
ON DUPLICATE KEY UPDATE makes the duplicate do something useful instead of erroring:
B> INSERT INTO signups (email) VALUES ('bob@example.com')
ON DUPLICATE KEY UPDATE attempts = attempts + 1; -- affected = 2 is MySQL's way of saying 'existing row updated'
Query OK, 2 rows affected
B> SELECT email, attempts FROM signups; -- one row, and it counted the duplicate attempt
email | attempts
-----------------+----------
bob@example.com | 2
(1 row)INSERT IGNORE also absorbs the duplicate (0 rows affected) — but it downgrades EVERY error on the statement to a warning, not just 1062. Prefer ON DUPLICATE KEY UPDATE: it targets exactly the race you mean.
B> INSERT IGNORE INTO signups (email) VALUES ('bob@example.com');
Query OK, 0 rows affectedVerified against MySQL 8.4.10 · Run it yourself · Scenario source
Three details in that transcript are worth a second look. The first is the wait: B's plain INSERT didn't fail immediately but parked in the lock queue until A's fate was decided. Had A rolled back, B's insert would have succeeded — the constraint arbitrates the race correctly, not only loudly.
The second is the affected-rows convention. Per the manual, "the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values." That 2 in the transcript is your "it was a duplicate" signal, and the same 1-versus-0 distinction powers the idempotency pattern.
The third is INSERT IGNORE, the blunt instrument. It absorbs the duplicate too, but it downgrades every error on the statement to a warning — type truncations included — so reach for ON DUPLICATE KEY UPDATE, which handles exactly the conflict you named.
The lesson compresses to one rule: SELECT-then-INSERT can't enforce uniqueness at any isolation level, so the constraint has to live in the database. INSERT … ON DUPLICATE KEY UPDATE gives you an atomic insert-or-update, and its 1/2/0 affected-rows count tells you which branch ran. Because the concurrent duplicate waits for the first inserter's COMMIT or ROLLBACK before it resolves, you get correctness under the race, not only an error code after the fact.