CodePark/ questions / Locking, concurrency & deadlocks
START PRACTICINGPRACTICE
Backend question · Databases & SQL › Locking, concurrency & deadlocks

Explain why these two transactions can deadlock, what PostgreSQL does about it, and how to prevent it.

-- T1                                -- T2
BEGIN;                               BEGIN;
UPDATE accounts SET ... WHERE id=1;  UPDATE accounts SET ... WHERE id=2;
UPDATE accounts SET ... WHERE id=2;  UPDATE accounts SET ... WHERE id=1;
-- each now waits for the other

They deadlock because each transaction waits for a lock the other one holds. T1 holds the row lock on account 1 and waits for account 2. T2 holds the lock on account 2 and waits for account 1. Neither can continue. This is a deadlock.

What PostgreSQL does: when a lock wait lasts longer than deadlock_timeout (1 second by default), PostgreSQL checks for a cycle of waiting transactions. If it finds one, it aborts one transaction with the error deadlock detected (SQLSTATE 40P01). The other transaction continues.

How to prevent it:

  • Lock in a consistent order. Always lock rows in the same order. For example, sort the account ids and update the lower id first. This is the main fix.
  • Keep transactions short, so locks are held for a short time.
  • Avoid lock upgrades, such as taking FOR SHARE on a row and then updating it.

Deadlocks can still happen, so treat 40P01 as a retryable error and run the transaction again.

  • A deadlock is a circular wait: each transaction holds a lock the other one needs.
  • PostgreSQL detects it after deadlock_timeout and aborts one transaction with 40P01.
  • Locking rows in a consistent order prevents most deadlocks.
  • Keep transactions short, and retry when a deadlock happens.