What is a deadlock in PostgreSQL and how do you avoid it?
A deadlock occurs when two transactions each hold a lock the other needs, so neither can proceed. PostgreSQL detects it after deadlock_timeout (1 second by default) and aborts one transaction. Prevent it by locking rows in a consistent order.
Explain it simply
Two friends each hold one pen and each need the other's pen to finish writing. Both wait politely forever. Database deadlocks are the same: transaction A holds row 1 and wants row 2, while transaction B holds row 2 and wants row 1.
PostgreSQL notices the cycle, cancels one transaction with an error, and the other continues.
Example
-- Session A -- Session B
BEGIN; BEGIN;
UPDATE acct SET bal = bal - 100 UPDATE acct SET bal = bal - 50
WHERE id = 1; WHERE id = 2;
UPDATE acct SET bal = bal + 100 UPDATE acct SET bal = bal + 50
WHERE id = 2; -- waits for B WHERE id = 1; -- waits for A
-- ERROR: deadlock detectedFix: always touch rows in the same order, for example lowest account id first, in every transaction:
SELECT id FROM acct WHERE id IN (1, 2) ORDER BY id FOR UPDATE;Also keep transactions short, and make the application retry the aborted one.
What to say in the interview
"A deadlock is a circular wait between transactions. PostgreSQL detects it and aborts one with an error. I prevent it by acquiring locks in a consistent order, keeping transactions short, and retrying on the deadlock error code 40P01."
Common mistakes
- Not retrying after a deadlock error and surfacing it to the user.