# PostgreSQL error 40P01: deadlock detected

Two transactions each hold a lock the other wants, so PostgreSQL aborts one to break the cycle. Which one it kills is not predictable, so the fix is never in error handling alone — it is acquiring locks in a consistent order, plus retrying the victim.

> Confidence: high · Verified: 2026-08-08 · Status: fresh · Source: https://knowbase.sh/k/postgres-40p01-deadlock-detected

## Error signature

```
ERROR: deadlock detected
```

Codes: 40P01, deadlock_detected

## Problem

A transaction fails with deadlock detected under concurrent load and succeeds when retried or when run alone. It appears at a rate proportional to traffic, so it is invisible in development and routine in production. The statement named in the error is often not the one at fault — it is simply the one PostgreSQL chose to abort.

## Root cause

- **Two code paths lock the same rows in opposite orders** _(primary)_
  - One transaction updates A then B, another updates B then A. Each holds what the other needs. This is the textbook case and needs no explicit LOCK statement — ordinary row-level UPDATEs are enough.
  - How to tell: The DETAIL line names two processes each waiting on the other, and the two statements touch the same tables in reverse order
- **A batch operation updates rows in unpredictable order** _(primary)_
  - An UPDATE or DELETE over a set of rows locks them in whatever order the plan produces. Two concurrent batches over overlapping sets can interleave and deadlock even though the application code looks sequential.
  - How to tell: Both statements in the DETAIL are the same bulk statement, differing only in parameters or in the rows matched
- **A foreign key forces a lock on the parent row** _(common)_
  - Inserting a child row takes a lock on the referenced parent to keep the reference valid. Two transactions inserting children of each other's parents deadlock without either touching the other's table directly.
  - How to tell: The DETAIL mentions a table the statement does not name, and that table is the target of a foreign key
- **Lock upgrade from a read to a write** _(common)_
  - A transaction that SELECTs a row and later updates it holds a weaker lock first and needs a stronger one after. Two transactions doing this on the same row block each other symmetrically.
  - How to tell: Both transactions read the same row before writing it, and adding FOR UPDATE to the initial SELECT removes the deadlock
- **Long-running transactions widen the window** _(edge)_
  - Deadlock probability scales with how long locks are held. A transaction kept open across an external API call or user input turns a rare interleaving into a frequent one — the cause is duration, not order.
  - How to tell: Deadlock rate falls sharply when transaction duration is reduced, without any change to lock ordering

## Solution

1. Read the DETAIL and CONTEXT lines in the server log, not just the error. They name both processes, both statements and both locks — the error message alone names only the victim.

```bash
grep -A6 'deadlock detected' /var/log/postgresql/postgresql-*.log | tail -40
```

   Note: Set log_lock_waits on so waits that do not yet deadlock are also recorded; they are the early warning for the same lock ordering problem.
2. Establish a single canonical lock order and apply it everywhere. Sorting the keys before touching them is the cheapest way to guarantee it.

```sql
-- 🔴 order depends on which transfer arrives first
UPDATE accounts SET balance = balance - 100 WHERE id = :from;
UPDATE accounts SET balance = balance + 100 WHERE id = :to;

-- ✅ always lock the lower id first, so two transfers cannot interleave
SELECT id FROM accounts
 WHERE id IN (:from, :to)
 ORDER BY id
   FOR UPDATE;
```

3. For bulk statements, impose an order explicitly rather than trusting the plan.

```sql
UPDATE items SET status = 'done'
 WHERE id IN (SELECT id FROM items WHERE status = 'queued' ORDER BY id FOR UPDATE);
```

4. Retry the aborted transaction. Deadlock is a transient, expected condition under concurrency, so 40P01 should be retried rather than surfaced to the user — with backoff and jitter, and a cap.

```javascript
// Retry only 40P01 and 40001 — never a constraint violation
const RETRYABLE = new Set(["40P01", "40001"]);

for (let attempt = 0; attempt < 3; attempt++) {
  try {
    return await db.tx(work);
  } catch (err) {
    if (!RETRYABLE.has(err.code) || attempt === 2) throw err;
    await sleep(50 * 2 ** attempt * (0.5 + Math.random()));
  }
}
```

   Note: The retry must re-run the whole transaction. Resuming mid-transaction is not possible — the abort rolled everything back.
5. Shorten transactions. Never hold one open across a network call, a queue publish, or anything waiting on a human.
6. Leave deadlock_timeout alone unless you have measured a reason. It is one second by default and controls how long a waiter sits before checking for a cycle — raising it hides real deadlocks for longer rather than preventing them.

**Verify:** Under the concurrency that previously produced them, the PostgreSQL log records no new deadlock detected entries over a full traffic cycle, and any that remain are retried transparently rather than reaching the caller.

**If that fails:** Where a consistent order genuinely cannot be imposed — independent services touching shared tables — serialise the conflicting operation behind an advisory lock so contention becomes a queue rather than a cycle.

## Applies to

- PostgreSQL: 9.0 and later — Detection and automatic abort are server behaviour; deadlock_timeout defaults to 1s.
- Platforms: self-hosted, managed PostgreSQL

## Not applicable to

- SQLSTATE 40001 serialization_failure, which is an isolation-level conflict rather than a lock cycle
- Lock waits that eventually succeed, which are contention rather than deadlock and never abort
- MySQL error 1205 lock wait timeout, a timeout on a single lock rather than a detected cycle
- Application-level deadlocks between processes, which the database cannot see or resolve

## Evidence

1. [PostgreSQL — Explicit Locking, Deadlocks](https://www.postgresql.org/docs/current/explicit-locking.html) — The PostgreSQL Global Development Group (official-docs), read 2026-08-08
   Supports: That PostgreSQL detects the cycle and aborts one transaction unpredictably, that row-level locks alone are enough to deadlock without explicit LOCK statements, and that consistent lock ordering plus retrying the victim is the prescribed defence.
   > The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order.
2. [PostgreSQL — Lock Management (deadlock_timeout)](https://www.postgresql.org/docs/current/runtime-config-locks.html) — The PostgreSQL Global Development Group (official-docs), read 2026-08-08
   Supports: That deadlock detection is deferred by deadlock_timeout, one second by default, and that raising it delays reporting of real deadlocks rather than avoiding them.
   > This is the amount of time to wait on a lock before checking to see if there is a deadlock condition.
3. [PostgreSQL Error Codes (Appendix A)](https://www.postgresql.org/docs/current/errcodes-appendix.html) — The PostgreSQL Global Development Group (official-docs), read 2026-08-08
   Supports: That 40P01 is deadlock_detected in class 40, Transaction Rollback — the class whose members are by definition safe to retry.
   > deadlock_detected

## Confidence

high — The detection mechanism, the unpredictability of which transaction is aborted, the lock-ordering defence and the deadlock_timeout default are all quoted from PostgreSQL's own documentation. The foreign-key and lock-upgrade cases follow from documented locking behaviour rather than from sentences about deadlocks specifically, and the retry code is illustrative.

---

Retrieved from https://knowbase.sh/k/postgres-40p01-deadlock-detected · knowbase · CC-BY-4.0