Understanding and Preventing Deadlocks in PostgreSQL

A deadlock is two transactions each holding a lock the other needs, both waiting forever. Postgres won't actually wait forever: it detects the cycle and kills one of the transactions so the other can proceed. But the killed transaction fails with a deadlock error, and if your application doesn't handle it, that's a user-facing error and possibly lost work. Deadlocks are almost always a design issue, and the fix is usually a small change to the order you touch rows.

This is part 20 of the PostgreSQL Masterclass, following locks.

How a deadlock forms

Deadlocks need two transactions acquiring the same locks in opposite orders. The classic example is a money transfer between two accounts, where two transfers run in opposite directions at once:

-- Transaction A: transfer from account 1 to 2
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- locks row 1
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- wants row 2

-- Transaction B: transfer from account 2 to 1 (at the same time)
BEGIN;
  UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- locks row 2
  UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- wants row 1

A locks row 1 and wants row 2. B locks row 2 and wants row 1. Neither can proceed. That's a deadlock. Postgres notices after a short delay, picks one transaction as the victim, and aborts it:

ERROR: deadlock detected
DETAIL: Process 123 waits for ShareLock on transaction 456; blocked by process 789.

The main fix: consistent lock ordering

The reason that example deadlocks is that the two transactions lock rows in opposite orders. If every transaction always locks rows in the same order, a cycle can't form. Order the updates by primary key, and both transactions touch row 1 before row 2:

-- both transactions lock lower id first, so no cycle is possible
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

For the transfer case, sort the two account ids and always update the smaller one first, regardless of transfer direction. More generally: when a transaction updates multiple rows, process them in a deterministic order (by primary key is the simplest) everywhere in your codebase. This single discipline prevents the large majority of deadlocks.

Lock rows up front when you can

Another effective pattern is to acquire all the locks you'll need at the start, in order, with a single SELECT ... FOR UPDATE. Locking both rows in one ordered statement means you never hold one while reaching for another:

BEGIN;
  SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
  -- both rows locked in id order; now update freely
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

The ORDER BY id in the locking SELECT is what makes it safe: every transaction that locks these rows does so in the same sequence.

Keep transactions short and focused

The longer a transaction holds locks, the wider the window for a deadlock. The same "short transactions" advice from earlier applies directly. A transaction that locks a row, then makes a slow external call, then locks another row, is a deadlock generator. Acquire locks close together, do the work, commit.

Foreign keys can also contribute. Inserting a child row takes a lock on the referenced parent row to ensure it isn't deleted mid-insert. If different transactions touch parent rows in different orders through their child inserts, you can deadlock without any obvious multi-row update in sight. Consistent ordering helps here too.

Handle the error: retry

Even with good design, deadlocks can happen under heavy concurrency, so applications should treat a deadlock as a retryable error. When you catch the deadlock error code, wait a brief random moment and retry the whole transaction. Because deadlocks are timing-dependent, the retry almost always succeeds:

// pseudocode
for attempt in 1..3:
  try:
    run_transaction()
    break
  catch DeadlockError:      // SQLSTATE 40P01
    sleep(random_small_backoff)
    continue

This is the same retry pattern that serialization failures need, so a single "retry on these SQLSTATEs" wrapper handles both 40P01 (deadlock) and 40001 (serialization failure). Any application doing concurrent writes should have this wrapper.

Diagnosing them

Postgres logs every deadlock with the queries involved, so the log is the first place to look. log_lock_waits = on additionally logs long lock waits before they become deadlocks, which helps you spot contention hotspots early. When deadlocks recur, the log's DETAIL line shows exactly which transactions and locks formed the cycle, and that almost always points at two code paths touching the same rows in different orders.

Deadlocks feel scary because the error is dramatic, but the mental model is simple: they come from inconsistent lock ordering, and consistent ordering plus a retry wrapper eliminates nearly all of them in practice. Lock rows in a deterministic order, keep transactions short, and retry on the error, and deadlocks stop being something you worry about.

Next, we step up to a design-level decision that sits above all this locking: optimistic versus pessimistic concurrency control, and when each approach fits.