$ cat ~/field-notes/database-deadlocks-explained.md
Database Deadlocks: Why They Happen and How to Stop Them
Two requests are moving money at the same time. Both are valid. Both have plenty of database capacity. Then one fails with deadlock detected.
This is not the database randomly breaking. It is the database protecting you from two transactions that could otherwise wait forever.
What a Deadlock Looks Like
Transaction A locks account 1, then wants account 2. Transaction B locks account 2, then wants account 1.
Transaction A Transaction B
------------- -------------
lock account 1 lock account 2
│ │
wait for account 2 wait for account 1
│ │
└────────── cycle ────────────┘
Neither can continue. Neither can release its first lock until it finishes or rolls back. The database detects the wait cycle, chooses one transaction as the victim, aborts it, and lets the other proceed.
The error is the recovery mechanism.
Reproducing One in PostgreSQL
Open two SQL sessions.
Session A:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- account 1 is now locked
Session B:
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- account 2 is now locked
Back in A:
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- waits for B
Back in B:
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- deadlock: B waits for A while A waits for B
PostgreSQL aborts one transaction with SQLSTATE 40P01. The other update can continue. The aborted transaction must be rolled back before that connection is usable again.
The Best Fix: Consistent Lock Order
If every transfer locks the smaller account ID first, a cycle cannot form.
async function transfer(client, fromId, toId, cents) {
const ids = [fromId, toId].sort((a, b) => a - b);
await client.query('BEGIN');
try {
// ORDER BY documents and enforces one locking order.
const result = await client.query(`
SELECT id, balance
FROM accounts
WHERE id = ANY($1)
ORDER BY id
FOR UPDATE
`, [ids]);
const from = result.rows.find(row => row.id === fromId);
if (!from || from.balance < cents) {
throw new Error('insufficient funds');
}
await client.query(
'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
[cents, fromId],
);
await client.query(
'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
[cents, toId],
);
await client.query('COMMIT');
} catch (err) {
await client.query('ROLLBACK');
throw err;
}
}
The specific order does not matter. Consistency does.
This applies beyond IDs. If an operation locks a customer, subscription, and invoice, every code path should acquire those resource types in the same documented order.
Keep Transactions Short
Locks normally live until transaction end. Every millisecond of unrelated work expands the window in which transactions can collide.
// Bad: database locks are held during unpredictable network I/O.
await client.query('BEGIN');
await client.query('SELECT ... FOR UPDATE');
await chargePaymentProvider();
await client.query('UPDATE orders ...');
await client.query('COMMIT');
Do not make HTTP calls, send email, wait for user input, or perform expensive CPU work inside a transaction. Use a state transition plus an outbox or background job when an external side effect must follow a committed change.
Short transactions do not prove deadlocks are impossible, but they make contention and recovery much cheaper.
Missing Indexes Create Surprise Locks
An update that should find one row can scan thousands if its predicate is not indexed.
UPDATE jobs
SET status = 'expired'
WHERE tenant_id = 42 AND external_id = 'abc';
If (tenant_id, external_id) is not indexed, the database does more work, touches more pages, and holds locks longer. Depending on the engine and isolation mode, scans can also lock ranges rather than just the row you expected.
Use the query plan:
EXPLAIN (ANALYZE, BUFFERS)
UPDATE jobs
SET status = 'expired'
WHERE tenant_id = 42 AND external_id = 'abc';
Run mutating EXPLAIN ANALYZE only in a transaction you intend to roll back or in a safe environment—it actually executes the statement.
Retry the Entire Transaction
Even well-designed systems can deadlock under rare timing. Retrying the aborted unit of work is correct when the operation is safe to repeat.
async function withDeadlockRetry(pool, operation) {
const maxAttempts = 3;
for (let attempt = 1; attempt <= maxAttempts; attempt++) {
const client = await pool.connect();
try {
return await operation(client);
} catch (err) {
const isDeadlock = err.code === '40P01';
if (!isDeadlock || attempt === maxAttempts) throw err;
const jitterMs = Math.floor(Math.random() * 40 * attempt);
await new Promise(resolve => setTimeout(resolve, jitterMs));
} finally {
client.release();
}
}
}
The operation must start a fresh transaction on each attempt. Never retry only the last SQL statement: the database has already aborted the transaction, and earlier reads may no longer be valid.
Also make external effects idempotent or keep them out of the transaction callback. A retried transaction must not charge a card twice.
Deadlock vs Lock Wait
They are related but not identical:
lock wait: A waits for B; B is still doing useful work
deadlock: A waits for B and B waits for A; no one can progress
A lock timeout protects latency when a valid wait takes too long:
SET LOCAL lock_timeout = '1s';
SET LOCAL statement_timeout = '5s';
The database can detect a deadlock before the lock timeout because the wait graph contains a cycle. A long lock wait may simply mean another transaction is slow.
Investigating Production Deadlocks
Capture the database error and transaction context: operation name, table names, attempt number, and database error code. Do not log sensitive query parameters.
For PostgreSQL, inspect current blockers:
SELECT
blocked.pid AS blocked_pid,
blocker.pid AS blocker_pid,
blocked.query AS blocked_query,
blocker.query AS blocker_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocker
ON blocker.pid = ANY(pg_blocking_pids(blocked.pid));
By the time you investigate a deadlock, the victim is already gone. Database deadlock logs are therefore more useful than a snapshot taken minutes later. Preserve them and map the statements back to application operations.
The Bottom Line
Deadlocks are cycles in lock acquisition, not mysterious database instability.
The rules:
- Lock shared resources in one consistent order
- Keep transactions short and free of network calls
- Index predicates used by updates and locking reads
- Treat the deadlock error as an aborted transaction
- Retry the entire transaction with a small attempt limit and jitter
- Keep retried operations idempotent
- Capture database deadlock logs and operation names
- Distinguish an actual cycle from an ordinary long lock wait
Retries make rare deadlocks survivable. Consistent ordering makes them rare in the first place.