A signup flow runs a SELECT for the submitted email address and, when no row comes back, INSERTs the new account. Under concurrent traffic it occasionally creates duplicate accounts for the same address. Explain why, and what actually fixes it.

## The shape of the bug The code is: 1. `SELECT id FROM users WHERE email = ?` 2. if no row → `INSERT INTO users (email, ...) VALUES (?, ...)` Between steps 1 and 2 there is a window. Whatever the code learned in step 1 is a statement about the past, and nothing prevents the world from changing before step 2 lands. This is the check-then-act (or time-of-check-to-time-of-use) pattern, and it is the single most common concurrency defect in application data access. ## Why a transaction alone does not close the window The usual first reaction is 'wrap it in a transaction'. That does not help, and understanding why is the point of the question. A transaction gives atomicity and a consistent view; it does not give exclusivity over rows that do not exist. Locks in a relational engine are taken on rows (and on index entries). Reading a predicate that matches **nothing** finds no row to lock, so nothing is protected. This is the phantom problem: the absence of a row is not a lockable object at the usual isolation levels. Under **READ COMMITTED**, each statement sees committed data as of its own start, so transaction B's SELECT cannot see A's uncommitted insert. Under snapshot-based **REPEATABLE READ**, B sees its transaction-start snapshot, which also excludes A's row. Either way both transactions legitimately conclude 'no such user'. **SERIALIZABLE** is the one level that is defined to prevent this. An engine implementing it with predicate locks or with serializable snapshot isolation will detect the read-write conflict and abort one transaction with a serialization failure, which the application must retry. That is correct but expensive and operationally noisy: you now need retry logic on every such path, and throughput suffers under contention. It is a fine tool for invariants nothing else can express, and overkill for uniqueness. Also note `SELECT ... FOR UPDATE` does not help: there is no row to lock. It solves check-then-act for *existing* rows, not for absence. ## The actual fix: make the constraint physical Declare a unique constraint (or unique index) on the email column. Enforcement then happens inside the index B-tree at the moment of insertion: - Transaction A inserts, placing its index entry for that key. - Transaction B inserts the same key, finds the conflicting entry, and blocks waiting for A to commit or roll back — the engine has something concrete to wait on. - If A commits, B fails with a unique-violation error. If A rolls back, B proceeds. There is no window, no isolation level to reason about and no retry loop. The invariant is enforced by the physical structure rather than by a sequence of decisions. This generalises: whenever you catch yourself writing 'check that no row exists, then insert', ask what index would make the conflict physical. Conditional uniqueness — 'at most one active row per customer' — has the same answer via a filtered/partial unique index. ## What the application code should look like afterwards Keep the SELECT. It is not there for correctness any more; it is there for user experience — it lets you return a clean 'this email is already registered' response on the common path, alongside other form errors, without hitting an exception path. Then handle the violation: - Catch the specific unique-violation error and identify **which** constraint fired by name. A row may violate several unique keys, and 'email taken' and 'username taken' are different messages. Named constraints make that mapping possible. - Translate to the same user-facing error as the pre-check, so the rare race and the common case look identical to the user. - Be aware of the transaction consequences: in some engines any error aborts the transaction, so a savepoint around the insert, or restarting the transaction, is required to continue. In others the statement fails and the transaction survives. - For operations that are naturally idempotent, prefer an insert-if-absent form (an upsert or insert-on-conflict-do-nothing) so the race is not an error at all. ## Things that look like fixes and are not **An application-level lock or `synchronized` block.** Works on one process, fails the moment you run two instances — which is every production deployment. It also does nothing about scripts and other services. **A distributed lock.** Correct in principle but far heavier than an index, adds a dependency and a failure mode, and still leaves the database able to accept duplicates from any path that forgot the lock. **Retrying the SELECT, or sleeping.** Does not narrow the window in any meaningful way; it just changes the probability. **Serializable isolation.** Correct, but you have replaced a free, always-on, universal guarantee with one that requires every writer to run at that level and every caller to retry. ## The generalisation worth saying out loud An invariant enforced by a sequence of application steps is only as strong as the weakest concurrent interleaving and the most careless code path. An invariant enforced by the database is a property of the data. When both are available, use the database for the guarantee and the application for the message.