Postgres deadlock detector not always working?

Question

We were recently having some problems with deadlocks in Posgres 9.4. Some queries were simply not returning and would just hang indefinitely. After some investigation we found that process deadlocking seemed to be the problem. When running the queries from the Lock Monitoring Postgres Wiki, we would see a whole bunch of blocked processes. The only way to resolve them was then to start killing some of those processes until Postgres could figure things out again.

Now my understanding was that the deadlock detector in Postgres should be able to figure out that there is a deadlock and then proceed to resolve it by rolling back one of the blocked transactions, so that the other one could proceed and then retrying the first one. But that is not what happened in our case, these processes where just deadlocked indefinitely until we killed them.

The deadlock_timeout was set to 1s (the default) and I could not find a flag to turn the deadlock detector on or off, so I assume it is always on.

So my question is whether the deadlock detector can only detect some types of deadlocks and if so, which ones?

And how come the queries shown on the Lock Monitoring Wiki can detect the deadlocked processes but the deadlock detector itself cannot?

Answer 1

Postgres will only spot a deadlock if it can see two transactions waiting on each other. In particular for two (or more) processes the scenario must be:

• A needs to acquire a resource that is locked by B.
• B needs to acquire a resource that is locked by A.

Deadlock handling will not deal with situations such as:

• A needs to acquire a resource that is locked by B.
• B locked the table in an interactive psql session then took the afternoon off without committing, rolling back or logging out.

From what you describe it sounds like one of your database sessions is not releasing its resources. Maybe it's missing a COMMIT etc. It's not a deadlock as far as Postgres is concerned because for all it knows there is a perfectly valid reason why the lock is being held that long.

One thing you can do is set a lock timeout. This will mean that there is an upper limit on how long a process will wait to try and get a lock before it gives up and flags a problem.

Lock time out and other settings documented at: http://www.postgresql.org/docs/9.3/static/runtime-config-client.html