How to determine if a query can potentially deadlock in Postgres?

Question

When implementing database queries I'm often wondering if the query I'm writing can potentially deadlock when executed in parallel by many clients. Often I only find out by running the query in parallel with a large number of clients to see if I'm getting the infamous "deadlock detected" in practice.

I'm wondering if there is a more formal approach to determine if a query has the potential to deadlock? By "more formal" I mean something like:

Is it possible to inspect/explain what the query is doing under the hood even when executed in isolation and judge from e.g. some internal logs that a deadlock is possible? If I could for instance see the exact locks that are needed and their order, I could probably predict if it can deadlock. In practice, I often just don't know if the query is locking on table or on row level. In such a case the assumption would be e.g. that a single table lock is fine, but locking multiple rows non-atomically brings the deadlock potential, because another instance of the query could lock the rows in a different order.
If it isn't possible via inspecting logs, are there some general patterns to look for in queries that are a potential deadlock?

score 1 · Answer 1 · answered Mar 14 '23 at 17:57

I don't think that there is a principled formal approach to this. Besides, while it can happen, it is not a frequent occurrence that a single statement deadlocks with the same statement run in a second session. Usually, the other statements that ran in the same transaction and also take locks are just as important.

The simple tricks to reduce the likelihood of a deadlock are:

keep the transactions as short as possible
don't bundle any more data modifications in a single transaction than are necessary to guarantee the consistency of your data

When that is done, you can strive for more by making sure that resources are always locked in a certain order. That order is often hard to determine and will depend on the execution plan chose by the statement (which you can find with EXPLAIN). To give you an example:

UPDATE tab
SET col = 42
WHERE val = 'changeme';

There is no guarantee that the rows will be locked in a certain order. You could instead try the following which is somewhat more expensive, but will do better in this respect:

WITH myrows AS (
   SELECT id
   FROM tab
   WHERE val = 'changeme'
   ORDER BY id
   FOR NO KEY UPDATE
)
UPDATE tab
SET col = 42
FROM myrows
WHERE tab.id = myrows.id;

I'm not saying that there is a guarantee that the latter statement will never deadlock with anything, but it has better odds not to deadlock with itself than the former statement.

How to determine if a query can potentially deadlock in Postgres?

1 Answers1