Why “Check Then Insert” Creates Duplicate Records

Two requests arrive a few milliseconds apart. Each one asks the database whether the email address is taken, each one hears “no”, and each one inserts a new user. Nothing threw an error, every test passed, and the table now holds two accounts for one person. This guide explains why the pattern fails, why transactions and SELECT ... FOR UPDATE usually do not save it, and how to fix it for good.

The pattern goes by several names: check-then-act, read-then-write, insert if not exists, get-or-create. It shows up in sign-up flows, “one cart per user” rules, booking systems, API idempotency checks, and ORM helpers that look atomic but are not. If you are dealing with duplicate webhook deliveries specifically, How to Prevent Duplicate Webhook Processing covers the provider side. This post covers the general database race that sits underneath it.

What is a check-then-insert race condition?

A check-then-insert race condition is a bug where code verifies that a row does not exist and then inserts it in a separate step, so another request can insert the same row in between. The check is correct when it runs. It is stale by the time the insert runs. Nothing in the code ties the two statements together, so any concurrency at all is enough to break the rule.

Here is the version almost every codebase has somewhere:

def register(conn, email, password):
    with conn.cursor() as cur:
        cur.execute("SELECT 1 FROM users WHERE lower(email) = lower(%s)", (email,))
        if cur.fetchone():
            raise EmailTaken(email)            # the check

        pw_hash = hash_password(password)     # ~100 ms of deliberate slowness

        cur.execute(                          # the act, based on a stale answer
            "INSERT INTO users (email, password_hash) VALUES (%s, %s)",
            (email, pw_hash),
        )
    conn.commit()

Password hashing is deliberately slow, so this handler has a race window of around a hundred milliseconds between the check and the insert. A double-click on the sign-up button, a mobile client that retries after a timeout, or a load balancer that retries an idempotent-looking POST all land inside that window.

The same shape appears whenever code reads state to decide whether it is allowed to write. “Count the bookings, and insert if there is room.” “Look for an active subscription, and create one if there is none.” “See if this idempotency key was used, and process the request if not.” Each is a check-then-insert with a different predicate.

Where does check-then-insert hide in real code?

It is easy to spot in a handler that runs a SELECT and then an INSERT. It is harder to spot when a framework does the check for you, or when the check and the write live in different functions.

Why do tests almost never catch it?

Tests miss check-then-insert races because they run requests one at a time, against a quiet database, with no retries. A race needs two writers for the same key inside the same few milliseconds, and a test suite rarely creates that. Production creates it constantly:

• Double submits. A user clicks twice, or a form submits on both Enter and click.
• Client and proxy retries. A timeout on the first attempt triggers a second attempt while the first is still running. See How to Spot a Retry Storm Before Merge for how retries multiply.
• Redelivered messages. Queues deliver at least once, and a redelivery can overlap the original. SQS or Kafka Message Processed Twice covers the consumer side.
• More replicas. A scheduled job that ran on one instance now runs on three after a scale-out.
• Slower databases. Under load, each statement takes longer, which widens every window.

That is why duplicates often appear “suddenly” after a traffic increase or a deploy that added workers, even though the code has not changed in months. The broader problem of bugs that only show up under real concurrency is covered in Race Conditions That Only Appear Under Production Load.

How do you know you already have this bug?

SELECT lower(email)                         AS email_key,
       count(*)                             AS copies,
       max(created_at) - min(created_at)    AS spread,
       array_agg(id ORDER BY created_at)   AS ids
FROM   users
GROUP BY lower(email)
HAVING count(*) > 1
ORDER BY spread;

The spread column is the fastest diagnostic. A spread of a few milliseconds is a concurrent race. A spread of seconds is a retry. A spread of days is a different bug: the check itself is wrong, for example comparing emails case-sensitively.

Doesn’t a transaction or SELECT FOR UPDATE prevent it?

Usually not. A transaction makes a group of statements commit or roll back together. It does not, at the default isolation level, stop two transactions from reading the same missing row and both deciding to insert it. This is the most common wrong fix for check-then-insert, so it is worth being precise.

• PostgreSQL, READ COMMITTED (the default). Each statement sees data committed before it started. Both checks see nothing, and both inserts succeed unless a unique index stops the second.
• PostgreSQL, REPEATABLE READ. Each transaction reads from one snapshot. The two inserts do not update the same existing row, so there is no write conflict to detect. Without a unique index, both commit.
• SELECT ... FOR UPDATE in PostgreSQL. It locks rows the query returns. A query that returns zero rows locks nothing.
• MySQL InnoDB, REPEATABLE READ (the default) with FOR UPDATE. InnoDB takes a gap lock on the empty range. Gap locks from different transactions do not conflict, so both proceed; then each insert waits on the other’s gap lock, and InnoDB rolls one back with a deadlock error.
• SERIALIZABLE. PostgreSQL’s serializable snapshot isolation detects the read/write dependency and fails one transaction with SQLSTATE 40001. That is correct behavior, but only if your code retries the failed transaction.

