Read a PostgreSQL deadlock error

Before you start

A deadlock is two or more transactions each waiting for a lock another one holds, so none can continue. PostgreSQL notices after a session has waited deadlock_timeout (1 second by default), ends one transaction with an error and lets the rest go on. The error below is made up, with the shape PostgreSQL gives.

Read the error

ERROR:  deadlock detected
DETAIL:  Process 4412 waits for ShareLock on transaction 9021; blocked by process 4398.
         Process 4398 waits for ShareLock on transaction 9020; blocked by process 4412.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,1) in relation "accounts"

How it happens

-- Session A                          -- Session B
BEGIN;                                BEGIN;
UPDATE accounts SET ... WHERE id = 1; UPDATE accounts SET ... WHERE id = 2;
UPDATE accounts SET ... WHERE id = 2; -- waits for B
                                      UPDATE accounts SET ... WHERE id = 1; -- waits for A

Change the code, not only the retry

• Take locks in one order. If every transaction updates the lower id first, the circle cannot form. Sort the ids before you loop.
• Lock early, together. SELECT ... FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE takes both rows in a fixed order up front.
• Keep transactions short. A shorter hold means fewer chances to meet.
• Retry on SQLSTATE 40P01. It is a normal outcome under load, and the ended transaction is safe to run again. Retry is a safety net, not the fix.

Next step

dbtempo shows deadlocks on an incident. See Read an incident. dbtempo changes nothing for a deadlock; the fix is in your code.

Sources

• PostgreSQL documentation: explicit locking, including deadlocks
• PostgreSQL documentation: deadlock_timeout
• PostgreSQL documentation: error codes (40P01)