Deadlocks: Nothing Is Watching Until Your Own Timer Fires

There is no deadlock detector running in your Postgres server. Nothing scans the lock table looking for cycles. A backend that cannot get a lock goes straight to sleep, sets a one-shot alarm for deadlock_timeout — one second by default — and only when that alarm goes off does it get up and look for a cycle itself. Once. If it finds one it is part of, it kills its own transaction.

That design has a name in the source: optimistic waiting. The reasoning is in src/backend/storage/lmgr/README and it is sound — checking for a cycle means taking every partition of the lock table exclusively, so doing it on every lock wait would cost more than the deadlocks do. But the consequences are not what the setting's name suggests, and almost every piece of advice about deadlock_timeout gets them backwards.

The panel runs 30 seconds of a contended workload. Eight sessions each open a transaction, lock three rows out of a pool of twenty, and commit. Nobody writes LOCK TABLE; these are ordinary UPDATE statements, and the lock on each row is taken implicitly, one row at a time, in whatever order the rows arrive. The control that carries the lesson is deadlock_timeout. Move it and watch two numbers that most people expect to move together.

At the defaults, 376 transactions commit in 30 seconds and 34 die with ERROR: deadlock detected. Now drag deadlock_timeout down to 10 ms. The deadlock count explodes from 34 to 994 — and the workload gets twenty times better: 7,744 transactions commit instead of 376. Per thousand commits the deadlock rate barely moves, 90 to 128. The setting never prevented a single deadlock. It decided how long everyone queued behind one had to stand there.

Now drag it the other way, to 5 s. Deadlock errors fall to 5, which looks like a fix, and 67 transactions commit in 30 seconds. The longest single lock wait is 10,019 ms. That is what "reducing deadlocks" by raising the timeout buys: a system that has almost stopped, reporting almost no errors.

The fix is not on this panel's timeout slider at all. Set row visit order to sorted by primary key before locking: 10,699 commits, zero deadlocks, and the detector never runs once.

What the check actually does when it wakes up

The waits-for graph is the standard one: a node per process, an edge from A to B when A is waiting for a lock that B holds in a conflicting mode. A deadlock is a cycle. What is not standard is everything around it.

The check is armed once per wait. ProcSleep calls enable_timeout_after(DEADLOCK_TIMEOUT, DeadlockTimeout) before it goes to sleep, and if the timer fires, CheckDeadLock runs, sets a state variable, and the process goes back to sleep. The timer is not re-armed. A process that has waited two minutes has examined the graph exactly once, 119 seconds ago.

That sounds like a hole, and it is not, because of the shape of a cycle: a cycle is only complete once its last member starts waiting, and that member's timer has not fired yet when it joins. So every hard deadlock is found within one deadlock_timeout of the moment the cycle closed — by whichever member's alarm is the first to go off after that, which is often a process that blocked much earlier and whose timer merely happened to land on the right side of the last arrival. Which is also the answer to "why did it kill that transaction" — Postgres does not choose a victim. There is no equivalent of SQL Server's DEADLOCK_PRIORITY, no cost heuristic, no preference for the younger transaction. The process that runs the check aborts itself, and the documentation says as much: "Exactly which transaction will be aborted is difficult to predict and should not be relied upon."

A cycle you are not part of is deliberately left alone. This is the line in the README that surprises people most: if the search reaches a cycle that loops back to some node other than the starting process, the checker reports no deadlock, "on the grounds that resolving such a deadlock is the responsibility of the processes involved — killing our start-point process would not resolve the deadlock." Watch the cycles seen and ignored readout at the defaults: 33 times in 30 seconds a process looked straight at a deadlock and went back to sleep, correctly. The processes in it have their own timers and will handle it.

Some cycles are broken without killing anyone. Postgres distinguishes two kinds of edge. A hard edge is the ordinary one: A waits because B holds a conflicting lock. A soft edge exists when A is merely behind B in the same lock's wait queue with a conflicting request — A waits for B not because B has anything, but because ProcLockWakeup grants in arrival order and will never wake A first. A cycle made only of hard edges is fatal. A cycle containing even one soft edge can be dissolved by reversing that edge, which means topologically re-sorting the wait queue so the two processes swap places. Nobody is aborted, and the only trace is a log line — process 41236 avoided deadlock for ExclusiveLock on tuple (14,3) by rearranging queue order after 1000.213 ms — which you will not see, because log_lock_waits is off by default in PostgreSQL 18.

