ON CONFLICT DO UPDATE reads like a single atomic step: try the insert, and if it collides, update instead. Operationally it isn't one step, it's two, with a wait and a lock in between — and two concurrent upserts hitting the same keys in opposite order can deadlock each other the same way two UPDATE statements can, even though neither transaction wrote an explicit SELECT ... FOR UPDATE anywhere.
What happens on a real conflict
INSERT INTO inventory (sku, qty)
VALUES ('SKU-100', 5)
ON CONFLICT (sku) DO UPDATE
SET qty = inventory.qty + EXCLUDED.qty;
If no row with that sku exists, this is a plain insert. If a row exists, Postgres doesn't just silently switch to an UPDATE plan — it has to find the conflicting row via the unique index backing the ON CONFLICT target, and that row has to be locked before the DO UPDATE can safely apply. If another transaction is concurrently inserting or updating that same row and hasn't committed yet, your statement blocks, waiting for that transaction to finish, exactly like an explicit SELECT ... FOR UPDATE would. This is documented Postgres behavior, not an edge case: a conflicting INSERT ON CONFLICT DO UPDATE against a row another in-flight transaction is touching will wait for that transaction to commit or roll back, then re-check whether the conflict still applies before proceeding.
Why that turns into a deadlock
A lock wait on its own just means "slower," not "stuck" — unless two transactions are waiting on each other. Consider two batch jobs upserting the same two SKUs in opposite order, each inside one multi-statement transaction:
-- Transaction A
BEGIN;
INSERT INTO inventory (sku, qty) VALUES ('SKU-100', 1)
ON CONFLICT (sku) DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;
INSERT INTO inventory (sku, qty) VALUES ('SKU-200', 1)
ON CONFLICT (sku) DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;
COMMIT;
-- Transaction B, running concurrently
BEGIN;
INSERT INTO inventory (sku, qty) VALUES ('SKU-200', 1)
ON CONFLICT (sku) DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;
INSERT INTO inventory (sku, qty) VALUES ('SKU-100', 1)
ON CONFLICT (sku) DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;
COMMIT;
If both reach their second statement at close to the same time, A is holding the lock on SKU-100 and waiting for B to release SKU-200, while B is holding SKU-200 and waiting for A to release SKU-100. Neither can proceed. After deadlock_timeout (1 second by default), Postgres's deadlock detector runs, finds the cycle, and kills one transaction with ERROR: deadlock detected, letting the other complete. The error is correct and expected — it's the application's job to retry the aborted transaction, not a sign Postgres is broken.
This is the exact same lock-ordering deadlock pattern that plain UPDATE statements can hit; ON CONFLICT DO UPDATE doesn't introduce a new kind of risk, it just hides the row lock behind syntax that looks like a single safe statement, so people reach for it in batch-upsert code without applying the lock-ordering discipline they'd automatically apply to a hand-written multi-row UPDATE.
The fix: sort the keys before you upsert
-- Both transactions now acquire locks in the same order, so neither
-- can be waiting on a row the other is also waiting on.
INSERT INTO inventory (sku, qty)
SELECT * FROM (VALUES ('SKU-100', 1), ('SKU-200', 1)) AS v(sku, qty)
ORDER BY sku
ON CONFLICT (sku) DO UPDATE
SET qty = inventory.qty + EXCLUDED.qty;
If every transaction in your workload upserts rows in a consistent key order — alphabetical, numeric, whatever, as long as it's the same order everywhere — the cycle above can't form. One transaction will always be "ahead" in the shared ordering and the other will simply wait its turn, not wait on something that's simultaneously waiting on it. Batching multiple rows into a single multi-row INSERT ... ON CONFLICT (as above) is also strictly better than issuing N separate single-row upserts in a loop: fewer round trips, and the row locks for the whole batch are acquired and released as a unit instead of individually, which shrinks the window where a deadlock cycle can form at all.
Why you need a real unique constraint, not just an index-like query pattern
ON CONFLICT (sku) only works if sku is backed by an actual unique index or constraint — Postgres needs something to detect the conflict against, not just a column you query by:
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
If you hit this, the fix isn't to add WHERE clauses or rewrite the query — it's ALTER TABLE inventory ADD CONSTRAINT inventory_sku_key UNIQUE (sku); first. There's no way around needing the constraint; ON CONFLICT is conflict detection against a declared uniqueness guarantee, not a general-purpose "does a similar row exist" check.
Rule of thumb: treat every multi-row
ON CONFLICT DO UPDATEbatch the way you'd treat a multi-rowUPDATEwith manual locking — sort the keys consistently across every call site that touches the same table, and batch rows into one statement instead of a loop of single-row upserts. If your application already retries on serialization/deadlock errors (it should, regardless), an occasional deadlock here is a correctly-functioning safety mechanism, not a bug — but consistent key ordering means you'll see it rarely instead of under every concurrent load test.