Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

Postgres Deadlock Detected 40P01: Reading pg_locks and Lock Order

The executor locks rows in plan order, not the order you listed them. A consistent lock order, the pg_locks query that proves the cycle, and a retry that works.

~5 min readSQL
#PostgreSQL#Locks#Debugging#Transactions
3D illustration of two arrows looping in opposite directions around a database cylinder, crossing at a red lock node

Introduction & Quick Summary

Symptom. Postgres logs ERROR: deadlock detected with SQLSTATE 40P01, followed by DETAIL: Process 4211 waits for ShareLock on transaction 8890; blocked by process 9021. and HINT: See server log for query details. One of the two transactions is rolled back, the client gets an exception, and the same pair of requests deadlocks again under load — usually on a batch update, an upsert, or a worker that claims rows.

Cause. Postgres does not lock rows in the order a statement lists them; it locks them in the order the executor reaches them, which is index order, heap order, or the order rows arrive from a join. Two transactions that touch the same rows in opposite orders therefore each hold one lock and wait for the other. Something else must break the cycle, so after deadlock_timeout (1 s by default) the server walks the wait-for graph, finds the cycle, and aborts the youngest transaction with 40P01.

Fix. Impose a global lock order — sort the ids in every batch update, acquire advisory locks by sorted key, and split multi-row updates so the executor also touches rows in that order. Keep transactions short, never hold one across user input or an HTTP call, use SELECT … FOR UPDATE SKIP LOCKED for queue-style claiming, and wrap the affected writes in a retry that reruns the whole transaction on 40P01. Read pg_locks and pg_blocking_pids() to confirm the cycle instead of guessing.

Core Mechanics, Signatures & Reproducible Setup

Two sessions deadlock on two rows in opposite order:

sql
-- session 1 BEGIN; UPDATE accounts SET balance = balance - 10 WHERE id = 1; -- session 2 BEGIN; UPDATE accounts SET balance = balance + 10 WHERE id = 2; -- session 1 UPDATE accounts SET balance = balance + 10 WHERE id = 2; -- waits on session 2 -- session 2 UPDATE accounts SET balance = balance - 10 WHERE id = 1; -- cycle: one is aborted

A given UPDATE on a multi-row set does not define an order you can rely on unless the plan is a sort. Force it:

sql
UPDATE accounts a SET balance = a.balance + v.delta FROM (VALUES (1, -10), (2, 10)) AS v(id, delta) WHERE a.id = v.id ORDER BY a.id; -- not valid syntax for UPDATE ... FROM

ORDER BY is not allowed on UPDATE, so the ordering has to come from the source: a sorted CTE materialised before the update, or one statement per id in sorted order inside the transaction.

sql
WITH ordered AS ( SELECT * FROM jsonb_to_recordset($1::jsonb) AS x(id int, delta numeric) ORDER BY id ) UPDATE accounts a SET balance = a.balance + o.delta FROM ordered o WHERE a.id = o.id;

Practical diagnostics — the blocked side first:

sql
SELECT a.pid, a.state, a.wait_event_type, a.wait_event, l.locktype, l.mode, l.granted, l.relation::regclass, pg_blocking_pids(a.pid) AS blockers, left(a.query, 80) AS query FROM pg_locks l JOIN pg_stat_activity a USING (pid) WHERE NOT l.granted ORDER BY a.query_start;

And the whole tree while the issue is live:

sql
SELECT pid, pg_blocking_pids(pid) AS blockers, state, wait_event, now() - xact_start AS xact_age, left(query, 120) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

Enable the server-side record so the next incident has evidence: log_lock_waits = on logs every wait longer than deadlock_timeout, and log_min_messages = warning keeps 40P01 in the log even when the application swallows it.

Key Comparison Table

DetectionWhere it appearsWhat it means
40P01 deadlock_detectedClient error, server logCycle found, this transaction was the victim
40001 serialization_failureSERIALIZABLE or REPEATABLE READConcurrent update conflict, retry the transaction
55P03 lock_not_availableNOWAIT, SKIP LOCKED, lock_timeoutLock busy, not a cycle
pg_stat_database.deadlocksMonitoringRate of victims over time
log_lock_waitsServer logLong waits before they become deadlocks
SituationLock order fixAlternative
Batch update of many idsSort ids, chunkOne statement with a sorted CTE
Upsert on several unique indexesFix index order in the schemaSingle arbiter constraint, retry
Job queue claimFOR UPDATE SKIP LOCKEDAdvisory lock per job id
Parent/child insert (FK)Insert parents in ascending idDefer the FK, or pre-lock parents sorted
Mixed DDL and trafficSET lock_timeout before DDLRun DDL in a maintenance window
Distributed workersSorted advisory keysA single coordinator per shard

Practical Edge Cases, Error Fixes & Deep Dive Code Examples

1. Two workers updating the same rows in different orders

