A deadlock in PostgreSQL isn’t a hang. The database notices the cycle, picks a victim, and aborts it with an error, usually within about a second. That makes deadlocks less dramatic than their name suggests, but it also means your application will see them as ordinary failed requests unless you plan for them. Interviewers ask about deadlocks to see whether you know the two halves of the answer: change the code so the cycle can’t form, and retry the cases you can’t rule out.
How two transfers deadlock
The textbook case is a money transfer. Every transfer debits one account and credits another:
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = :from;
UPDATE accounts SET balance = balance + 50 WHERE id = :to;
COMMIT;Now run two transfers at the same moment, one from account 1 to account 2 and one in the opposite direction:
time session A (1 -> 2) session B (2 -> 1)
---- ---------------------------------- ----------------------------------
t1 BEGIN; UPDATE ... WHERE id = 1 BEGIN; UPDATE ... WHERE id = 2
(A holds row 1) (B holds row 2)
t2 UPDATE ... WHERE id = 2
waits for B
t3 UPDATE ... WHERE id = 1
waits for A -> cycleEach UPDATE takes a row-level lock that’s held until the transaction ends. At t3 neither session can proceed. When a session has been waiting for deadlock_timeout (1 second by default), PostgreSQL runs its deadlock detector, finds the cycle, and cancels one transaction. The victim’s client receives something like:
ERROR: deadlock detected
DETAIL: Process 4120 waits for ShareLock on transaction 771; blocked by process 4127.
Process 4127 waits for ShareLock on transaction 770; blocked by process 4120.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,1) in relation "accounts"(Process ids and transaction numbers will differ; this is the shape of the message, written from the documented format rather than captured from a live server.) The ShareLock on transaction wording confuses people. It means “waiting for that transaction to finish”, which is how PostgreSQL waits on a row another transaction has updated. The SQLSTATE is 40P01, and that code, not the message text, is what your application should match on.
The other session’s UPDATE then goes through, and it commits normally. One transfer succeeded, one failed, and no data is corrupted. The failure is the whole cost.
Removing the cycle with a consistent lock order
A cycle needs two transactions to acquire the same locks in opposite orders. If every transfer locked the lower account id first, session B would wait for row 1 before ever touching row 2, and there’d be nothing for A to wait on in return. Since UPDATE has no ORDER BY, take the locks explicitly at the start:
BEGIN;
SELECT id FROM accounts
WHERE id IN (:from, :to)
ORDER BY id
FOR UPDATE;
UPDATE accounts SET balance = balance - 50 WHERE id = :from;
UPDATE accounts SET balance = balance + 50 WHERE id = :to;
COMMIT;The SELECT ... ORDER BY id FOR UPDATE locks the rows in ascending id order. Replay the timeline: A locks 1, then 2. B tries to lock 1 first, waits for A, and only continues once A commits. B is delayed by A’s few milliseconds instead of being killed a second later.
The same idea applies wherever a transaction touches several rows:
- Batch updates and upserts. Sort the input by key in the application before sending it. Two jobs upserting overlapping sets of product ids in random order are a classic source of deadlocks in nightly imports.
- Parent and child tables. Decide on an order (for example, always lock the order row before its line items) and follow it everywhere, including in triggers.
- Short transactions. A transaction that holds locks for 5 milliseconds has far fewer chances to collide than one that holds them while calling an HTTP API. Do slow work before
BEGINor afterCOMMIT.
Consistent ordering covers the deadlocks you can see in your own code. It doesn’t cover everything: foreign key checks take share locks on referenced rows, unique index inserts wait on each other, and an ORM may issue statements in an order you didn’t pick. So you also need the second half.
Retrying the whole transaction
When a deadlock error arrives, PostgreSQL has already rolled back the victim’s work, or rather marked the transaction as failed so that every further statement errors until you issue ROLLBACK. Retrying only the statement that failed is a bug: the first UPDATE of the transfer was undone too, so re-running just the credit would create money. The unit of retry is the transaction, starting again from BEGIN, re-reading anything it read before.
Here’s a wrapper for the pg driver’s pool interface. It retries deadlocks and serialization failures (40001, the code Repeatable Read and Serializable transactions raise on conflicts) a small number of times with random, growing delays:
import { setTimeout as sleep } from 'node:timers/promises';
const RETRYABLE = new Set(['40P01', '40001']); // deadlock_detected, serialization_failure
export async function withTransaction(pool, work, { attempts = 3 } = {}) {
for (let attempt = 1; ; attempt++) {
const client = await pool.connect();
try {
await client.query('BEGIN');
const result = await work(client);
await client.query('COMMIT');
return result;
} catch (err) {
await client.query('ROLLBACK').catch(() => {});
if (!RETRYABLE.has(err.code) || attempt >= attempts) throw err;
const delay = Math.random() * 50 * 2 ** attempt; // 0-100ms, then 0-200ms
console.log(`attempt ${attempt} failed with ${err.code}, retrying in ${Math.round(delay)}ms`);
await sleep(delay);
} finally {
client.release();
}
}
}The work callback receives the client and runs every statement of the transaction, so a retry repeats all of them. To exercise the loop without a database, a fake pool can throw an error with code: '40P01' on the first UPDATE and succeed afterwards. Running that with Node 22 prints:
attempt 1 failed with 40P01, retrying in 33ms
transfer committed after 3 UPDATE callsThree UPDATE calls: the failed debit, then the debit and credit of the successful retry. The delay value is random, so yours will differ.
A few choices in that wrapper are worth defending in an interview. The jitter matters because two sessions that deadlocked together and retry after exactly the same pause can collide again. The attempt cap matters because a deadlock that recurs every time is a design bug to fix, not noise to absorb, and you want it in the logs. And the retry list is short on purpose: a unique violation (23505) or a check constraint failure will fail identically on every attempt.
When a retry is not safe
The wrapper assumes the transaction has no effects outside PostgreSQL. If work sends an email, charges a card or publishes to a queue before the deadlock hits, the retry does it again. Keep external calls out of retried transactions, or record the intent in a table inside the transaction (an outbox) and have a separate process act on committed rows. Similarly, if the caller’s request carries an idempotency key, store it in the same transaction so a client-side retry on top of your server-side retry can’t double-apply.
It’s also worth logging every deadlock with the statement that lost, even when the retry succeeds. PostgreSQL writes the full detail, including both queries, to the server log. A steady trickle on one code path points at a lock-order problem you can fix outright.
Interview exercise: the nightly import
A nightly job loads price updates from five CSV files in parallel, each file in its own transaction of a few thousand INSERT ... ON CONFLICT (sku) DO UPDATE statements. Most nights one or two files fail with deadlock detected. The files overlap: popular SKUs appear in several of them. What’s happening, and what would you change?
Answer with reasoning
Each transaction locks SKU rows in file order, and the files list SKUs in different orders. Transaction 1 locks SKU A-100, then wants B-200; transaction 3 already holds B-200 and wants A-100. With thousands of overlapping keys, a cycle is almost guaranteed. The primary fix is ordering: sort each file’s rows by sku before applying them, so every transaction acquires locks in the same sequence and conflicts become short waits. Secondary changes: commit in smaller batches (say 500 rows) so locks are held for less time, and wrap each batch in a retry on 40P01 with a capped attempt count, since a batch is safe to re-run from its start. Merging the five files into one sorted stream before loading also works and may be simpler. Raising deadlock_timeout would only make each failure slower to appear; it fixes nothing.
See also
- SQL interview questions and SQL MCQs
- Row locks, NOWAIT and SKIP LOCKED
- Transaction isolation levels, where serialization failures come from
- PostgreSQL docs: explicit locking and deadlocks