Your service checks 'SELECT ... WHERE email = ?' and inserts the user only if no row came back, yet duplicate emails still appear in production. Explain why, and what actually prevents duplicates.
answer
- Window between SELECT and INSERT
- Phantom — no row to conflict on
- UNIQUE index closes it, snapshot isolation does not
- Insert then catch 23505 / 1062
- NULLs, case, soft deletes, per-tenant scope
basics
~20 sTwo sessions can both run the SELECT before either INSERT, so both see nothing and both insert. Only a UNIQUE constraint on the column makes the invariant unbreakable; the application then catches the unique-violation error instead of pre-checking.
solid answer
~60 sThe check-then-act pattern has a window between the SELECT and the INSERT. Under normal isolation levels another session can slip its insert into that window, so both transactions see 'no row', and both write one. Retrying, adding a transaction, or raising the isolation level does not by itself fix it — read committed and snapshot reads still see nothing where a *future* row will be, and unless the engine's serializable implementation detects the conflict, both commit. The fix is to move the invariant into the engine: a **UNIQUE constraint** (backed by a unique index) on the email column. Uniqueness is then checked at write time against all committed and in-flight rows; the loser blocks until the winner commits, then fails with a unique-violation error. The application drops the pre-check, attempts the insert, and translates the specific integrity error code into a friendly 'email already registered' response. The general rule: an invariant enforced only by application logic is only as strong as the least careful code path and the narrowest race window. Declared constraints have no such window.
code
text · 10 linestime session A session B
---- --------------------------------- ---------------------------------
1 SELECT ... WHERE email='[email protected]'
-> 0 rows
2 SELECT ... WHERE email='[email protected]'
-> 0 rows
3 INSERT INTO users(email) ...
4 INSERT INTO users(email) ...
5 COMMIT COMMIT
two rows with the same emailgo deeper
Show the interleaving and say the fix is a UNIQUE constraint plus catching the error on insert.
Explain why isolation levels below serializable cannot help (there is no existing row to conflict on) and name the try-and-catch-error-code pattern.
Cover the real-world variants: normalization, soft deletes with partial indexes, per-tenant scope, and the dedupe migration needed before the constraint can be added.
Generalize to a policy — invariants expressible as constraints live in the schema, application checks are UX only — and weigh that against serializable isolation as a blanket alternative.
## The race, step by step ``` T1: SELECT id FROM users WHERE email = '[email protected]'; -- 0 rows T2: SELECT id FROM users WHERE email = '[email protected]'; -- 0 rows T1: INSERT INTO users (email) VALUES ('[email protected]'); T2: INSERT INTO users (email) VALUES ('[email protected]'); T1: COMMIT; T2: COMMIT; -- two rows ``` Nothing here is a bug in the database. Each transaction read a state that was true when it read it, and neither read is invalidated by the other's later insert, because there was no row to conflict on. This is a *phantom* — a row that did not exist at read time and does at write time — and it is exactly the class of anomaly that row-level reads cannot see. ## Why the usual reflexes do not fix it - **Wrapping both statements in one transaction**: still two independent transactions racing; atomicity is not mutual exclusion. - **REPEATABLE READ / snapshot isolation**: strengthens what a transaction re-reads, but a snapshot that showed zero rows keeps showing zero rows. Both still insert. - **Application-level locks or a 'check twice' retry**: narrows the window; does not close it, and breaks under multiple app instances or any other writer (a DBA script, an importer, a second service). - **SERIALIZABLE**: an engine with true serializable execution (predicate locking or serializable snapshot isolation) *can* catch this and abort one transaction, but you are then paying global concurrency cost, and you must handle serialization-failure retries — an expensive way to buy a property one index gives you for free. ## The engine-enforced fix ```sql ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email); ``` A UNIQUE constraint is implemented by a unique index. When T2 tries to insert the same key, the index write finds T1's uncommitted entry, waits for T1's outcome, and then either fails with a unique-violation error (T1 committed) or succeeds (T1 rolled back). There is no window, and the guarantee holds against *every* writer — the other service, the batch job, the manual SQL at 2 a.m. ## The application pattern that goes with it Stop pre-checking as a correctness mechanism and switch to **try the write, handle the specific error**: 1. Attempt the INSERT. 2. Catch the unique-violation error by its dedicated error code (SQLState 23505 in PostgreSQL, error 1062 in MySQL; in JDBC, `SQLIntegrityConstraintViolationException`). Never string-match the message. 3. Map it to a domain response: 'that email is already registered'. A pre-check is still fine as a *user-experience* optimization — giving fast feedback in a signup form — as long as nobody treats it as the guarantee. ## Practical gotchas - **NULLs.** In most engines, multiple NULLs are allowed in a UNIQUE column because NULLs are not equal to each other. If 'at most one row per user with no external id' is the rule, NULLs will not enforce it. - **Case and normalization.** `[email protected]` and `[email protected]` are different strings. Enforce on a normalized form — store the canonical lowercase value, or build the unique index on a normalized expression — or you have a constraint that does not express the real invariant. - **Soft deletes.** A unique index over the whole table blocks re-registering a deleted email; a partial/filtered unique index over live rows is the usual answer. - **Composite scope.** 'Unique per tenant' is `UNIQUE (tenant_id, email)`, not `UNIQUE (email)`. - **Deploying it.** Adding the constraint to an existing table fails until existing duplicates are cleaned up; plan the dedupe first. ## The general principle This question is really about the boundary between engine-enforced and application-enforced invariants. If an invariant can be expressed as a constraint, expressing it there removes an entire class of concurrency bug and makes the rule true for writers you have not met yet. Application checks are advisory; constraints are load-bearing.
- Would running both transactions at SERIALIZABLE isolation fix the duplicate?An engine with genuine serializable execution can detect the conflicting phantom and abort one transaction, so yes in principle — but only if you also implement retry-on-serialization-failure everywhere, and you pay concurrency cost across the whole workload. A unique index gives the same guarantee locally, cheaply, and against writers that never go through your code.
- Your users table allows soft deletes and the email column is UNIQUE. A user deletes their account and tries to sign up again with the same address. What breaks and how do you fix it?The insert fails because the soft-deleted row still occupies the unique key. The usual fix is a partial or filtered unique index covering only live rows (WHERE deleted_at IS NULL), so deleted rows no longer reserve the address. The alternative is to scramble or archive the email at deletion time, which also satisfies data-retention concerns.
saying these in an interview costs you the question
- Claiming a transaction alone prevents the race
- Believing REPEATABLE READ or snapshot isolation stops two inserts of a not-yet-existing row
- Adding an application-level mutex and calling the invariant enforced
- Detecting the duplicate by matching the error message text instead of the error code
- Forgetting that multiple NULLs are allowed under a UNIQUE column in most engines