There is one case that skips the wait entirely. If you are about to join a queue for an object on which you already hold a conflicting lock, and the waiter ahead of you is blocked by that lock, JoinWaitQueue in proc.c spots the two-way deadlock before anyone sleeps: it asks "must he wait for me?" and then "must I wait for him?", and if both are true it sets early_deadlock and returns PROC_WAIT_STATUS_ERROR instead of queueing. That is the lock-upgrade deadlock: two sessions each holding a shared lock on the same row and each trying to take it exclusive. It is the one deadlock Postgres reports in microseconds rather than in deadlock_timeout milliseconds.

Where the lock order comes from, and why it is not yours

"Acquire locks in a consistent order" is correct advice and it is what the row visit order control demonstrates: sorted gives 10,699 commits and zero deadlocks against 376 and 34. The problem is that in a workload made of plain UPDATE and DELETE statements, you are not the one choosing the order. The plan is.

A statement locks each row as its scan produces it, so the lock order is the row order of the access path:

• An index scan produces rows in index-key order — the order of whichever index the planner picked, which is not necessarily the primary key.
• A bitmap heap scan produces them in physical block order, because the bitmap is sorted by block to make the heap reads sequential. Its output order therefore has nothing to do with the index that built it.
• A sequential scan produces them in heap order — and an updated row's new version is written wherever there is free space, usually at the end of the file, so heap order changes under you as the table is written to.
• A parallel scan of any kind interleaves blocks across workers non-deterministically, so two executions of one statement can differ.

Set row visit order to heap order, re-read every transaction. Every session now follows the same rule — sort by physical position — and the run still produces 38 deadlocks across 528 commits. Nobody is inconsistent; the order itself moved between one transaction starting and the next. This is the case that looks impossible from the outside, and it is the reason two runs of the same DELETE statement can deadlock with each other.

Two more sources of lock order that are not in your SQL at all. A foreign key check takes a KEY SHARE lock on the parent row, in the order the child rows are processed — so inserting a batch of children that reference two shared parents will deadlock against another batch that references them in the other order, with no UPDATE of the parent anywhere. And INSERT ... ON CONFLICT DO UPDATE takes a row lock on whatever it collides with, in the order your VALUES list happens to be in, which is a lock order chosen by whoever built the array.

UPDATE ... WHERE id = ANY(ARRAY[7,3,9]) does not lock in 7, 3, 9 order. Sorting that array in your application changes nothing, because the array is a filter, not an iteration. What does work is making the ordering explicit and separate:

SELECT id FROM accounts
WHERE  id = ANY($1)
ORDER  BY id
FOR    UPDATE;                 -- locks in id order, one row at a time

UPDATE accounts SET ... WHERE id = ANY($1);   -- now conflict-free

The SELECT ... FOR UPDATE pass locks the rows in the order the ORDER BY produced, and by the time the UPDATE runs this transaction already holds every row lock it needs, so the UPDATE's own visit order cannot matter. That trick and its costs are the subject of their own lesson — including the fact that FOR UPDATE with ORDER BY only helps if every writer does it.

The boundary: where none of this helps

Push rows locked per transaction to 6 with everything else at the defaults. The run produces 9 commits and 36 rollbacks — 4,000 deadlocks per thousand commits, which is to say almost every transaction dies and is retried until it gets lucky. Sorting still fixes it. But the shape of that number is the warning: past a certain contention, a retry loop is no longer a safety net, it is the workload. Retrying a transaction that will deadlock again with high probability converts a correctness mechanism into a livelock with extra logging.

Push rows they contend over from 20 up to 200 instead. 20,633 commits, zero deadlocks, no detector runs at all. Spreading the contention is a stronger fix than any ordering discipline, because it removes the wait rather than sequencing it — which is the argument for sharding a hot counter row, and for not funnelling a queue table through a single status row.

Three things the simulation cannot show you, that end real incidents:

The deadlock may not be between two of your transactions. If the process blocking you is an autovacuum worker, the same deadlock_timeout alarm takes a different branch entirely: DeadLockCheck returns DS_BLOCKED_BY_AUTOVACUUM and your backend sends the worker a SIGINT. The worker is the one that logs the error — ERROR: canceling autovacuum task, raised inside its own interrupt handler — so the message carries the vacuum's process id, not yours. Your transaction survives. The vacuum does not, and it restarts from block zero next time, which is why a busy table's autovacuum can appear to run forever and never finish and why, on a large table, the space it already freed is never advertised.

