Deadlock: a cycle the server has to break

A deadlock is a cycle in the graph of who is waiting for whom: each transaction in the cycle holds a lock another one needs, so none of them can move. PostgreSQL does not prevent this. It waits deadlock_timeout after a session first blocks, then looks for a cycle, and if it finds one it aborts a transaction to break it. The error arrives at the application, the remaining transactions continue, and nothing in the data is left inconsistent.

Why no amount of tuning reduces the count

deadlock_timeout is read as a tolerance and is nothing of the sort. It is how long a blocked session waits before the server spends effort looking for a cycle, which exists because the check is not free and the overwhelming majority of waits clear on their own. Shortening it makes detection faster and the checks more frequent. Lengthening it makes a real deadlock last longer before anyone is released. Neither changes how many occur, and there is no victim-selection policy to configure.

The count rises because two code paths touch the same rows in different orders, and the orders are usually not written down anywhere. Three sources account for most of what we see. An UPDATE with a multi-row predicate and no ordering locks rows in whatever order the plan produces them, so two concurrent runs of the same statement can start from opposite ends. A foreign key makes a write to a child row take a lock on the parent row, so two transactions touching unrelated children of two shared parents can deadlock without either statement naming the other’s table. And an upsert under contention retries internally, which changes the order it acquires things in.

What the log line is actually telling you

Turn on log_lock_waits before you need it. It is off by default, and with it on, any wait longer than deadlock_timeout leaves a line naming the blocked statement and the process holding the lock, which means an ordinary lock incident leaves the same evidence a deadlock does. The deadlock entry itself lists every process in the cycle together with the statement each was running, and that list is the fix: put the objects in a fixed order and make both paths follow it.

Reading the entry as a database problem leads to the wrong work. A rising deadlocks count in pg_stat_database is a report about application ordering, in the same way a rising constraint-violation count would be. The server is telling you it resolved something it should not have had to resolve.

Getting from the error to the two statements

The hard part is rarely the deadlock and almost always the blocking that precedes it, because the same ordering bug usually shows up first as a queue of sessions waiting on one transaction. Walking that queue to its root, reading what kind of lock is actually in dispute, and the settings that stop a migration from starting one are covered in lock contention and blocking trees. If the transaction at the root turns out to be doing nothing at all, idle in transaction is the state to understand first.