Find and Fix a Deadlock

An application log shows deadlock detected a few times a day, and some customers see a failed payment. Nothing is broken in the database. Two transactions asked for the same rows in opposite orders, and PostgreSQL cancelled one of them to get out. This page reproduces that with two money transfers, shows how to read the error, and fixes it by changing the order of the locks. It uses two psql sessions, A and B, in the order the commands ran. \set VERBOSITY terse keeps each error to its first line, so the output is the same on every run.

Step 1: two accounts

Session A

CREATE TABLE accounts (id int PRIMARY KEY, owner text NOT NULL, balance numeric(10,2) NOT NULL);
INSERT INTO accounts VALUES (1, 'Ana', 500.00), (2, 'Ben', 500.00);

CREATE TABLE
INSERT 0 2

Step 2: two transfers in opposite directions

Transfer 1 moves 100 from account 1 to account 2. Transfer 2 moves 50 from account 2 to account 1. Each one is two updates in one transaction, and each updates its source first.

Session A

\set VERBOSITY terse
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

BEGIN
UPDATE 1

Session B

\set VERBOSITY terse
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;

BEGIN
UPDATE 1

Now A needs account 2, which B holds, and has to wait. Its output appears only after the deadlock is resolved.

Session A

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

UPDATE 1

B needs account 1, which A holds. Each waits for the other.

Session B

UPDATE accounts SET balance = balance + 50 WHERE id = 1;

ERROR:  deadlock detected

After one second PostgreSQL ran its deadlock check, found the cycle, and cancelled B. A's update went through. B's transaction is now aborted and can only be rolled back.

ROLLBACK;

ROLLBACK

COMMIT;
SELECT id, owner, balance FROM accounts ORDER BY id;

COMMIT
 id | owner | balance
----+-------+---------
  1 | Ana   |  400.00
  2 | Ben   |  600.00
(2 rows)

The first transfer completed. The second one did nothing, and the application has to run it again.

Step 3: read the message in the server log

The client gets one line. The server log gets the whole story, and that is where you look. The message has this shape. The numbers are different on every server, so the placeholders below stand for them.

ERROR:  deadlock detected
DETAIL:  Process <pid_1> waits for ShareLock on transaction <xid_2>; blocked by process <pid_2>.
        Process <pid_2> waits for ShareLock on transaction <xid_1>; blocked by process <pid_1>.
        Process <pid_1>: UPDATE accounts SET balance = balance + 100 WHERE id = 2;
        Process <pid_2>: UPDATE accounts SET balance = balance + 50 WHERE id = 1;
HINT:  See server log for query details.
CONTEXT:  while updating tuple (<page>,<item>) in relation "accounts"

Read it in three passes. The two waits for ... blocked by lines are the cycle: each process waits for a transaction the other one runs. The last two lines of the detail are the statements that were waiting, which point to the code. And CONTEXT names the table. What the log does not show is the earlier statements that took the first locks, so open the application code of both statements and look at what each transaction did before. To catch it in the act rather than after, set log_lock_waits = on in postgresql.conf. Then every lock wait longer than deadlock_timeout is logged, deadlock or not.

Step 4: lock in the same order everywhere

The two transfers took the accounts in different orders: A took 1 then 2, and B took 2 then 1. If every transfer locks both accounts first, in id order, the second transfer waits for the first one, and no cycle can form. Reset the balances, then run the same two transfers again, this time with the lock step first.

Session A

UPDATE accounts SET balance = 500.00;
BEGIN;
SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

UPDATE 2
BEGIN
 id
----
  1
  2
(2 rows)

UPDATE 1

Session B

\set VERBOSITY terse
BEGIN;
SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

BEGIN
 id
----
  1
  2
(2 rows)

B asked for the same two rows and is waiting for A, which is correct and harmless. A finishes its transfer and releases both rows.

Session A

UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

UPDATE 1
COMMIT

B's lock step completes only now, and B runs its own two updates.

Session B

UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;
SELECT id, owner, balance FROM accounts ORDER BY id;

UPDATE 1
UPDATE 1
COMMIT
 id | owner | balance
----+-------+---------
  1 | Ana   |  450.00
  2 | Ben   |  550.00
(2 rows)

Both transfers completed: 500 - 100 + 50 for Ana and 500 + 100 - 50 for Ben.

Step 5: handle what is left

Two things stay true after the fix. A deadlock can still come from a code path that you did not fix, so the application should retry a transaction that fails with the error code 40P01, up to a few times. And a short transaction is a smaller target: do the lock step first, and keep network calls and long calculations outside the transaction.

Related course: SQL Advanced.