That branch has a carve-out that decides how your incident ends. Read the condition in proc.c: the signal is sent only if (statusFlags & PROC_IS_AUTOVACUUM) && !(statusFlags & PROC_VACUUM_FOR_WRAPAROUND). An anti-wraparound vacuum — the one autovacuum launches when a table's oldest transaction id gets close to the freeze limit — is exempt, and your backend simply keeps waiting for it. So the rule is the opposite of what people learn from the ordinary case: a routine autovacuum yields to you, and the one autovacuum you most need to finish is also the one that will not step aside. If a session appears wedged behind a vacuum that never gets cancelled, check whether that worker is running to stop transaction id wraparound before looking for a bug anywhere else.

Row locks are not in the lock table. A row lock lives in the row's own xmax field on the heap page, which is why max_locks_per_transaction's documentation says flatly: "This is not the number of rows that can be locked; that value is unlimited." What a waiter actually blocks on is a ShareLock on the holder's transaction id, which every transaction holds in ExclusiveLock on itself until it ends. That is why the DETAIL line says "waits for ShareLock on transaction 8814" and never names your table: at the moment of the cycle, the object being contended is a transaction, not a row. The row appears only in the CONTEXT line.

Deadlock is not the same error as a serialization failure. 40P01 is a lock cycle and can happen at any isolation level, including READ COMMITTED. 40001 is a snapshot conflict and only happens at REPEATABLE READ or SERIALIZABLE. Both are transient, both want a retry loop, and the fixes point in opposite directions: consistent lock ordering reduces the first and does nothing for the second. Retrying them correctly is a lesson of its own.

Checking it on a real system

Start with whether you have a deadlock problem at all, in one number that is already being collected:

SELECT datname, deadlocks, xact_commit, xact_rollback,
       round(1000.0 * deadlocks / nullif(xact_commit, 0), 2) AS per_1k_commits,
       stats_reset
FROM pg_stat_database
WHERE datname = current_database();

per_1k_commits is the number to trend, not deadlocks — an absolute count falls whenever throughput falls, which is exactly what happens when you raise deadlock_timeout, and it will look like an improvement.

To find the cycles, turn on the logging that the error already wants you to read. The client only ever gets the process ids; the queries go to the server log:

ALTER SYSTEM SET log_lock_waits = on;      -- off by default
ALTER SYSTEM SET log_min_duration_statement = '250ms';
SELECT pg_reload_conf();

log_lock_waits is the important one and it costs nothing when nothing is waiting, because it fires on the same timer as the deadlock check. That coupling is worth stating plainly: raising deadlock_timeout to 10 s also means no lock wait shorter than 10 s is ever logged. The setting people reach for to quieten deadlocks is the same setting that hides the waits which would have explained them.

With it on you get four messages, and each says something different:

• process N still waiting for ExclusiveLock on tuple (14,3) ... after 1000.121 ms — a wait passed the timeout and there was no cycle.
• process N acquired ExclusiveLock ... after 3210.5 ms — the same wait, resolved.
• process N avoided deadlock ... by rearranging queue order after 1000.2 ms — a soft deadlock. Real, resolved, invisible without this setting.
• process N detected deadlock while waiting for ... after 1000.4 ms — followed by the ERROR.

While an incident is live, the blocking chain is one function call. Do not write the recursive pg_locks self-join people paste around; pg_blocking_pids already walks it:

SELECT a.pid, pg_blocking_pids(a.pid) AS blocked_by,
       a.state, a.wait_event_type, a.wait_event,
       now() - a.state_change AS waiting_for,
       left(a.query, 70) AS query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY waiting_for DESC;

A wait_event_type of Lock with wait_event = transactionid is a row lock. tuple is the queue in front of a row lock. relation is a table-level lock, which in a deadlock almost always means a migration ran against production. If pg_blocking_pids shows a set of rows that point at each other, you are looking at a live cycle that nobody has noticed yet, and you now know exactly how long it will take somebody to: one deadlock_timeout from whenever the last of them blocked.

Finally, the retry loop, because with deadlocks you will need one. Retry on SQLSTATE 40P01 from the outermost level, not from a savepoint. ROLLBACK TO SAVEPOINT undoes only what happened after the savepoint: the transaction keeps its transaction id, keeps every lock it took before that point, and is still the thing the other member of the cycle is blocked on. Retrying from inside it re-enters the same conflict holding the same locks. Only ending the top-level transaction releases them all. Cap the attempts, and jitter the backoff: two clients that deadlocked together and retry after the same fixed delay will deadlock together again.

Next, the mechanism every one of these cycles is built out of: row locks, their four modes, and what SKIP LOCKED really skips.