DEV Community

Cover image for Why SELECT Before INSERT Causes Duplicate Records
Doogal Simpson
Doogal Simpson

Posted on Originally published at doogal.dev

Why SELECT Before INSERT Causes Duplicate Records

Quick Answer: Checking whether a record exists with a SELECT query before executing an INSERT fails under concurrent traffic. When two requests arrive simultaneously, both queries see zero records and proceed to insert duplicates. To reliably prevent duplicate entries, enforce a database-level unique constraint and handle the resulting violation error gracefully in your application layer.

We have all seen this pattern in codebases: you have a table where a column like email must stay unique. The most intuitive approach is to query the database first. If the row does not exist, your application proceeds with the insert. It works smoothly in local development and passes single-threaded unit tests. However, the moment your service handles concurrent production traffic, duplicate records inevitably appear.

Why does checking for an existing row before inserting cause duplicates?

When two requests arrive at nearly the identical millisecond, both execute the SELECT query before either has performed the INSERT. Because neither query finds an existing record, both transactions assume the email is available and write duplicate rows. This classic concurrency bug is known as a Time-of-Check to Time-of-Use (TOCTOU) race condition.

Imagine a single-occupancy fitting room with an unlocked door. Two shoppers walk up at the exact same split-second, peek inside, and see that nobody is there. Because both checked before anyone stepped through the doorway, both walk in simultaneously and bump into each other. Checking state before acting without an atomic lock guarantees collisions under concurrent load.

-- Thread A checks state
SELECT id FROM users WHERE email = 'alex@example.com'; -- 0 rows

-- Thread B checks state concurrently
SELECT id FROM users WHERE email = 'alex@example.com'; -- 0 rows

-- Both threads believe the email is free and insert duplicates:
INSERT INTO users (email) VALUES ('alex@example.com');
INSERT INTO users (email) VALUES ('alex@example.com');
Enter fullscreen mode Exit fullscreen mode

Why doesn't SELECT FOR UPDATE stop duplicate rows?

A SELECT ... FOR UPDATE statement locks existing matching rows, but it cannot lock a row that does not exist yet. If two concurrent transactions query for an email that is not in the database, both lock zero rows, receive an empty result, and proceed with their inserts.

Engineers often reach for row-level locking when encountering concurrency bugs, assuming it will serialize access. However, database row locks attach to actual index entries or physical records. If no row matches your filter, standard row-locking mechanisms find nothing to hold onto. Both connections continue unblocked, making the lock completely ineffective for preventing initial duplicate writes.

What is the correct way to prevent duplicate inserts?

The correct solution is enforcing a database-level unique constraint on the column and catching the constraint violation error in your application. Letting the database engine act as the single source of truth guarantees atomicity regardless of how many requests hit your API at the same millisecond.

Here is how common strategies compare when handling simultaneous writes:

Strategy Concurrency Safe? Mechanism Failure Mode
SELECT then INSERT No Application-level check Race condition allows duplicate rows
SELECT FOR UPDATE No Row-level write lock Cannot lock non-existent records
Unique Constraint Yes Database index validation Rejects the second write with an error

Once you add a unique constraint, the storage engine guarantees that only one transaction succeeds. The second transaction triggers a unique violation error (such as PostgreSQL error code 23505). Your application code simply needs to catch that specific database exception and return the appropriate response—matching what you would have returned had the pre-existing row been detected upfront.

Frequently Asked Questions

How should an API respond to a unique constraint violation?

An API should generally return an HTTP 409 Conflict status code or an idempotent HTTP 200 OK, depending on the endpoint's semantics. If the client intends to ensure an entity exists idempotently, catching the duplicate violation and returning the existing resource representation creates a seamless client experience.

Can serializable database transactions prevent duplicates without constraints?

Serializable isolation can detect write conflicts and abort one of the transactions, but it does not remove the need for error handling. Because serializable transactions throw serialization failures when race conditions occur, you still have to catch errors and execute retry logic, making an explicit unique constraint far simpler and more performant.

Is an UPSERT statement better than handling unique constraint errors?

Yes, native UPSERT clauses like INSERT ... ON CONFLICT DO NOTHING in PostgreSQL or INSERT IGNORE in MySQL handle collisions atomically in a single database round-trip. They rely on the underlying unique index while sparing your application from catching database driver exceptions.

Top comments (0)