Check-then-insert: the race you've already shipped
Checking for an email and then inserting it leaves a concurrency gap. In this InnoDB schedule at the default REPEATABLE READ level, both plain SELECTs report no matching row. With no unique constraint, both inserts commit:
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.11 · Run it yourself · Scenario source
A check in code is not a constraint
The final count is 2. Declare UNIQUE when the database must enforce email uniqueness. This schedule does not establish that every isolation level or locking strategy permits the same race.
Both checks were accurate when they ran. Neither reserved the missing email. The SERIALIZABLE lesson shows that stronger locking can instead create waits and deadlocks; that does not replace declaring the invariant in the schema.
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 skips this duplicate with 0 affected rows. The manual documents other ignorable errors too, not every error. An upsert reacts to any conflicting primary or unique key, not a named conflict target.
B> INSERT IGNORE INTO signups (email) VALUES ('bob@example.com');
Query OK, 0 rows affectedVerified against MySQL 8.4.11 · Run it yourself · Scenario source
B's plain INSERT waits for A, then fails with 1062 after A commits. The subsequent upsert increments the existing row's attempts to 2 and reports 2 affected rows. The final INSERT IGNORE skips the duplicate and reports 0 affected rows. A rollback by the first inserter is not executed here.
The MySQL 8.4 upsert contract applies to a conflict on a primary or unique key. It does not select a named conflict target. With multiple unique indexes, a different unique key can select the row to update. The affected-row convention is 1 for an insert, 2 for a changed update, and 0 for an unchanged update, except that CLIENT_FOUND_ROWS changes the last count to 1. The idempotency recipe requires that flag to be absent.
INSERT IGNORE does not downgrade every error. The INSERT manual says "Ignored errors generate warnings instead." Its ignorable conditions include duplicate keys and some invalid-value conversions. An upsert avoids that broad suppression, but its update can still fail, including on another constraint. Check the actual error and transaction state rather than treating every failure as a duplicate.