What does 'deadlock detected' mean in Postgres?
A deadlock is a cycle of waits: two or more transactions each hold a lock that another one in the group needs, so none of them can proceed. The simplest case has two transactions and two rows:
| Step | Transaction A | Transaction B |
|---|---|---|
| 1 | Locks row 1 | |
| 2 | Locks row 2 | |
| 3 | Requests row 2, waits on B | |
| 4 | Requests row 1, waits on A |
At step 4 the wait graph is closed: A → B → A. Neither transaction will ever release its lock, because each one is waiting on the other.
Postgres does not check for this on every lock request, because the check is expensive. A backend that has waited on a lock for longer than deadlock_timeout (one second by default) inspects the wait graph. If it finds a cycle, it picks a victim, usually the backend that ran the check. Postgres aborts the victim's whole transaction with ERROR: deadlock detected and SQLSTATE 40P01. The DETAIL line names the processes involved and the locks each one was waiting for. The victim's locks are released, and the other transaction gets its lock and commits normally.
So the error appears in only one of the two code paths, and often not the one with the bug. The surviving path shows no error, only a pause of roughly deadlock_timeout.
This guide's thesis is that deadlocks come from application code taking the same locks in different orders. Raising or lowering deadlock_timeout only changes how long Postgres waits before it breaks the cycle. It does not prevent the cycle from forming.
+----------------------+ +----------------------+
| Transaction A | waits for row 2 | Transaction B |
| holds lock: row 1 |-------------------->| holds lock: row 2 |
| |<--------------------| |
+----------------------+ waits for row 1 +----------------------+
| |
+----------------------+---------------------+
|
v
+----------------------------------------+
| Wait cycle closed: A -> B -> A |
| Lock wait exceeds deadlock_timeout |
| (default 1s); waiting backend checks |
| the wait graph and finds the cycle |
+----------------------------------------+
|
v
+----------------------------------------+
| Victim picked (usually the checker), |
| e.g. Transaction B |
+----------------------------------------+
| |
v v
+-------------------------------+ +-------------------------------+
| Transaction B: aborted | | Transaction A: gets row 2, |
| ERROR: deadlock detected | | commits normally |
| SQLSTATE 40P01 | | (no error, ~1s pause only) |
| its locks are released | | |
+-------------------------------+ +-------------------------------+
Each transaction holds the row lock the other one needs, which closes the wait cycle A -> B -> A; after deadlock_timeout Postgres aborts one victim with SQLSTATE 40P01 and the other transaction gets its lock and commits.
Who should use this approach?
The diagnose-then-reorder workflow in this guide is aimed at the following people:
- Backend engineers on a shared Postgres database, especially in a monolith where many features write to the same hot tables. If
40P01errors show up intermittently and you can't reproduce them on demand, a lock ordering bug is the likely cause. - Engineers who own a data-access layer or ORM conventions. The fix works best when it lives in shared helpers rather than at individual call sites, so whoever owns those helpers is the natural owner of the global lock order.
- On-call engineers and SREs who see deadlock errors in logs or error trackers and need to turn a log entry into a specific pair of code paths.
The approach is less useful in some situations:
- A single deadlock with no recurrence. A retry may be enough. The workflow pays off when the same pair of relations keeps showing up.
- Serialization failures under
SERIALIZABLEorREPEATABLE READisolation. These are SQLSTATE40001, not40P01. They come from a different mechanism, and lock ordering does not prevent them. - Contention on a single hot row. Here the problem is queueing, not a cycle. Reordering locks won't help, because there is only one lock to order.
How does the lock-ordering approach work?
The approach is a loop with four steps:
- Find the cycle. Use the server log's deadlock entry to identify the two transactions and the locks each one held and wanted.
- Reproduce it deterministically. Step through the statements in two sessions in the order that closes the cycle.
- Impose one global order. Make every code path that touches those resources acquire them in that order.
- Re-run the reproduction. Confirm that one session now only waits and no error appears.
The reason step 3 prevents deadlocks rather than just making them rarer is simple. Give every lockable resource a rank, such as table first, then primary key ascending. If every transaction acquires locks in increasing rank, a transaction only ever waits for a resource ranked higher than everything it already holds.
A cycle would need some transaction in the chain to wait for a resource ranked lower than one it holds. In the two-row example, B would hold row 2 and wait for row 1. A consistent order rules that out, so the wait graph can't close. Waits still happen, but they form chains that end at a transaction that is running. That transaction eventually commits or rolls back, and the chain drains.
The guarantee only covers locks that follow the order. That is why the later sections spend time on implicit locks: foreign key checks, triggers, ORM flushes, and unordered bulk updates. Any of these can acquire a resource out of rank and reopen the cycle.
Why are monoliths so prone to lock ordering bugs?
In a monolith, dozens of features written by different people over several years share the same handful of hot tables: users, accounts, orders, inventory. Each feature's transaction is correct on its own. Nobody owns the global order in which rows get locked, so two code paths can lock the same two rows in opposite sequence.
The classic case is a money transfer. The transfer endpoint updates the source account, then the destination. A refund job written later credits the original payer first, then debits the merchant. When A pays B while B refunds A, each transaction holds one row lock and waits for the other. Postgres sees the cycle and aborts one of them.
Even when the explicit UPDATEs follow a consistent order, other sources take locks you never wrote:
| Lock source | Why it breaks ordering |
|---|---|
| Foreign key checks | Inserting or updating a child row takes FOR KEY SHARE on the parent, which conflicts with some concurrent parent updates |
| ORM autoflush and lazy loads | Pending writes are flushed when a query runs, so statement order follows ORM internals, not your code |
| Triggers | A trigger on one table can update rows in another, adding locks inside the same transaction |
| Unique index conflicts | Concurrent inserts of the same key make the second insert wait on the first transaction |
Bulk UPDATE ... WHERE |
Rows are locked in scan order, which can differ between plans and executions |
Because these locks are invisible in the application code, reading the code alone rarely reveals the cycle.
-- Setup: two accounts on a shared hot table.
CREATE TABLE accounts (id int PRIMARY KEY, balance numeric NOT NULL);
INSERT INTO accounts VALUES (1, 100), (2, 100);
-- Run the steps in two psql sessions, in the order numbered.
-- Session A: transfer endpoint, locks the source row first.
BEGIN; -- step 1
UPDATE accounts SET balance = balance - 10 WHERE id = 1; -- step 2: A holds row 1
-- Session B: refund job, credits the payer first.
BEGIN; -- step 3
UPDATE accounts SET balance = balance + 10 WHERE id = 1; -- step 4: B waits for A's lock on row 1
-- Session A again:
UPDATE accounts SET balance = balance + 10 WHERE id = 2; -- step 5: A gets row 2 and commits normally
COMMIT; -- step 6
-- No deadlock yet. A deadlock needs B to have locked row 2 before step 4.
-- Run B with the opposite order to create the cycle:
-- B: UPDATE ... WHERE id = 2; B holds row 2
-- A: UPDATE ... WHERE id = 2; A waits for B
-- B: UPDATE ... WHERE id = 1; B waits for A, which closes the cycle
-- After deadlock_timeout, Postgres aborts one transaction with:
-- ERROR: deadlock detected
-- Fix: every code path locks the rows it needs in one global order
-- (here ascending id) before writing to any of them.
BEGIN;
SELECT id FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE; -- takes the locks on rows 1 and 2 in a fixed order
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;
Two sessions that update the same two account rows in opposite order form a lock cycle that Postgres aborts as a deadlock, and locking both rows up front in ascending id order with SELECT ... ORDER BY id FOR UPDATE removes the cycle.
How do you diagnose a Postgres deadlock from logs and pg_locks?
Reading the code alone rarely tells you which transactions collided. The server log does. When Postgres aborts a victim, it writes an ERROR: deadlock detected entry with a DETAIL block such as "Process 4121 waits for ShareLock on transaction 90211; blocked by process 4388," followed by the reverse edge of the cycle. A ShareLock on a transaction means one backend is waiting for another's transaction to commit or roll back. That typically happens because both backends tried to update the same row. The CONTEXT line names the tuple and relation, for example "while updating tuple (12,7) in relation orders."
The log also prints each process's query text. The client only receives a hint to check the server log. Map each PID to its statement, and the conflicting order usually becomes obvious.
To catch contention before it becomes a deadlock, enable log_lock_waits. Postgres will then log any lock wait that exceeds deadlock_timeout. Recurring waits on the same relations point to the code paths most likely to deadlock next.
For live contention, join pg_locks to pg_stat_activity on pid. Select these columns:
grantedlocktypemoderelation::regclasstransactionidquerystate
Filter on NOT granted to see the waiters. Call pg_blocking_pids(pid) on each waiter to get the sessions holding what it needs.
This view has a limit. pg_locks is a point-in-time snapshot, and Postgres resolves a deadlock about deadlock_timeout after the wait begins. Unless you query during that window, the cycle is already gone. Treat the log as your primary evidence and live queries as supporting detail.
-- Surface lock waits longer than deadlock_timeout (default 1s) in the server log.
-- Requires superuser; takes effect on reload, no restart needed.
ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();
SHOW deadlock_timeout;
-- Live contention: every ungranted lock, who is waiting, and who blocks them.
-- This is a snapshot; a deadlock is usually resolved before you can run it.
SELECT
l.pid AS waiting_pid,
l.granted,
l.locktype,
l.mode,
l.relation::regclass AS relation,
l.transactionid,
a.state,
a.query AS waiting_query,
pg_blocking_pids(l.pid) AS blocked_by
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE NOT l.granted
ORDER BY l.pid;
-- What the blocking sessions are running (pg_blocking_pids returns int[]).
SELECT b.pid, b.state, b.xact_start, b.query
FROM pg_stat_activity b
WHERE b.pid IN (
SELECT unnest(pg_blocking_pids(l.pid))
FROM pg_locks l
WHERE NOT l.granted
);
Enables log_lock_waits, then joins pg_locks to pg_stat_activity and uses pg_blocking_pids() to show which sessions are waiting, the lock each one wants, and which sessions hold it.
How can you reproduce a deadlock step by step?
Start from the log entry
Copy the full deadlock detected message, including its DETAIL lines. They name each backend process, the lock it waited for, the process blocking it, and the statement each one was running. If your log_line_prefix includes the application name or a request identifier, you can tie each process to a specific service or endpoint. Without that, you match on query text alone.
Map queries to code paths
For each statement, find the code that issues it and the transaction boundary around it. The statement in the log is only the one that was waiting. Earlier statements in the same transaction already hold locks, so read the whole transaction. Write down, for both transactions, the ordered list of rows or tables each one locks. Where the two lists touch the same resources in opposite order, you have found the cycle.
Replay it by hand
Open two psql sessions against a non-production database seeded with the same rows. Run BEGIN in both. Then interleave the statements in the order you reconstructed: session A takes its first lock, session B takes its first lock, A requests B's resource and blocks, and B requests A's resource. After roughly deadlock_timeout, one session gets the deadlock error. Stepping through by hand removes timing luck, so the result is the same on every run.
Confirm the fix
Apply the new lock order and replay the same steps. Session B should now block on its first statement and continue once A commits. No error should appear. That gives you a clear pass or fail. Changing deadlock_timeout or lock_timeout cannot give you that: those settings only change how long a cycle waits before Postgres breaks it, and the cycle can still form.
-- Setup (run once in a scratch database)
CREATE TABLE accounts (id int PRIMARY KEY, balance numeric NOT NULL);
INSERT INTO accounts VALUES (1, 100), (2, 100);
-- REPRO: run each step in the named psql session, in this order.
-- [A] BEGIN;
-- [B] BEGIN;
-- [A] UPDATE accounts SET balance = balance - 10 WHERE id = 1; -- A locks row 1
-- [B] UPDATE accounts SET balance = balance - 10 WHERE id = 2; -- B locks row 2
-- [A] UPDATE accounts SET balance = balance + 10 WHERE id = 2; -- A waits on B
-- [B] UPDATE accounts SET balance = balance + 10 WHERE id = 1; -- B waits on A: cycle
-- After about deadlock_timeout, one session gets:
-- ERROR: deadlock detected
-- [A] ROLLBACK; [B] ROLLBACK; -- reset before the next run
-- VERIFY FIX: both code paths now lock rows in ascending id order.
-- [A] BEGIN;
-- [B] BEGIN;
-- [A] UPDATE accounts SET balance = balance - 10 WHERE id = 1; -- A locks row 1
-- [B] UPDATE accounts SET balance = balance + 10 WHERE id = 1; -- B blocks here, holds nothing
-- [A] UPDATE accounts SET balance = balance + 10 WHERE id = 2; -- A proceeds, no cycle
-- [A] COMMIT; -- B's statement now completes
-- [B] UPDATE accounts SET balance = balance - 10 WHERE id = 2;
-- [B] COMMIT; -- pass: no deadlock error
-- Optional, from a third session while a session is blocked:
-- shows who is waiting and which backend PIDs block it.
SELECT pid, pg_blocking_pids(pid) AS blocked_by, wait_event_type, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';
A two-session psql script that reproduces an opposite-order row lock cycle, then reruns the same steps with a consistent lock order to confirm that session B only waits and no deadlock error appears.
How do you fix deadlocks with a consistent lock order?
The durable fix is to remove the cycle. A deadlock needs two transactions that each hold a lock the other wants. If every code path acquires locks in the same global order, no transaction can wait on another that is waiting on it.
Order rows and tables deterministically
Sort the keys before you touch them. For a multi-row change, lock the rows first with SELECT ... FOR UPDATE and ORDER BY id, then run the updates. A bare UPDATE ... WHERE id IN (...) locks rows in whatever order the plan visits them, which you do not control. Across tables, write down one acquisition order, such as accounts before ledger_entries. Then put that order into a shared data-access helper so individual call sites do not each re-implement it.
Shrink the window
Keep transactions short. Do not make HTTP calls, publish to a queue, or wait on a user while a transaction is open. Take the strongest lock you need at the start. Two transactions that both take FOR SHARE and later try to update the same row will deadlock each other. That is the classic lock-upgrade cycle.
What it costs
A global order couples code paths that used to be independent. It only holds if code review, helpers, or tests enforce it. Locking up front also holds locks longer on hot rows, so some deadlocks become queueing latency instead. In some cases you will need explicit SQL because the ORM does not expose the locking order you need.
When should you rely on retries, and when should you avoid them?
Even with a consistent lock order in place, keep a retry path for SQLSTATE 40P01. Postgres resolves a deadlock by aborting one participant, and from the application's view that is a transient failure: the other transaction commits, and running yours again will usually succeed. Treat the retry as a safety net, not the fix. A retry that quietly succeeds hides the fact that two code paths still disagree about ordering.
The retry has to wrap the whole transaction, not the failed statement. After the error, the transaction is aborted and every later statement fails until you roll back. So the loop should do the following on each attempt:
- Roll back, then rerun the full unit of work from
BEGIN, re-reading any state it depends on. - Cap attempts at a small number, such as three, then surface the error.
- Sleep with randomized backoff (jitter) so both victims do not collide again on the same schedule.
- Emit a metric on every retry, tagged with the transaction name.
The metric is what keeps this honest. If a deploy introduces a new path that locks accounts before orders, you see a rising retry count for a specific transaction instead of nothing.
Avoid retries when the transaction has side effects outside the database, such as sending an email, calling a payment API, or publishing to a queue. Rolling back the database does not undo those, so a retry can send them twice. Either defer them until after commit, for example with an outbox table that a separate worker drains, or make them idempotent with a stable key the receiver deduplicates on.
How can this approach save engineering time?
Intermittent deadlocks are expensive mainly because each occurrence tends to restart the investigation from scratch. Someone sees a 40P01 in the error tracker, can't reproduce it locally, adds a retry or bumps a timeout, and moves on until it happens again. This workflow saves time in several specific ways:
- The log replaces guesswork. With
log_lock_waitson and alog_line_prefixthat identifies the application, the deadlock entry names both statements and both processes. You start from the two code paths involved instead of searching the whole codebase. - A deterministic repro ends the "can't reproduce" loop. The two-session
psqlscript fails the same way on every run. Engineers stop waiting for production traffic to produce the right interleaving. - The repro becomes a regression check. The same step sequence, run before and after the change, gives a pass or fail result for review. It can also be scripted into a test that drives two connections, which catches a later change that reintroduces the opposite order.
- One shared ordering rule fixes a class of bugs. Once the order lives in a data-access helper, new code paths that use the helper inherit it. That prevents future deadlocks on those tables, not only the one you investigated.
- Retry metrics point at the next problem. Tagged retry counts show which transaction started deadlocking after a deploy, which narrows the search to recent changes in that path.
To make the trade concrete, here is an illustrative estimate. Every input is an assumed value, not a measurement. Assume a team hits a recurring deadlock twice a month. Each occurrence costs about three engineer-hours of triage, log reading, and a temporary workaround, which comes to roughly six hours a month. Assume instead that one engineer spends about a day finding the cycle, writing the repro, and moving the order into a shared helper. Under those assumptions, the one-time effort pays for itself within the first couple of months, and later regressions are caught by the repro rather than by users. Plug in your own incident rate and triage time; the comparison only holds if the deadlock genuinely recurs.
What are the alternatives to fixing lock order?
Every alternative either changes what happens when a cycle forms or removes concurrency so the cycle cannot form.
| Option | What it changes | Prevents deadlocks? | Cost | Reasonable when |
|---|---|---|---|---|
Lower lock_timeout / statement_timeout |
Aborts a waiter after a fixed time | No. It turns the deadlock into a timeout error, possibly before detection runs | Long legitimate waits fail as well | You need bounded latency and already retry |
Change deadlock_timeout |
How long a backend waits before running the deadlock check | No. It only changes how quickly a cycle is found | Lower values run the check more often under normal contention | Cycles are rare and detection delay dominates latency |
| Advisory locks | Serializes work on an application-chosen key | Yes, if every path takes them first, in a fixed order | Discipline across the whole codebase; easy to bypass | Work spans rows with no natural parent |
Parent-row FOR UPDATE |
Serializes all writers under one parent | Yes, within that parent | Throughput per parent drops to one writer | Writers to one aggregate are naturally rare |
LOCK TABLE |
Serializes every writer to the table | Yes | Throughput on that table drops to one writer; long queues | Batch jobs, maintenance windows |
| Retries alone | Nothing structural | No | Wasted work grows with contention | Cycles are rare and side effects are safe |
Coarser serialization pays off when the contended scope is small and contention on it is low. Locking one order header is fine if a typical order sees a handful of concurrent writes. It is not fine if one hot account receives most of the traffic. Measure how long the coarse lock is held, because that duration caps throughput for that key. If the lock-hold time multiplied by the arrival rate approaches one, you have moved the bottleneck, not removed it.
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 fix, 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.
- Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.
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.
- Consumers learn about a breaking change at review time rather than from production errors.
- 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.