The PostgreSQL behavior is documented in Transaction Isolation. If you want to see how often other developers hit exactly this, the Stack Overflow questions on SELECT FOR UPDATE and insert-if-not-exists are a long list of the same surprise.

How do you fix check-then-insert?

Fix check-then-insert by moving the rule into the database and making the insert itself the check. Declare the rule as a constraint, try the write, and treat “already exists” as a normal outcome rather than a pre-condition. The database is the only component that sees every writer, on every instance, at the moment each one writes.

Step 1: declare the rule as a unique constraint

-- Case-insensitive uniqueness, because that is what the rule actually means
CREATE UNIQUE INDEX CONCURRENTLY users_email_lower_key
    ON users (lower(email));

Normalize the key the way the business rule does. A unique index on email will happily accept Ana@x.io and ana@x.io. Also check how nullable columns behave: PostgreSQL treats NULLs as distinct in a unique index by default, so several rows with a NULL key are allowed unless you use NULLS NOT DISTINCT (PostgreSQL 15 and later).

Step 2: insert first, then read on conflict

def register(conn, email, password):
    pw_hash = hash_password(password)        # slow work first; it no longer matters
    with conn.cursor() as cur:
        cur.execute(
            """INSERT INTO users (email, password_hash)
               VALUES (%s, %s)
               ON CONFLICT ((lower(email))) DO NOTHING
               RETURNING id""",
            (email, pw_hash),
        )
        row = cur.fetchone()
    conn.commit()
    if row is None:
        raise EmailTaken(email)               # the index decided, not a stale SELECT
    return row[0]

The double parentheses in ON CONFLICT ((lower(email))) are required: the conflict target is a list, and an index expression inside it needs its own parentheses. The target must match a unique index; see the PostgreSQL INSERT documentation for the full rules. For get-or-create, where you want the existing row back, add a second statement:

INSERT INTO carts (user_id) VALUES ($1)
ON CONFLICT (user_id) DO NOTHING
RETURNING id;

-- No row returned: the cart already existed, or a racer just committed it.
SELECT id FROM carts WHERE user_id = $1;

If you cannot use ON CONFLICT, the same logic works by catching the error. Do the insert, catch the unique violation (SQLSTATE 23505 in PostgreSQL, error 1062 in MySQL), and read the existing row. In PostgreSQL, any error aborts the surrounding transaction, so wrap the insert in a savepoint if the transaction must continue afterwards. In MySQL, prefer INSERT ... ON DUPLICATE KEY UPDATE over INSERT IGNORE, because IGNORE also downgrades unrelated errors, such as truncated values, to warnings.

Step 3: pick the right guard when the rule is not a plain key

-- "One active subscription per user"
CREATE UNIQUE INDEX one_active_subscription
    ON subscriptions (user_id) WHERE status = 'active';

-- "No overlapping bookings for a room" (during is a tstzrange)
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE bookings ADD CONSTRAINT no_overlapping_bookings
    EXCLUDE USING gist (room_id WITH =, during WITH &&);

-- "No more than capacity": the UPDATE is the check
UPDATE events SET seats_taken = seats_taken + 1
WHERE  id = $1 AND seats_taken < capacity
RETURNING seats_taken;          -- 0 rows: sold out; insert the booking only if 1 row

-- Fallback: serialize on the key itself, released at COMMIT or ROLLBACK
SELECT pg_advisory_xact_lock(hashtextextended('signup:' || lower($1), 0));

The conditional UPDATE works because a row-level write lock serializes concurrent updates to the same event row: the second UPDATE waits, then re-evaluates seats_taken < capacity against the new value. Locking a parent row with SELECT ... FOR UPDATE works for the same reason: unlike the missing child row, the parent exists and can be locked. Advisory locks work too, but every code path that writes the key must remember to take the same lock, which is exactly the kind of convention that erodes over time.

Are get_or_create and find_or_create_by safe?

They are safe only when a unique constraint backs the lookup fields. Both helpers read before they write, so without a constraint two concurrent calls can both create. With a constraint, the second create fails and a well-built helper recovers by reading the winner’s row.

