How do I insert a row only if it doesn't exist, without race conditions?

Almost every application has code like this:

user = db.query("SELECT id FROM users WHERE email = %s", email)
if not user:
    db.execute("INSERT INTO users (email) VALUES (%s)", email)

It passes every test. Then in production, two requests for the same email arrive a few milliseconds apart. Both SELECTs find nothing, both INSERTs run, and you get a duplicate row or a unique-violation error. "Insert if not exists" and "update or insert" (upsert) are some of the most-asked SQL questions because the obvious approach is a race condition.

Why check-then-insert is broken

Under the default isolation levels (READ COMMITTED in PostgreSQL, Oracle and SQL Server; REPEATABLE READ in MySQL), a SELECT that finds nothing doesn't lock anything. Nothing stops another transaction from inserting the same row between your check and your insert. Wrapping both statements in a transaction doesn't help by itself.

The fix has two parts:

1. A unique constraint. It's the only thing that truly prevents duplicates. Without one, no pattern below is safe.
2. A single atomic statement that tells the database what to do when the row already exists.

ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

PostgreSQL and SQLite: ON CONFLICT

-- Insert if missing, otherwise do nothing
INSERT INTO users (email, name) VALUES ('ana@example.com', 'Ana')
ON CONFLICT (email) DO NOTHING;

-- Upsert
INSERT INTO users (email, name) VALUES ('ana@example.com', 'Ana')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name, updated_at = now();

EXCLUDED is the row you tried to insert. This is atomic under concurrency: one statement wins and the others update or do nothing, with no errors and no duplicates. SQLite supports the same syntax.

A common gotcha: DO NOTHING ... RETURNING id returns no row when the row already exists. To always get the id back:

WITH ins AS (
  INSERT INTO users (email) VALUES ('ana@example.com')
  ON CONFLICT (email) DO NOTHING
  RETURNING id
)
SELECT id FROM ins
UNION ALL
SELECT id FROM users WHERE email = 'ana@example.com'
LIMIT 1;

PostgreSQL 15+ also has MERGE, but under concurrency it can still raise unique violations. For single-row upserts, ON CONFLICT is the right tool.

MySQL / MariaDB: ON DUPLICATE KEY UPDATE

INSERT INTO users (email, name) VALUES ('ana@example.com', 'Ana') AS new
ON DUPLICATE KEY UPDATE name = new.name;

The AS new alias syntax is MySQL 8.0.19+. Older versions use VALUES(name), which is now deprecated.

Things to watch for:

• It fires on a conflict with any unique index, not just the one you had in mind. Tables with several unique keys can update a different row than you expect.
• Avoid INSERT IGNORE for this. It also turns other errors (truncated data, invalid values, foreign-key failures) into warnings and carries on.
• REPLACE INTO is a delete plus an insert, not an update. It fires delete triggers, cascades foreign keys and gives the row a new auto-increment id.
• Upserts consume auto-increment values even when they update, so gaps in ids are normal.

SQL Server: lock the range, or handle the error

T-SQL has no ON CONFLICT, and a plain MERGE is not race-free on its own. The reliable pattern is to take a key-range lock:

BEGIN TRAN;

UPDATE dbo.Users WITH (UPDLOCK, SERIALIZABLE)
SET Name = @Name
WHERE Email = @Email;

IF @@ROWCOUNT = 0
    INSERT dbo.Users (Email, Name) VALUES (@Email, @Name);

COMMIT;

SERIALIZABLE locks the key range, even when no row exists, so a second session waits instead of inserting a duplicate. If you prefer MERGE, add WITH (HOLDLOCK) to the target.

For "insert if missing" where conflicts are rare, it's often simpler to just insert and catch error 2627/2601 (unique violation). That's safe, fast, and doesn't need extra locking.

Oracle

Use MERGE for upserts, or insert and catch DUP_VAL_ON_INDEX for insert-if-missing. As in SQL Server, concurrent MERGEs can still raise ORA-00001, so be ready to retry.

In the ORM

Most ORMs have a native upsert that compiles to the statements above (Prisma upsert, Django update_or_create plus a unique constraint, SQLAlchemy on_conflict_do_update, Rails upsert). Check the SQL it actually generates: some ORMs implement get_or_create as select-then-insert and depend on the unique constraint to catch the race.

Checklist

1. Add a unique constraint. Nothing else prevents duplicates.
2. Replace select-then-insert with one atomic statement: ON CONFLICT, ON DUPLICATE KEY UPDATE, or an UPDLOCK, SERIALIZABLE update-then-insert.
3. Avoid INSERT IGNORE and REPLACE INTO for upserts.
4. Where conflicts are rare, insert and handle the unique-violation error.