The classic: worker A processes [5, 3], worker B processes [3, 5]. Both statements are single UPDATE … WHERE id = ANY($1) calls, so the executor walks the index in whatever order it likes and the two sessions interleave.

python
def apply_updates(conn, rows): rows = sorted(rows, key=lambda r: r["id"]) # one global order with conn.transaction(): for r in rows: conn.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (r["delta"], r["id"]))

Per-row statements in sorted order are slower than a set-based update but they make the order explicit; for large batches, chunk to 500 ids and keep chunks sorted.

2. Upserts that deadlock on insert

INSERT … ON CONFLICT DO UPDATE takes a speculative insertion lock, then a row lock. Two transactions inserting different rows that collide on different unique indexes acquire locks in the schema's index order, so a table with UNIQUE(email) and UNIQUE(username) deadlocks when one transaction inserts (a@x, bob) and the other (b@y, bob).

sql
-- One arbiter, and accept that the other unique index may still conflict INSERT INTO users (email, username) VALUES ($1, $2) ON CONFLICT (email) DO UPDATE SET username = EXCLUDED.username;

If both constraints must be arbiters, retry on 40P01 — it is the documented behaviour, not a bug.

3. Queue claiming without deadlocks

SELECT … FOR UPDATE on the oldest jobs makes every worker queue on the same row. SKIP LOCKED lets each worker take a different job and removes the contention entirely:

sql
WITH claimed AS ( SELECT id FROM jobs WHERE status = 'queued' ORDER BY created_at FOR UPDATE SKIP LOCKED LIMIT 10 ) UPDATE jobs j SET status = 'running', started_at = now() FROM claimed c WHERE j.id = c.id RETURNING j.id, j.payload;

SKIP LOCKED is not for read-modify-write of shared business rows: skipping means you may read stale data by design.

4. Foreign keys that lock the parent

Inserting a child row takes a FOR KEY SHARE lock on the parent. Two transactions inserting children for parents 1 and 2 in opposite order deadlock exactly like an UPDATE pair, and the failing statement is an innocent INSERT.

sql
-- deterministic parent order inside the transaction SELECT id FROM parent WHERE id = ANY($1) ORDER BY id FOR KEY SHARE; -- then insert the children

Deferring the constraint (DEFERRABLE INITIALLY DEFERRED) moves the check to commit and can make the deadlock surface later rather than remove it; the ordering fix is what removes it.

5. Advisory locks held by a lock manager

pg_advisory_lock is session-scoped and unordered. A migration helper that locks [table_a, table_b] in one process and [table_b, table_a] in another deadlocks with no table lock involved.

sql
SELECT pg_advisory_xact_lock(k) FROM unnest($1::int[]) WITH ORDINALITY AS t(k, ord) ORDER BY k; -- sorted, transaction-scoped, auto-released

pg_advisory_xact_lock releases at commit or rollback, so a crashed worker cannot leak it. Remember that a transaction-mode pooler such as PgBouncer or Supavisor invalidates session-level advisory locks between statements.

6. DDL waiting behind a long read

ALTER TABLE needs ACCESS EXCLUSIVE and queues behind the oldest transaction, and every later query queues behind the ALTER — a lock pile-up that looks like a deadlock storm. Put a ceiling on the wait:

sql
SET lock_timeout = '3s'; ALTER TABLE orders ADD COLUMN note text;

In a migration tool, set lock_timeout for the session and retry the DDL a few times rather than holding the queue.

Common Compilation/Runtime Mistakes & Version Gotchas

  • Retrying the statement, not the transaction. After 40P01 the transaction is aborted; the failed statement cannot be replayed inside it. Retry from BEGIN, with backoff and a cap, and make the handler idempotent.
  • deadlock_timeout tuned as a cure. Lowering it shortens the wait before detection but aborts innocent transactions sooner; raising it wastes time. Fix the order, keep detection at the default.
  • SELECT … FOR UPDATE on a join. Locking rows from two tables in one statement multiplies the orderings. Lock each table's rows in a separate, sorted statement.
  • idle in transaction sessions. Any application that opens a transaction and then calls an external API keeps locks for seconds. Set idle_in_transaction_session_timeout and restructure the call order.
  • Assuming row locks queue. Postgres row locks do not have a queue: a later, smaller request can overtake a big batch. This is why the same batch deadlocks intermittently instead of always.
  • SERIALIZABLE semantics. Under SERIALIZABLE, 40001 is the normal conflict signal and is expected under contention. Both 40P01 and 40001 need an identical retry path in the client.
  • Supabase and poolers. Through a transaction-mode pooler, session-level features (SET persistence, advisory session locks, prepared statements in some modes) do not carry across statements. Use transaction-scoped locks and per-transaction settings.

Related tools. Reformat and eyeball the pg_locks and pg_blocking_pids() queries with the SQL formatter before pasting them into a runbook, correlate log timestamps with transaction ages using the timestamp converter, and pretty-print the DETAIL and CONTEXT JSON when your platform logs them as structured payloads with the JSON formatter.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.