Frontier intelligence. Powered by Claude.

The fix

Sort each batch before it writes, so every transaction takes the parent locks in the same order. No schema change, no lock hints:

WITH ordered AS (
  SELECT order_id, amount
  FROM   unnest($1::bigint[], $2::numeric[])
           AS t(order_id, amount)
  ORDER BY order_id      -- the whole fix is this line
)
INSERT INTO order_lines (order_id, amount)
SELECT order_id, amount FROM ordered;

Why it happened

Classic cycle — but the detail that matters is the lock mode: ShareLock on a foreign-key parent, not on the rows either statement wrote. Nothing in your code locked those parents. Postgres took the FK locks for you, in the order the rows happened to arrive in each batch.

Deadlocks need a cycle, and a global lock ordering makes one impossible — so this removes the class of bug rather than making it rarer. It costs one sort per batch on an already-indexed column.

SELECT ... FOR UPDATE on the parents would also work, but it serialises batches that never actually conflict: you would trade the deadlock for a throughput ceiling. What I would not do is raise deadlock_timeout — that hides the symptom and makes the next one harder to catch.

Cause. Concurrent batch inserts took foreign-key locks on the same orders rows in different orders. Postgres acquired those locks implicitly, so nothing in our code looked like it was locking anything. Two batches, two orderings, one cycle. Postgres killed the loser, which is the behaviour we saw as "random" 500s on checkout between 22:41 and 23:06.

Fix. Sort each batch by order_id before writing. One line. Deployed and verified against a replay of last night's traffic — zero deadlocks across 40k inserts.

Why it won't recur. A consistent global lock order makes a deadlock cycle impossible for this path, rather than less likely. This is a removal, not a mitigation.

Follow-up. The same pattern exists in the refunds writer. Not firing today because volume is low, but it is the same bug waiting. Ticket filed.