Deadlocks and lock ordering

Transactions, isolation and locking Mid-level Python 3.13+ PostgreSQL 17+ Reviewed 31 Oct 2026 By Best Latest Apps

Spot the bug

Two concurrent transfers in opposite directions sometimes deadlock. Which line causes it?

import psycopg
from psycopg.rows import TupleRow
def transfer(
conn: psycopg.Connection[TupleRow], src: int, dst: int, amount: int
) -> None:
with conn.transaction():
conn.execute(
"UPDATE accounts SET balance = balance - %s WHERE id = %s",
(amount, src),
)
conn.execute(
"UPDATE accounts SET balance = balance + %s WHERE id = %s",
(amount, dst),
)

Hint 1 of 1

Compare the lock order of transfer(1, 2) and transfer(2, 1).

Choose an answer, then open Show answer below to check it.

Show answer

Answer: line 9 conn.execute(

Each transfer locks its source row first. transfer(1, 2) locks 1 then wants 2; transfer(2, 1) locks 2 then wants 1: a cycle. Lock both rows in id order first, e.g. SELECT ... WHERE id IN (%s, %s) ORDER BY id FOR UPDATE.

30-second answer

A deadlock is a cycle: transaction A holds a lock B needs while B holds one A needs. PostgreSQL checks for cycles after waiting deadlock_timeout (1 second by default) and aborts one transaction with SQLSTATE 40P01; the other proceeds. Prevent most deadlocks by always locking rows in the same order, for example by sorting ids before updating, keeping transactions short, and retrying the victim.

Practise saying it · 30 s. No recording, just a timer

Explain database deadlocks.

Say your answer out loud, then check which of these points you covered:

Lock cycle between two transactions
Detected after deadlock_timeout
One aborted with 40P01
Lock in a consistent order
Retry the victim

Deep dive

Classic case: two transfers, A→B and B→A, each update their source row first.

Foreign-key checks and unique indexes take locks you didn't write explicitly.

log_lock_waits = on logs waits longer than deadlock_timeout, which shows contention before it becomes deadlocks.

Application-level locks (mutexes, Redis locks) can deadlock with database locks too.

Common mistakes

Treating 40P01 as a fatal error instead of retrying.
Updating rows in request order, which differs per request.
Raising deadlock_timeout to 'fix' deadlocks.

Senior take & follow-ups

Give the cycle, the detection, and the prevention: consistent ordering plus retries.

Follow-up questions

Think of your answer first, then open the hint.

How do you find who blocks whom right now?
Show hint
pg_stat_activity with pg_blocking_pids(pid).
Is a lock timeout the same as deadlock detection?
Show hint
No; lock_timeout aborts any wait that's too long, cycle or not.

Similar on iOS: DispatchQueue.main.sync on the main thread On iOS the cycle is two threads or queues waiting on each other; nothing detects it, the app just hangs.

Suggest a fix if something here is wrong or out of date.