A deadlock happens when two transactions each hold a lock the other needs, so neither can proceed. PostgreSQL detects the cycle and aborts one with error code 40P01. Deadlocks are a normal consequence of concurrency, so you prevent the common ones and retry the rest.
Before you start
You should be comfortable with transactions, FOR UPDATE and row locks. This article uses PostgreSQL and assumes an application that can catch and retry.
Step-by-step walkthrough
Step 1: Lock rows in a consistent order
The most common deadlock is two transactions locking the same two rows in opposite order. Always access shared rows in a fixed order, such as ascending id, so the lock acquisition can never form a cycle. This removes most deadlocks by design.
Step 2: Keep transactions short
The longer a transaction holds locks, the larger the window for a cycle and the more it blocks others. Do not hold a transaction open across a network call or user think time; do the fast database work and commit.
Step 3: Retry on the deadlock error
When you cannot eliminate a deadlock, catch the 40P01 error and retry the whole transaction. The retry must redo the work from the beginning, because the transaction was rolled back, and it should use backoff so retries do not collide again.
Worked scenario
A transfer locks rows in a fixed order and retries on deadlock.
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
SELECT balance FROM accounts WHERE id = 2 FOR UPDATE;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;Walk through the example
By locking account 1 before account 2, any transfer between them takes the locks in the same order, so two concurrent transfers cannot form a cycle. If the order depended on user input, one transaction might lock 2 then 1, which is the classic deadlock. The application retries the whole block if it still receives 40P01.
Common mistake
Locking rows in the order they appear in the request, which varies, and then handling deadlocks as a fatal error instead of retrying. Another is a long transaction that holds locks while waiting on an external service.
Verify the behavior
Run two concurrent transactions that lock the same rows in opposite order and observe the 40P01 abort, then fix the order and confirm no deadlock. Add a bounded retry loop and confirm a transient deadlock results in a successful commit. Check that the retry redoes the full transaction.
Interview exercise
Why must a deadlock retry restart the entire transaction?
Answer and reasoning
When PostgreSQL aborts a transaction to break the deadlock, it rolls back all its changes, so any work already done is gone. The retry must therefore redo the reads and writes from the start, not resume midway, or it would operate on state that no longer exists. Keep transactions short so a restart is cheap.
Continue learning
Compare locking in Row locks and Transaction isolation. Read the PostgreSQL explicit locking documentation and try the SQL interview questions.