Postgres deadlock detected: Reading the Log and Fixing the Lock Order
Two transactions each hold what the other wants. Here is how to read the deadlock report, find both statements, and restructure so it cannot happen again.
The short answer
ERROR: deadlock detected
DETAIL: Process 4471 waits for ShareLock on transaction 88231; blocked by process 4489.
Process 4489 waits for ShareLock on transaction 88229; blocked by process 4471.
HINT: See server log for query details.
Two transactions each hold a lock the other needs. Postgres detects the cycle after one second and kills one of them so the other can proceed.
The fix is almost always lock ordering: make every transaction acquire locks on rows in the same deterministic order, usually by sorting on primary key before you touch anything.
The detail you need is in the server log, not the client error. Turn on log_lock_waits and read the full report, which names both queries.
Tested on PostgreSQL 16.3.
What a deadlock actually is
Not the same as a slow lock. A lock wait resolves when the holder commits. A deadlock never resolves, because each party is waiting on the other, so Postgres has to break the cycle by aborting somebody.
The classic shape:
Transaction A Transaction B
------------- -------------
UPDATE accounts WHERE id = 1; UPDATE accounts WHERE id = 2;
UPDATE accounts WHERE id = 1; <- waits for A
UPDATE accounts WHERE id = 2; <- waits for B
deadlock
A holds row 1 and wants row 2. B holds row 2 and wants row 1. Neither can proceed.
Postgres runs a deadlock detector, triggered after deadlock_timeout, which defaults to one second. It builds a wait for graph, finds the cycle, and aborts one transaction with a 40P01 SQLSTATE.
The one second delay matters for diagnosis: a deadlock always costs at least a second before it errors. If you have deadlocks at any volume, you also have a latency problem, not just an error rate problem.
Getting the full report
The error the client sees is truncated and rarely useful on its own. The server log has both queries.
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '1s';
SELECT pg_reload_conf();
log_lock_waits logs any wait longer than deadlock_timeout, which also surfaces near misses that have not become deadlocks yet. Those are your early warning.
The full report looks like this:
ERROR: deadlock detected
DETAIL: Process 4471 waits for ShareLock on transaction 88231; blocked by process 4489.
Process 4489 waits for ShareLock on transaction 88229; blocked by process 4471.
Process 4471: UPDATE inventory SET qty = qty - 1 WHERE sku = 'B-22'
Process 4489: UPDATE inventory SET qty = qty - 1 WHERE sku = 'A-11'
CONTEXT: while updating tuple (14,3) in relation "inventory"
Now you have both statements. In practice they are usually the same statement in the same code path, executed by two requests with their arguments in different orders. That is the signature of the ordering problem.
Note that the logged statement is the one that was waiting, not necessarily the one that acquired the first lock. To find the earlier statement you need the transaction's history, which means either application logs correlated by timestamp and PID, or reasoning about the code path.
The common causes
1. Unordered multi row updates
The classic. Any code that updates several rows in an order derived from user input.
# order arrives from the request, so it varies
for item in cart.items:
db.execute("UPDATE inventory SET qty = qty - :n WHERE sku = :sku",
{"n": item.qty, "sku": item.sku})
Two carts containing the same two SKUs in different orders will deadlock under concurrency. It works fine in testing because you never run two checkouts simultaneously.
Fix: sort before you touch anything.
for item in sorted(cart.items, key=lambda i: i.sku):
db.execute(...)
One line. It works because if every transaction acquires locks in the same total order, a cycle is impossible. This is the standard result and it is worth internalising, because it applies far beyond databases.
2. Foreign keys taking locks you did not ask for
This one surprises people because the deadlocking statements do not appear to touch the same rows.
Inserting a row with a foreign key takes a FOR KEY SHARE lock on the referenced parent row, to stop it being deleted or having its key changed mid transaction.
-- transaction A
INSERT INTO order_items (order_id, product_id) VALUES (100, 5);
-- takes FOR KEY SHARE on products id 5 and orders id 100
-- transaction B
UPDATE products SET name = 'x' WHERE id = 5;
-- wants FOR NO KEY UPDATE, compatible
UPDATE products SET id = 6 WHERE id = 5;
-- wants FOR UPDATE, conflicts
The tell is a deadlock report naming two tables where your code only meant to touch one. If you see while updating tuple ... in relation "products" from a statement that only inserted into order_items, this is why.
Postgres has been careful here since 9.3, and FOR NO KEY UPDATE avoids most of this, but it still bites with updates that touch the referenced key or with ON DELETE CASCADE chains.
3. Upsert races
INSERT INTO counters (key, n) VALUES ('a', 1)
ON CONFLICT (key) DO UPDATE SET n = counters.n + 1;
Safe on its own. Deadlocks when two transactions each upsert multiple keys in different orders, which is the same ordering problem as case one wearing a different hat. Sort your keys.
4. Index and trigger side effects
A trigger that writes to an audit table, or a unique index causing an insert to wait on another transaction's uncommitted conflicting insert, can create edges in the wait graph that are invisible in your application code.
Deferred unique constraints are particularly good at producing surprising deadlocks, because the conflict check happens at commit time rather than at statement time, so the ordering is not the order your code executed in.
5. Long transactions widening the window
Not a cause in itself, but a powerful amplifier. A transaction that holds a lock while making an HTTP call, waiting on a queue, or doing application side computation holds it for hundreds of milliseconds instead of one.
The rule I try to hold: never do I/O to another system inside a database transaction. Fetch what you need first, then open the transaction, write, and commit. This one habit eliminates a large share of both deadlocks and lock wait timeouts.
Finding the pattern in production
A single deadlock tells you little. The population tells you everything.
Grep the log and count by statement:
grep -A6 "deadlock detected" postgresql.log \
| grep -oP 'Process \d+: \K.*' \
| sed 's/[0-9]\+/N/g' \
| sort | uniq -c | sort -rn | head
Normalising the numbers turns 400 individual statements into a handful of distinct query shapes, and it usually makes the responsible code path obvious immediately.
Also check whether they cluster in time. Deadlocks that all occur within a few minutes daily point at a batch job racing with live traffic, which is a scheduling fix rather than a code fix.
For live lock investigation:
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query,
now() - blocked.query_start AS blocked_for
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';
pg_blocking_pids is the function worth remembering. It answers "who is holding me up" directly.
Fixing it properly
Sort before locking. The primary fix. Any operation touching multiple rows should establish a deterministic order first, usually by primary key.
Lock explicitly and up front for complex transactions:
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- both locks acquired in a known order before any writes
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
The ORDER BY inside the FOR UPDATE is the important part. Without it, Postgres locks in whatever order the plan produces, which can vary.
Keep transactions short. Open late, commit early, no external I/O inside.
Retry on 40P01. Even with good ordering, deadlocks can happen, and they are safe to retry because the aborted transaction rolled back completely. This is one of the cleanest retry cases in all of software.
import time
from psycopg import errors
def with_deadlock_retry(fn, attempts=3):
for i in range(attempts):
try:
return fn()
except errors.DeadlockDetected:
if i == attempts - 1:
raise
time.sleep(0.05 * (2 ** i)) # backoff with jitter in real code
Retry is a backstop, not a fix. If your retry rate is climbing, the ordering problem is still there and you are paying a second of latency each time.
Consider advisory locks for coordination that is not really about rows. If your transactions are serialising on a logical resource rather than specific rows, pg_advisory_xact_lock(id) gives you a single well ordered lock and sidesteps the whole graph.
What not to do
Do not raise deadlock_timeout to make them go away. All that does is make each deadlock take longer to detect. The deadlock still happens and now it costs five seconds instead of one.
Do not switch to a lower isolation level. Deadlocks are not an isolation level problem. They happen at Read Committed and they happen at Serializable, and lowering isolation introduces different bugs.
Do not add NOWAIT everywhere. It converts a deadlock into an immediate error, which is sometimes what you want for an interactive operation, and is usually just moving the failure.
Do not serialise everything through one global lock. It works and it destroys throughput. I have seen this shipped as a deadlock fix more than once.
Prevention
Establish a lock ordering convention and write it down. Something like: always acquire in ascending primary key order, and always lock parent tables before child tables. Put it in your contributing guide. This is exactly the kind of invariant that is invisible in code and obvious in a document.
Test concurrency deliberately. A test that fires the same endpoint twice simultaneously with arguments in opposite orders catches the entire class. Most suites never run two things at once, which is why this reaches production so reliably.
Alert on deadlock rate. pg_stat_database.deadlocks is a counter. Graph it. A rising rate is a code path degrading under growing concurrency, and it tends to grow quietly until it is an incident.
Log lock waits from day one. log_lock_waits = on costs almost nothing and gives you the near misses before they become failures.
