The code checks whether a user with this email exists. It does not, so the code inserts one. This is correct, obvious, and wrong under any concurrency at all.
Two requests arrive milliseconds apart. Both check, both find nothing, both insert. One succeeds and one hits a unique constraint violation — or, if there is no constraint, you now have two users with the same email and a data problem that will surface much later.
The window between the check and the insert is the bug, and no amount of care in application code closes it.
What changed in 2026
- Upsert support converged. Mainstream databases settled on comparable semantics, even where the syntax differs, which made the pattern portable in practice.
- Idempotency requirements spread. Retry-heavy distributed systems made "apply this write exactly once" a routine need rather than a special case.
- Bulk upsert deadlocks became better understood. The ordering fix moved from folklore to documented practice.
- Sequence gap surprises persisted. The behaviour where conflicts consume identifiers continued to catch teams out, particularly in tests.
What upsert does
A single statement that inserts a row, and if it conflicts with an existing one on a unique constraint, updates instead. Atomic, with no window between checking and acting.
The essential requirement: there must be a unique constraint or unique index to conflict on. The database detects the conflict through that constraint. Without one, there is nothing to detect and no conflict to handle — this is the most common reason an upsert does not behave as expected.
Three broad behaviours are available:
Update on conflict. The common case — insert, or overwrite the existing row's columns with the new values.
Do nothing on conflict. Insert if absent, silently skip if present. Useful for idempotent inserts where you do not want to overwrite anything.
Conditional update. Update only when a condition holds — for instance, only if the incoming version is newer than the stored one. This is how you avoid an out-of-order retry overwriting fresher data with stale data.
That last one matters more than it looks in any system with retries, where a delayed message can arrive after a newer one and quietly undo it.
The sharp edges
Sequence gaps. In many engines, an insert that conflicts has already consumed a value from the sequence backing the primary key. The row is not inserted; the identifier is gone. Your IDs develop gaps proportional to your conflict rate.
This is harmless if IDs are opaque and alarming if anyone treats them as a count or expects contiguity. Tests asserting on specific ID values are the usual casualty.
Bulk upsert deadlocks. Two concurrent batches upserting overlapping keys in different orders will deadlock — batch A locks key 1 then waits for key 2 while batch B holds key 2 and waits for key 1.
The fix is simple and easy to forget: sort each batch by the conflict key before sending it. Every batch then acquires locks in the same order, and the cycle cannot form. This is one of those one-line changes that eliminates an entire class of intermittent production failure.
Triggers fire differently. An upsert that updates fires update triggers, not insert triggers. Logic that assumed every new record passes through an insert trigger will silently miss rows.
Knowing which happened. By default you often cannot tell whether a given row was inserted or updated. Most engines can return that information if you ask — and if your logic depends on it, ask explicitly rather than inferring from an affected-row count, which varies by engine.
Common mistakes
- Select-then-insert. The race this pattern exists to eliminate.
- No unique constraint. Nothing to conflict on, so nothing works.
- Unsorted bulk batches. Intermittent deadlocks under concurrency.
- Unconditional overwrite in a retry-heavy system. A late retry clobbers newer data; guard with a version or timestamp condition.
- Expecting contiguous IDs. Conflicts consume sequence values.
- Assuming insert triggers always fire. Updates take the update path.
- Upserting a huge batch in one statement. A long transaction holding many locks; chunk it.
FAQ
Is upsert the same as idempotency?
It is a tool for achieving it, not the same thing. An upsert keyed on a stable identifier makes repeating a write safe, which is exactly what retry logic needs — see idempotency explained. Idempotency is the property; upsert is one way to get it.
What about MERGE?
Standard SQL's more general statement, supported by a number of engines, capable of insert, update, and delete in one operation. More expressive, more verbose, and historically more prone to subtle concurrency issues in some implementations. For simple insert-or-update, the dedicated upsert syntax is usually clearer.
Does it work with partial unique indexes?
Generally yes, and it is a useful combination — upserting against uniqueness that only applies to live rows, for instance. Check your engine's syntax for specifying which index to conflict on. See partial indexes.
Can it deadlock with a single row?
Not with itself. Deadlocks come from multiple rows locked in inconsistent orders, which is why sorting fixes it.
What about soft-deleted rows?
They still occupy the unique value unless your constraint is partial, so an upsert will update a deleted row rather than inserting a new one. Frequently surprising — see soft delete patterns.
Where to go next
For the retry safety upsert supports, read idempotency explained. For the concurrency anomalies underneath, database isolation levels, and for constraints that apply to a subset of rows, partial indexes.