Diagnosing PostgreSQL Deadlocks and Retrying Transactions

A deadlock is a cycle of transactions waiting for locks that the other transactions hold. It can be reproduced in a small development database; a production traffic spike is not a prerequisite.

PostgreSQL detects the cycle and aborts a transaction so the others can progress. Do not rely on which transaction becomes the victim. Consistent lock ordering reduces the opportunity for cycles, including ones created by row updates rather than explicit lock statements. PostgreSQL’s locking documentation explains this behavior.

Reconstruct a Small Cycle

Consider two accounts, A and B:

The essential evidence is the order of locks, not a particular time of day or number of users. In a test database, two sessions with explicit pauses between statements can reproduce this sequence.

Application logs should connect the database error to an operation identifier and its transaction boundary. Avoid treating a lock wait as proof of a deadlock: an ordinary wait can end when the other transaction commits.

Inspect the Waiting Work

This read-only diagnostic query shows sessions currently waiting on locks:

SELECT pid, wait_event, query_start,
       pg_blocking_pids(pid) AS blocking_sessions,
       query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY query_start;

Visibility depends on database permissions. Query text can contain sensitive application values, so keep diagnostic output within the operational team. This query captures current waits; a resolved deadlock may need the database’s error log for reconstruction.

Review existing logging with the database operator. Changing cluster-wide settings is not a necessary first step in every investigation.

Make Lock Acquisition Predictable

For transfers between two accounts, all participating code paths can acquire the required account locks in ascending ID order before applying the operation. Validate that both records exist and handle transfers to the same account deliberately.

That rule only addresses the locks it covers. Triggers, foreign-key checks, additional tables and lock upgrades can introduce other relationships. Document a shared order across the complete transaction rather than declaring deadlocks impossible after sorting one array.

Keep transactions short. A remote HTTP request inside a transaction can hold database locks while waiting on an unrelated service.

Retry the Transaction, Not One Failed Statement

The PostgreSQL deadlock SQLSTATE is 40P01. The operation passed to this helper must create a fresh transaction and roll it back before rejecting:

export async function retryDeadlock(operation, {
  attempts = 3,
  sleep = ms => new Promise(resolve => setTimeout(resolve, ms)),
  random = Math.random,
} = {}) {
  if (!Number.isInteger(attempts) || attempts < 1) {
    throw new RangeError("attempts must be a positive integer");
  }
  for (let attempt = 0; attempt < attempts; attempt++) {
    try {
      return await operation();
    } catch (error) {
      if (error?.code !== "40P01" || attempt === attempts - 1) throw error;
      const ceiling = Math.min(1000, 100 * 2 ** attempt);
      await sleep(Math.floor(random() * ceiling));
    }
  }
}

Some database clients wrap SQLSTATE elsewhere; adapt the classification to the actual driver. This helper is not a transaction manager.

Do not send an email or charge a payment method inside a transaction callback that may run again without an idempotency strategy. Retrying database work does not undo a completed external side effect.

Verify the Invariant

Reproduce the original competing operations in an isolated database, then verify both the final records and the expected error handling. Track deadlock frequency and retry exhaustion after release. A passing single-request test does not exercise the conflict.

The successful repair makes lock ownership understandable, keeps retries bounded and preserves the business operation when contention occurs.

Sardar Mukhshaf

Full-Stack & DevOps Engineer

I build scalable web applications, secure backend systems, and production-ready cloud infrastructure. My engineering focus spans interface design, API architecture, deployment pipelines, and observability. Follow for more deep dives into system architecture, performance engineering, and emerging technologies.

ShareTwitterLinkedInCopy Link