A user submits the form twice and you get two identical orders. Why did the check-then-insert not prevent it, and how do you fix it in the database?

The interleaving that defeats the check

The code almost certainly reads like this: select any existing order matching this request, and if none is found, insert one. Under concurrency that is two statements with a gap between them, and the gap is where the duplicate is born. Both requests run the SELECT before either has committed its INSERT. Neither sees the other's row, because an uncommitted row is invisible to everybody else at every isolation level worth using. Both conclude there is no order. Both insert. Both commit.

Notice what is not happening. This is not a dirty read, and it is not a lost update where one write overwrites another. It is the absence of a row being read as a fact, when that fact is only true until the other transaction commits. Raising the isolation level to repeatable read does not help and in a sense makes it worse: under snapshot isolation each transaction is guaranteed a stable view of the past, which is precisely the view in which the other order does not exist.

Two isolation-level options do address it, and both are worth knowing because they show what the problem really is.

• PostgreSQL SERIALIZABLE uses serialisable snapshot isolation, which tracks the predicate your SELECT examined and notices that the other transaction inserted a row into that range. Rather than allow the pair to commit, it aborts one with a serialisation failure. The cost is that every transaction in the application must be wrapped in retry logic, because the abort can happen to any of them.
• InnoDB under REPEATABLE READ can block the second insert with a gap lock, but only if the read that preceded it was a locking read — SELECT ... FOR UPDATE. A plain SELECT takes no locks and provides no protection, which is why the same code appears to be safe in a test that used explicit locking and is unsafe in production where it did not.

Both are real answers. Neither is the one to lead with, because both make correctness depend on every future caller writing the query the right way.

Put the invariant in a constraint

The rule you want is "at most one order per request", and rules of that shape belong in a unique index, because an index is the one place in the system with a genuine serialisation point. Two concurrent inserts of the same key cannot both succeed: the second one blocks on the index entry until the first transaction ends, and then either fails with a unique violation because the first committed, or proceeds because the first rolled back. That behaviour is not something you can build out of SELECT and application code, however careful you are, because no amount of care removes the window.

The question is then what the key is. A natural key works where the domain has one: one active subscription per customer, one vote per user per poll, one payment per invoice. But an order is not naturally unique — a customer may legitimately buy the same basket twice in a minute — so heuristics like "same customer and same total within five seconds" reject real orders and are the wrong tool.

What works there is a client-supplied idempotency key: the browser or mobile app generates an identifier once per logical attempt, sends it with the request, and resends the same value on every retry. The server puts a unique index on it, and duplicates collapse regardless of whether the duplication came from a double click, a proxy retry, or a mobile client resuming a request whose response it never received. Scope the key to the customer or the endpoint rather than making it globally unique, so one tenant cannot collide with another.

Writing the insert so a collision is not an error

With the constraint in place, the collision path needs to be deliberate. PostgreSQL lets you express it in the statement:

INSERT INTO orders (idempotency_key, customer_id, total_cents)
VALUES ($1, $2, $3)
-- No error, no aborted transaction. Zero rows returned means somebody else won.
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING order_id;

The detail people miss is that DO NOTHING returns no row when it conflicts, so the caller cannot get the existing order_id from this statement and must follow it with a SELECT on the key. That second read is safe by then, because whichever transaction created the row has committed by the time your statement stopped waiting on it.

Here is where the engine matters, and it is worth saying which one you mean. In PostgreSQL, an unhandled unique violation aborts the entire transaction: every subsequent statement fails until you roll back, so any work already done in that transaction is lost. If you want to catch the violation and continue, you must have set a SAVEPOINT first, or use ON CONFLICT so no error is raised at all. InnoDB behaves differently — by default a duplicate-key error rolls back only the failing statement, leaving the transaction alive to handle it. Code written against MySQL that catches the exception and carries on will therefore break in a non-obvious way when pointed at PostgreSQL, and this is one of the more common porting defects.

Where there is no row to constrain — a job that must not run twice, a file that must be imported once — a PostgreSQL transaction-scoped advisory lock gives you the same serialisation point without a table, released automatically when the transaction ends. It is a mutex keyed on a number you choose, and it is the right tool exactly when the thing being protected is not a row.

Designing what the loser sees

Deduplication is only half the requirement. A caller that retried because it never received the first response does not want a conflict error, it wants the outcome of the original request.

So store the result alongside the key: when a request arrives with a key that already completed, return the original response and the original identifier, with a status that says this is a replay rather than a new creation. That turns the retry from an error the client has to interpret into an idempotent operation, which is what everybody assumed the endpoint was in the first place. The one case that needs thought is a key seen before whose original transaction is still running — the honest answer there is to make the second caller wait on the same constraint and then read the committed result, rather than to invent a partial answer.

Finally, decide the retention. Idempotency keys accumulate, and dropping them after a defined window is fine as long as the window comfortably exceeds the longest retry your clients perform, because a retry arriving after expiry will be treated as new work.

A SELECT that finds nothing is a statement about a moment, not a guarantee about the future. Give the database a unique key it can serialise on, decide in the statement itself what a collision means, and return the original result to whoever retried.