What does "postgres deadlock detected" actually mean?
When Postgres reports ERROR: deadlock detected, it has found a cycle in the wait-for graph. Each transaction in the cycle holds a lock that another one needs, and each is waiting on a lock held further along. Transaction A waits on B, B waits on A, or a longer chain closes back on itself. None of them can make progress, and waiting longer will not change that.
Postgres does not check for cycles on every lock request, because that would be expensive. A backend that has been blocked for longer than deadlock_timeout (one second by default) runs the detector. If it finds a cycle, it aborts one transaction with SQLSTATE 40P01, which releases that transaction's locks so the others can continue. The server log entry names the processes involved and the locks each was waiting for.
The victim is not guaranteed. It is often the backend that ran the check, but which backend that is depends on timing. Your code cannot assume the "background job side" or the "request side" is the one that fails. Every participant must be able to handle 40P01 and retry.
Stricter isolation does not help. Deadlocks come from row and table locks, which every isolation level takes. SERIALIZABLE keeps those deadlocks and adds 40001 serialization failures on top.
In a monolith, many code paths touch the same tables. A recurring deadlock there is a design signal, not bad luck. It means two paths acquire the same locks in different orders.
How does a lock-ordering cycle form, step by step?
Take two sessions and two rows in the same table. Each UPDATE takes a row-level lock that is held until its transaction commits or rolls back.
- Session A updates row 1 and holds its lock.
- Session B updates row 2 and holds its lock.
- Session A tries to update row 2. B holds it, so A waits for B's transaction to finish.
- Session B tries to update row 1. A holds it, so B waits for A.
Step 4 closes the loop. Neither transaction can finish, because each one is waiting for the other to release a lock. Neither will release it, because releasing happens only at commit or rollback. Waiting longer cannot fix this. After deadlock_timeout passes, Postgres checks the wait-for graph, finds the cycle and aborts one transaction with 40P01. The other transaction then proceeds.
In a monolith, the same pattern usually spans two tables and two modules. Order fulfillment locks the orders row and then decrements inventory. Inventory adjustment, such as a restock or cycle count, locks the inventory row and then updates the affected orders to clear a backorder flag. Each path works correctly when it runs alone and passes its own tests. They deadlock only when they run at the same time against the same order and SKU.
Fulfillment (A) Adjustment (B)
holds orders#42 holds inventory#SKU-7
| |
| waits for inventory#SKU-7 | waits for orders#42
v v
[ held by B ] <---------------------> [ held by A ]
The cycle forms because the code has no shared rule for which table is locked first.
-- Reproduce the fulfillment/adjustment deadlock with two psql sessions.
-- Run each step in the session shown, in order.
-- Setup (either session)
CREATE TABLE orders (id int PRIMARY KEY, sku text, backordered bool);
CREATE TABLE inventory (sku text PRIMARY KEY, qty int);
INSERT INTO orders VALUES (42, 'SKU-7', true);
INSERT INTO inventory VALUES ('SKU-7', 10);
-- Step 1, session A (fulfillment): lock the order row first
BEGIN;
UPDATE orders SET backordered = false WHERE id = 42;
-- Step 2, session B (adjustment): lock the inventory row first
BEGIN;
UPDATE inventory SET qty = qty + 5 WHERE sku = 'SKU-7';
-- Step 3, session A: needs inventory#SKU-7, held by B -> A blocks
UPDATE inventory SET qty = qty - 1 WHERE sku = 'SKU-7';
-- Step 4, session B: needs orders#42, held by A -> cycle closes.
-- After deadlock_timeout (1s by default), one session gets:
-- ERROR: deadlock detected (SQLSTATE 40P01)
-- and is rolled back; the other session's UPDATE then completes.
UPDATE orders SET backordered = false WHERE id = 42;
-- Finish whichever transaction survived
COMMIT;
-- Fix: both modules take locks in the same order (orders, then inventory),
-- for example by locking the order row up front in the adjustment path:
-- BEGIN;
-- SELECT 1 FROM orders WHERE id = 42 FOR UPDATE;
-- UPDATE inventory SET qty = qty + 5 WHERE sku = 'SKU-7';
-- UPDATE orders SET backordered = false WHERE id = 42;
-- COMMIT;
A two-session psql script that reproduces the orders/inventory lock-order deadlock step by step, then shows the fix: the adjustment path locks tables in the same order as fulfillment.
Where do hidden lock acquisitions come from?
Application code is only part of what decides lock order. Postgres and the layers around it take row and table locks that never appear in the code you review, and those locks take part in cycles in exactly the same way.
| Source | Lock taken | Why it is easy to miss |
|---|---|---|
| Foreign key check on child INSERT/UPDATE | FOR KEY SHARE on the referenced parent row |
No statement touches the parent table. It conflicts with SELECT ... FOR UPDATE, DELETE, or key-column updates on that parent. |
| Trigger writing to another table | Row locks on audit, counter or summary rows | The trigger runs inside your transaction, so its writes are part of your lock order even though the code never mentions those tables. |
| ORM cascades and flush ordering | Row locks in whatever order the ORM chooses to emit SQL | The unit of work orders statements by its own dependency rules, not by the order your code mutated objects. |
Multi-row UPDATE/DELETE |
Row locks in the order the plan visits rows | SQL defines no row visit order. A sequential scan and an index scan lock the same rows in a different sequence. |
LOCK TABLE or SELECT ... FOR UPDATE added for one feature |
Table lock or explicit row locks | The lock was correct for its own feature, but it now sits in front of locks other code paths acquire in the opposite order. |
The plan-dependent case is the hardest to catch because nothing in the code changes. A batch UPDATE ... WHERE status = 'pending' can run safely for months, then start deadlocking after someone adds an index or the data distribution shifts enough for the planner to pick a different scan. Two concurrent batches then visit overlapping rows in opposite orders.
How do you diagnose a deadlock from logs, pg_locks and pg_blocking_pids()?
When Postgres aborts a transaction, the server log records which processes formed the cycle. A typical DETAIL line reads like "Process 4121 waits for ShareLock on transaction 88123; blocked by process 4187," followed by the reverse edge from 4187 back to 4121. A ShareLock on transaction means a row-level conflict: one session is waiting for the other's transaction to finish so it can touch a row that transaction already modified. A wait on a relation shows its OID, and you can resolve that OID to a table name with a ::regclass cast. The server log also includes each process's query text, which the client never receives. That makes it the best evidence you will get, because it shows both sides of the cycle.
Real deadlocks are resolved within about deadlock_timeout (default one second). By the time anyone looks, both sessions have moved on, so a live snapshot rarely catches one. You usually reconstruct the cycle from the logs.
To catch the near-misses, set log_lock_waits = on. Any lock wait that outlasts deadlock_timeout is then logged, which often shows the same two code paths contending long before they actually deadlock.
During a live contention spike, join pg_locks to pg_stat_activity on pid and filter to NOT granted. Call pg_blocking_pids(pid) for each waiter to get its blockers, then pull query and application_name for both waiters and blockers. application_name is what maps a session back to a specific service or job.
Tuning deadlock_timeout only shifts costs. Lowering it runs the detector more often during ordinary short waits, which adds overhead. Raising it lets deadlocked sessions hold locks longer before one is cancelled. Neither change removes the inconsistent lock order that causes the deadlock.
-- 1. Log any lock wait longer than deadlock_timeout to catch near-misses.
-- Requires superuser. Both settings take effect on reload, not restart.
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '1s'; -- the default, shown explicitly
SELECT pg_reload_conf();
-- 2. During a live contention spike, list each ungranted lock with its blockers.
-- pg_blocking_pids() returns an array, so unnest it to get one row per
-- waiter/blocker pair.
SELECT
l.pid AS waiting_pid,
wa.application_name AS waiting_app,
l.locktype,
l.mode AS wanted_mode,
l.relation::regclass AS relation, -- NULL for transactionid waits
l.transactionid, -- set for row-level conflicts
now() - wa.query_start AS waiting_for,
wa.query AS waiting_query,
b.pid AS blocking_pid,
ba.application_name AS blocking_app,
ba.state AS blocking_state, -- 'idle in transaction' is common
ba.query AS blocking_query -- last statement run, which may not
-- be the one that took the lock
FROM pg_locks l
JOIN pg_stat_activity wa ON wa.pid = l.pid
CROSS JOIN LATERAL unnest(pg_blocking_pids(l.pid)) AS b(pid)
JOIN pg_stat_activity ba ON ba.pid = b.pid
WHERE NOT l.granted
ORDER BY waiting_for DESC;
This example turns on log_lock_waits so near-misses show up in the server log, then queries pg_locks, pg_stat_activity and pg_blocking_pids() to list each waiter and its blockers with their query text and application_name.
How do you fix lock ordering and design transactions to avoid deadlocks?
The fix lives in application code, so it belongs to the backend and platform engineers who own the shared schema. The goal is to make every transaction acquire locks in the same global order, so a cycle cannot form.
Lock deterministically before mutating
When a transaction will modify several rows, lock them first with SELECT ... FOR UPDATE and an ORDER BY on the primary key. Postgres sorts the rows before locking them, so two transactions touching overlapping sets will both try the lowest key first. One waits instead of deadlocking. Apply the same rule to batch updates by sorting the keys in the application before building the statement.
Across tables, define a project-wide touch order, such as accounts before orders before order_items, and document it next to the schema.
Design transactions to hold less
- Keep transactions short. The longer locks are held, the more chances other transactions have to overlap with them.
- Never hold locks across network calls or user think time.
- Take the strongest lock you need up front. Two sessions holding
FOR SHAREon the same row that both try to upgrade toUPDATEwill deadlock each other. - Set
lock_timeoutso a blocked statement fails fast with a clear error instead of queueing.
Trade-offs
Pre-locking lengthens hold time and serializes traffic on hot rows, which turns occasional deadlocks into routine waiting. A global order is also a discipline problem. ORMs and generic repositories often flush writes in an order based on object state rather than key order, so the real acquisition sequence can drift from the documented one without anyone noticing.
-- Transfer funds and mark the related orders paid.
-- Every transaction that touches these tables follows the same order:
-- accounts -> orders -> order_items, and ascending primary key within each table.
BEGIN;
-- Fail fast with an error instead of queueing behind a long-held lock.
-- SET LOCAL applies only to this transaction.
SET LOCAL lock_timeout = '2s';
-- Step 1: lock every account row we will mutate, lowest id first.
-- Concurrent transfers 7->42 and 42->7 both request id 7 first,
-- so one waits for the other instead of forming a cycle.
SELECT id
FROM accounts
WHERE id IN (42, 7)
ORDER BY id
FOR UPDATE;
-- Step 2: lock orders only after accounts, again in key order.
-- FOR UPDATE is taken here up front. Taking FOR SHARE now and
-- upgrading later is the pattern that deadlocks.
SELECT id
FROM orders
WHERE id IN (1003, 1001)
ORDER BY id
FOR UPDATE;
-- Step 3: mutate. The rows are already locked, so these statements
-- cannot add new lock-order edges. No network calls run while locks are held.
UPDATE accounts SET balance = balance - 100.00 WHERE id = 7;
UPDATE accounts SET balance = balance + 100.00 WHERE id = 42;
UPDATE orders SET status = 'paid' WHERE id IN (1001, 1003);
COMMIT;
A short transaction that sets lock_timeout and pre-locks rows with SELECT ... FOR UPDATE ORDER BY id in a fixed table order (accounts before orders) before mutating them, so overlapping transactions queue instead of deadlocking.
What are the alternatives to fixing lock order, and when should you avoid them?
When the ordering can't be enforced easily, teams reach for other tools. Each one shifts the cost to a different place.
Retries. Wrap the whole transaction, not just the failing statement. Catch SQLSTATE 40P01 (deadlock) and 40001 (serialization failure), roll back, and run the transaction again. Cap the number of attempts and add jittered backoff so the retries don't collide on the same schedule. This is only safe if the transaction is idempotent and triggers no external side effects before commit, such as emails, HTTP calls or queue publishes. The bigger risk is that retries hide trends. Emit a metric on every retry, or a rising deadlock rate will stay invisible until latency spikes.
Advisory locks. pg_advisory_xact_lock serializes work on a logical resource cleanly, for example "account 42". Every code path must derive the key the same way. Session-level advisory locks can outlive the request behind a connection pooler. If you take advisory locks before row locks on some paths and after them on others, you create a new cycle.
Coarse locking. LOCK TABLE or a single-worker queue cannot deadlock on that resource, but it also serializes every writer.
| Approach | Fits when | Avoid when |
|---|---|---|
| Retry on 40P01/40001 | Rare, idempotent conflicts | Side effects precede commit |
| Advisory locks | One logical resource, shared key scheme | Session locks behind a pooler |
LOCK TABLE / serial worker |
Low-volume batch jobs | Hot OLTP tables |
Tuning deadlock_timeout |
Reducing detection overhead or log noise | Expecting fewer deadlocks; it only changes how long a session waits before the check runs |
SERIALIZABLE |
You need its anomaly guarantees | Hoping it removes row-lock deadlocks; it adds 40001 aborts instead |
| Explicit lock ordering | Default for multi-row writes | Rarely |
import random
import time
import psycopg
from psycopg import errors
RETRYABLE = (errors.DeadlockDetected, errors.SerializationFailure) # 40P01, 40001
def run_with_retry(conn, work, max_attempts=4, base_delay=0.05, on_retry=None):
"""Run work(conn) as one transaction and retry the whole transaction on 40P01/40001.
work must be idempotent, with no emails, HTTP calls or queue publishes before commit.
"""
for attempt in range(1, max_attempts + 1):
try:
# Each attempt is a fresh transaction. The block rolls back on exception.
with conn.transaction():
return work(conn)
except RETRYABLE as exc:
if on_retry:
# Count every retry, or a rising deadlock rate stays invisible.
on_retry(exc.sqlstate, attempt)
if attempt == max_attempts:
raise # Bounded: surface the failure instead of looping forever.
# Full jitter, so colliding transactions don't retry in lockstep.
time.sleep(random.uniform(0, base_delay * 2 ** attempt))
def transfer(conn, src=42, dst=7, amount=100):
# Explicit lock ordering still applies: lock the lower id first.
for acct in sorted((src, dst)):
conn.execute("SELECT 1 FROM accounts WHERE id = %s FOR UPDATE", (acct,))
conn.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, src))
conn.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, dst))
with psycopg.connect("postgresql://app:example-password@localhost/appdb", autocommit=True) as conn:
run_with_retry(conn, transfer, on_retry=lambda code, n: print(f"retry sqlstate={code} attempt={n}"))
A psycopg 3 wrapper that retries the whole transaction on SQLSTATE 40P01 and 40001. It caps the number of attempts, adds jittered backoff and calls a metric hook on every retry, and the transaction it runs still locks rows in a fixed order.
How does consistent lock ordering affect development cost and save engineering time?
The upfront cost
Consistent lock ordering is mostly a documentation and review cost. Someone has to write down the canonical table order (for example accounts before orders before order_items) and the rule for rows within a table (ascending primary key). Someone has to check when the ORM flushes, because a deferred flush can reorder writes relative to the code that triggered them. And some code paths need an explicit SELECT ... FOR UPDATE at the top of the transaction so locks are taken in the documented order instead of wherever the first write happens.
Where the time comes back
Deadlocks depend on timing, so they rarely reproduce locally. Each incident means pulling the two statements and their PIDs from the log, working out which code paths issued them, and rebuilding the cycle by hand. Retries hide the symptom without removing the cycle. As concurrency grows, retries collide again, and p99 latency rises without any obvious error.
Illustrative estimate (assumed numbers, not a benchmark): investigation hours per year = incidents per month × 12 × hours per incident. With 2 incidents per month at 6 hours each, that is 2 × 12 × 6 = 144 hours, compared with a one-time convention and audit of perhaps 20 to 40 hours.
Prevention and observability
- Set
application_nameper service or worker, and include%ainlog_line_prefix. Add query comments that name the code path, so a deadlock log entry points to a specific handler rather than just a PID. - Graph the
deadlockscounter inpg_stat_databaseso you see the trend before users report errors. - For each suspected cycle, write a test that opens two connections, takes the first lock on each, and then issues the conflicting statements in a fixed interleaving. The test should assert that one connection fails with
40P01.
When to invest
Invest as soon as more than one module in a monolith writes to shared tables under concurrent load. The cost of agreeing on a lock order rises as the number of code paths that would need auditing grows.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to postgres deadlock detected, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- The dependency graph answers "what does this touch?" in seconds instead of a manual audit.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- You can size a change's blast radius before merging, avoiding surprise cross-team firefighting.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.