The Get-or-Create Race: Duplicate Rows Under Concurrency, Unique Constraints and ON CONFLICT Traps

Few lines of code are more ordinary than the one that looks a record up and creates it when nothing comes back. Find the tag or create it; find this month's folder or create it; find the user by email or register them. With one request at a time it is flawless. It passes every test and runs quietly for months. Then one day there are two rows with the same name, created within the same millisecond, with half of the related data pointing at one and the rest at the other. Both requests asked whether the row existed, both were told it did not, and both inserted. However short the gap between the lookup and the insert, concurrency will eventually fit two requests into it.

Why it shows up in production and not in tests

Test suites mostly run sequentially, and a developer submits a form once. What triggers the race is the parallelism of real use: a mobile client uploading its whole queue at once, a double clicked button, a client that times out and retries, a queue that delivers the same message twice, two webhooks arriving in the same second. Duplicates cluster around firsts, because only the first write for a key can race: the first upload of a month, a new customer's first order, the first use of a new tag. Every later request finds the existing row and the problem goes quiet.

Why application side fixes fall short

The first instinct is to put a lock in the code. An in-process mutex protects exactly one process. Run the application with several workers or on several servers and each one holds its own lock while the race carries on.

The second instinct is SELECT ... FOR UPDATE, but you cannot lock a row that does not exist. When the query returns nothing, PostgreSQL locks nothing, and both transactions insert anyway. InnoDB in MySQL, at its default isolation level, takes a gap lock instead. Gap locks do not conflict with each other, so both transactions get one, both then try to insert, and you get a deadlock error. The race is not fixed; only the error it produces has changed.

A distributed lock in something like Redis looks like it works, but it comes with an expiry; a request that stalls past it loses the lock and lets the next one in. It also only stops code that knows to ask for it. A manual SQL session or a one off import script will never see it.

The fix that holds: tell the database

The only guard that applies to every writer is a unique constraint. With it in place, the database rejects the second insert no matter which code path, process or language it came from.

ORM helpers are misleading on this point. Django's documentation is explicit about get_or_create: if uniqueness is not enforced at the database level for the fields involved, concurrent calls can insert several rows with the same values. Rails' create_or_find_by and Laravel's createOrFirst flip the order, trying the insert first and reading the existing row when a uniqueness violation comes back. That is the right idea, but without a constraint there is never a violation, the insert always succeeds and the helper protects you from nothing. Their safety lives in the schema, not in the method name.

Doing it in PostgreSQL, and the RETURNING surprise

PostgreSQL lets you handle the conflict inside the statement instead of through an exception:

INSERT INTO tags (name)
VALUES ('reports')
ON CONFLICT (name) DO NOTHING
RETURNING id;

The first surprise is RETURNING. On a conflict nothing is inserted and nothing is returned. If you need the id, an empty result has to be followed by a separate SELECT.

Folding both into one statement with a CTE that combines the insert and a lookup through UNION ALL is a popular trick. The catch is that every part of a CTE shares one snapshot. If the conflicting row was committed by another transaction after your statement started, the insert waits for it and skips, but the lookup cannot see the new row and the statement returns nothing. Under the default READ COMMITTED level every new statement takes a fresh snapshot, so running it once more fixes this.

The second surprise comes from writing DO UPDATE SET name = EXCLUDED.name so that a row always comes back. It does come back, but every call now performs a real update: a new row version, update triggers firing, a row lock, and table bloat for no reason.

The third one concerns sequences. An identity column fed by nextval consumes a value even when the insert ends in a conflict, so on tables where conflicts are common the ids develop large gaps. Any code assuming consecutive ids is wrong.

Soft deletes and NULLs: the partial unique index

On tables that mark deleted rows with a deleted_at column, a plain unique index gets in the way: a deleted record blocks a new one with the same name. A unique index on (name, deleted_at) does not help either, because unique indexes treat NULLs as distinct from each other. Two live rows, both with a NULL deleted_at, pass the constraint without complaint. What you want is a partial unique index:

CREATE UNIQUE INDEX tags_name_active_unique
    ON tags (name)
    WHERE deleted_at IS NULL;

With only this index in place, ON CONFLICT (name) fails with there is no unique or exclusion constraint matching the ON CONFLICT specification. For PostgreSQL to pick a partial index, the conflict target has to repeat its predicate: ON CONFLICT (name) WHERE deleted_at IS NULL DO NOTHING.

When you really do want NULLs to compare equal, PostgreSQL 15 added NULLS NOT DISTINCT. When case differences should count as duplicates, as with email addresses, build the index on an expression such as lower(email), or [email protected] and [email protected] end up as two users.

Adding the constraint when duplicates already exist

The decision to add a constraint usually comes after the duplicates, and building a unique index fails while they exist. Find them first:

SELECT name, count(*) AS copies, min(id) AS keep
FROM tags
WHERE deleted_at IS NULL
GROUP BY name
HAVING count(*) > 1;

Merging is more than deleting the extra row. Everything attached to it, child records, file links, sharing settings, has to move to the row you keep, or you lose data, or a foreign key refuses the delete. On a small table, do the merge and the index in one migration and one transaction, so no new duplicate can sneak in between.

On large tables you will reach for CREATE UNIQUE INDEX CONCURRENTLY, because a plain index build blocks writes for its whole duration. A concurrent build cannot run inside a transaction, though, and if a new duplicate lands while it runs, the build fails and leaves an INVALID index behind. Queries ignore that index, yet it keeps adding overhead to every write. Check with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; and drop and retry if anything turns up.

Catching the exception inside a transaction

Catching the uniqueness violation in application code and then reading the existing row is a valid approach too, but in PostgreSQL a failed statement poisons the whole transaction. Every statement after it fails with current transaction is aborted, commands ignored until end of transaction block. The insert attempt has to run inside a savepoint. The Rails and Laravel helpers do that for you; hand written code forgets it easily, and the bug only shows up during the race, which is exactly the moment hardest to reproduce.

When unique means no overlap

Sometimes what must be unique is not a value but a range, such as two overlapping bookings for the same room. A unique index cannot express that, but a PostgreSQL exclusion constraint gives the same guarantee:

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE reservations
    ADD CONSTRAINT reservations_no_overlap
    EXCLUDE USING gist (room_id WITH =, during WITH &&);

Here during is a tstzrange column. A second booking for the same room with an overlapping range is rejected, and the check-then-insert code never needs to be written.

When this is the wrong tool

If uniqueness is not a hard rule, a constraint is the wrong tool. Merging similar customer names or differently spelled addresses is fuzzy work; it belongs in a matching job that asks a human for the final call, not in the schema.

Sharded databases need care as well. Most distributed databases require a unique constraint to include the shard key, so "email is unique across the whole system" cannot be expressed as one constraint. There you need a separate table or service whose only job is to own that uniqueness.

For every other get-or-create line, the rule is short: before writing the code, check that the unique index is actually in the schema. If it is not, that line will produce a duplicate sooner or later.