PlaybookJun 24, 20265 min read

Postgres deadlocks are retryable. Lock ordering makes them rare.

40P01 is Postgres doing its job: it picked a victim so the other transaction could finish. Retry the victim, then fix the order.

Start reading ↓

Two transactions each hold a lock the other wants. After deadlock_timeout (1s by default) Postgres aborts one of them with 40P01. The other succeeds.

40P01Error↻ retryableDeadlock detectedTwo transactions each hold a row lock the other needs, so Postgres aborted one after deadlock_timeout (1s). Lock rows in a consistent order and retry the aborted transaction.

Retry the whole transaction

Not the last statement: the aborted transaction rolled back everything. Wrap the full unit of work.

transfer.ts
await withRetry(() => prisma.$transaction(async (tx) => {
  await tx.account.update({ where: { id: a }, data: { balance: { decrement: amt } } });
  await tx.account.update({ where: { id: b }, data: { balance: { increment: amt } } });
}), { tries: 3 });

Then lock in a consistent order

transfer.ts
const [first, second] = [a, b].sort();
await tx.$queryRaw`SELECT 1 FROM account WHERE id IN (${first}, ${second}) ORDER BY id FOR UPDATE`;
Tip

Set log_lock_waits = on. Every wait longer than deadlock_timeout gets logged with both queries, which is usually enough to see the ordering bug.