PostgreSQL deadlock detected: use the lock graph to repair concurrency order

Scope and prerequisites

PostgreSQL transactions and row/table locks. Diagnostics require appropriate monitoring privileges; managed database access may be limited.

Capture the deadlock error and transaction boundaries. Queries and logs can contain customer data. Prefer EXPLAIN without ANALYZE for an initial plan; EXPLAIN ANALYZE actually executes the statement.

Common symptoms

• Applications receive deadlock detected.
• The same business action fails intermittently and succeeds on retry.
• High-concurrency writes increase tail latency.

1. Capture the complete context

Deadlock detail in PostgreSQL logs includes the wait relationship and SQL. Join that evidence to request traces, transaction boundaries, and table access order before attempting a fix.

SHOW log_lock_waits;
SHOW deadlock_timeout;
SELECT pid, wait_event_type, wait_event, state, query FROM pg_stat_activity WHERE state <> 'idle';

2. Find inconsistent lock acquisition

A common pattern is one transaction locking orders then inventory while another does the reverse. Apply a consistent resource order, reduce transaction scope, and keep remote calls outside transactions.

SELECT locktype, relation::regclass, mode, granted, pid FROM pg_locks WHERE NOT granted OR relation IS NOT NULL;

3. Use observable, bounded retry

An idempotent transaction can retry deadlock errors with bounded backoff, but retries must emit metrics and business context rather than silently hiding a systemic pattern.

SELECT datname, deadlocks, stats_reset FROM pg_stat_database WHERE datname = current_database();

Interpret the evidence

Illustrative diagnosis

A hypothetical example to explain the reasoning, not a reported customer incident or a claim of testing on your stack.

Transaction A updates inventory row 10 then row 20, while transaction B updates 20 then 10. Each can hold the row the other needs. Sorting identifiers before acquiring locks addresses this particular cycle; blindly raising a timeout does not. Reproduce the order in a test database and include all statements in the transaction, not only the query that reported the error.

Verify recovery

• Run concurrent versions of the affected business action in staging and confirm both database invariants and the absence of the same lock cycle.
• Compare deadlock counts, retry counts and latency under representative load. A hidden retry loop can make errors appear fixed while latency worsens.

Rollback and stopping point

Deploy lock-order or retry changes with a reversible application release. A code rollback cannot undo duplicate external side effects; confirm idempotency and avoid mixing old/new lock orders during rollout.