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.

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:
-- 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 abortedA given UPDATE on a multi-row set does not define an order you can rely on unless the plan is a sort. Force it:
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 ... FROMORDER 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.
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:
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:
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
| Detection | Where it appears | What it means |
|---|---|---|
40P01 deadlock_detected | Client error, server log | Cycle found, this transaction was the victim |
40001 serialization_failure | SERIALIZABLE or REPEATABLE READ | Concurrent update conflict, retry the transaction |
55P03 lock_not_available | NOWAIT, SKIP LOCKED, lock_timeout | Lock busy, not a cycle |
pg_stat_database.deadlocks | Monitoring | Rate of victims over time |
log_lock_waits | Server log | Long waits before they become deadlocks |
| Situation | Lock order fix | Alternative |
|---|---|---|
| Batch update of many ids | Sort ids, chunk | One statement with a sorted CTE |
| Upsert on several unique indexes | Fix index order in the schema | Single arbiter constraint, retry |
| Job queue claim | FOR UPDATE SKIP LOCKED | Advisory lock per job id |
| Parent/child insert (FK) | Insert parents in ascending id | Defer the FK, or pre-lock parents sorted |
| Mixed DDL and traffic | SET lock_timeout before DDL | Run DDL in a maintenance window |
| Distributed workers | Sorted advisory keys | A 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.
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).
-- 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:
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.
-- deterministic parent order inside the transaction
SELECT id FROM parent WHERE id = ANY($1) ORDER BY id FOR KEY SHARE;
-- then insert the childrenDeferring 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.
SELECT pg_advisory_xact_lock(k)
FROM unnest($1::int[]) WITH ORDINALITY AS t(k, ord)
ORDER BY k; -- sorted, transaction-scoped, auto-releasedpg_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:
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_timeouttuned 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 UPDATEon 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 transactionsessions. Any application that opens a transaction and then calls an external API keeps locks for seconds. Setidle_in_transaction_session_timeoutand 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.
SERIALIZABLEsemantics. UnderSERIALIZABLE,40001is 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 (
SETpersistence, 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 toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesPostgres JSONB Indexing: GIN vs Expression B-Tree
GIN answers containment, not equality, and an expression B-tree is the reverse. Choose by operator, match the expression exactly, and prove it with EXPLAIN.
Postgres too many clients: Pool Sizing and Connection Exhaustion
Each process caches its own pool and nothing coordinates them. Divide the ceiling by replicas, put a transaction-mode pooler in front, and stop idle-in-transaction sessions from holding slots.
Postgres RLS 42P17: infinite recursion detected in policy
A policy on members that reads members re-enters itself. One security definer function with a pinned search_path ends the recursion and leaves the rest of the policy intact.
SQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.