• Django get_or_create. The Django documentation says it is atomic assuming the database enforces uniqueness of the keyword arguments. With a unique=True field, OneToOneField, or UniqueConstraint, a racing create raises IntegrityError and Django fetches the existing row.
• Rails find_or_create_by runs a SELECT and then an INSERT. create_or_find_by (Rails 6 and later) inserts first and falls back to a find on ActiveRecord::RecordNotUnique. It needs the unique index to exist, and the Rails validation guide recommends that index for validates :uniqueness too.
• JPA and Hibernate have no get-or-create. The usual pattern is to insert in a new transaction and catch DataIntegrityViolationException (Spring) or ConstraintViolationException (Hibernate), then load the existing entity.
• SQLAlchemy exposes the PostgreSQL form directly: insert(Cart).values(user_id=uid).on_conflict_do_nothing(index_elements=["user_id"]) from sqlalchemy.dialects.postgresql.

# db/migrate: the guard
add_index :carts, :user_id, unique: true

# Races without the index: SELECT, then INSERT
Cart.find_or_create_by(user_id: user.id)

# Inserts first; on RecordNotUnique, finds the existing cart
Cart.create_or_find_by(user_id: user.id)

How do you add a unique constraint when duplicates already exist?

You clean up first, then build the index without blocking writes. A unique index cannot be created while duplicate keys exist, so the rollout is a short procedure rather than a one-line migration:

1. Find the existing duplicates. Group by the normalized key and list every key with more than one row, as in the query above.
2. Merge or remove them. Pick a surviving row per key, re-point foreign keys and child rows to it, and delete or archive the others. This is a product decision as much as a technical one, so agree on the survivor rule first.
3. Build the unique index without blocking writes. In PostgreSQL, CREATE UNIQUE INDEX CONCURRENTLY avoids a long write lock but cannot run inside a transaction block. If it fails, for example because a new duplicate slipped in, it leaves an INVALID index behind: drop it, fix the rows, and run it again.
4. Switch the code to insert first. Replace the SELECT-then-INSERT with ON CONFLICT, or with an insert that catches the unique violation.
5. Keep the check only as a friendly error. The database error is now the real answer.

-- Must run outside BEGIN/COMMIT (many migration tools need a "no transaction" flag)
CREATE UNIQUE INDEX CONCURRENTLY users_email_lower_key ON users (lower(email));

-- If the build failed, the index exists but is not valid:
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY users_email_lower_key;   -- then fix the data and retry

An expression index such as lower(email) enforces uniqueness on its own; you do not need to convert it to a table constraint. ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX only accepts plain column indexes, not expression or partial ones. The details are in the CREATE INDEX documentation. Deploy the index before the code change that relies on it; Missing Database Indexes in a PR covers how reviewers can check that ordering.

What should a reviewer look for in a pull request?

A check-then-insert rarely looks wrong in a diff. It looks like careful code: the author checked first. The review question is whether the database enforces the same rule the code checks. Walk the diff for these patterns:

This fits into the broader review practice in How to Review a Pull Request for Production Reliability Risks. A useful habit is to write one concurrency test per uniqueness rule: start two threads or two connections, release them from a barrier at the same moment, and assert that exactly one row exists afterwards. It will not catch every interleaving, but it catches the missing constraint every time.

How Tomosu helps

Tomosu analyzes a repository and each pull request for production reliability risks, and check-then-insert is a pattern where the evidence lives in more than one file: the handler that checks, the model or repository that writes, and the migration that does or does not declare the constraint. For this problem, Tomosu:

• Flags read-then-write sequences where a query decides whether an insert or upsert runs, including ORM helpers such as find_or_create_by and uniqueness validators.
• Checks for the matching guard in the schema and migrations, and calls out a rule that only exists in application code.
• Points at the wider window: remote calls, hashing, or retries between the check and the write, and callers that retry the same request.
• Weighs the blast radius, so a duplicate-prone path in billing or account creation ranks above one in an internal admin tool.

Those findings feed the Production Reliability Index alongside the other signals for the change. To try it on your own code, follow the repository scan guide.

Scan your repository with Tomosu →

Key takeaways

• Check then insert creates duplicate records because two requests can both pass the check before either one writes.
• Tests rarely catch it; double submits, retries, redeliveries, and extra replicas trigger it in production.
• Transactions at default isolation and SELECT ... FOR UPDATE do not stop it, because you cannot lock a row that does not exist.
• Put the rule in the database: a unique, partial unique, or exclusion constraint, or a conditional update for counts.
• Make the insert the check with ON CONFLICT or by catching the unique violation, and return the same response on both branches.
• ORM get-or-create helpers are only safe with a unique index behind them.
• On a live table, clean duplicates first, then build the index concurrently before shipping code that depends on it.

Frequently asked questions

Duplicate records are rarely a data problem. They are a rule that lived in application code and never reached the database. Tomosu helps you find those rules before production does. Assess your repository →