PostgreSQL deadlock detected: how to diagnose and prevent deadlocks

ERROR: deadlock detected means PostgreSQL aborted one transaction to break a circular wait-for graph. The victim returns SQLSTATE 40P01; the application must retry it. Deadlocks are a safety mechanism, not a bug: they fire when concurrent transactions acquire locks in incompatible orders. Even a few per minute degrade user experience, burn retry budget, and mask deeper contention. This guide shows how to read the deadlock output, find the root cause, and stop the cycle.

What this means

PostgreSQL detects deadlocks by traversing the wait-for graph of blocked backends. The detector wakes whenever a process has waited longer than deadlock_timeout (default one second). If it finds a cycle, it selects a victim transaction and aborts it. The aborted backend receives an error with a DETAIL line listing the wait-for graph: each blocked process, the lock mode it holds, the lock mode it waits for, and the PID of the blocker. The HINT line tells you to check the server log for the full query text. Detection is not instantaneous. Because the detector only runs after a backend has waited for deadlock_timeout, a query may hang for up to that duration before PostgreSQL aborts the victim and returns the error.

flowchart LR
    A["Transaction A
holds lock on row 1"]
    B["Transaction B
holds lock on row 2"]
    A -->|waits for row 2| B
    B -->|waits for row 1| A

Common causes

CauseWhat it looks likeFirst thing to check
Inverted row-lock order in bulk DMLTwo identical UPDATE or upsert statements deadlock because they touch the same rows in different key order.Whether application sorts keys before issuing the statement.
Queue workers polling with SELECT FOR UPDATEMultiple workers race for the same rows; without ordering or skipping, they form a cycle.Whether the queue query uses SKIP LOCKED.
Idle-in-transaction sessions holding row locksAn abandoned transaction holds RowExclusiveLock while a DDL statement or another DML waits behind it.pg_stat_activity for idle in transaction with old xact_start.
DDL mixed with concurrent DML and selectsALTER TABLE waits for an AccessExclusiveLock behind a long SELECT; new DML queues behind the DDL.pg_locks for AccessExclusiveLock waiters and the granted AccessShareLock holding them.
Advisory or explicit table locks in mismatched orderApplication locks tables A then B in one path and B then A in another.Application code for explicit LOCK TABLE or advisory lock sequences.

Quick checks

Run these read-only queries to assess the current state.

# Verify deadlock timeout and lock-wait logging
psql -c "SHOW deadlock_timeout;" -c "SHOW log_lock_waits;"

# Cumulative deadlocks since stats reset
psql -c "SELECT datname, deadlocks, stats_reset FROM pg_stat_database WHERE deadlocks > 0;"

# Pending lock requests (relation will be NULL for non-relation locks)
psql -c "SELECT pid, locktype, mode, granted, relation::regclass, transactionid FROM pg_locks WHERE NOT granted;"

# Backends currently blocked on locks
psql -c "SELECT pid, usename, state, wait_event_type, wait_event, left(query,80) AS query FROM pg_stat_activity WHERE wait_event_type = 'Lock';"

# Idle-in-transaction sessions that may hold locks
psql -c "SELECT pid, state, xact_start, now() - xact_start AS xact_age, left(query,80) AS query FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY xact_start;"

# Verify the log line prefix includes %p so DETAIL PIDs match log entries
psql -c "SHOW log_line_prefix;"

How to diagnose it

  1. Read the deadlock DETAIL. The log entry contains ERROR: deadlock detected and a DETAIL block listing the cycle: each blocked process, the lock mode it holds, the lock mode it waits for, and the PID of the blocker. Note the PIDs, lock modes (for example, ShareLock vs ExclusiveLock), and whether the contested object is a relation, tuple, or transaction ID.
  2. Map the wait-for graph. Draw the cycle from the DETAIL: PID 123 waits on PID 456, which waits on PID 123. If the graph has more than two edges, the cycle involves three or more transactions.
  3. Find the queries. The HINT line tells you to check the server log for the full query text. If the incident is recent, pg_stat_activity may still show the query for surviving backends: SELECT pid, query FROM pg_stat_activity WHERE pid IN (...);
  4. Identify the contested object. Query pg_locks for the PIDs involved. Look for relation locks on specific tables, tuple locks on specific rows, or transactionid locks when the application updates the same rows in different order.
  5. Check for a long-running blocker. If one PID in the cycle is idle in transaction, that session is the enabler. Terminate it with pg_terminate_backend(pid) only after confirming the application can tolerate the rollback.
  6. Review log_lock_waits output. If log_lock_waits = on, PostgreSQL logs any lock wait that exceeds deadlock_timeout. Grepping the log for wait lock or still waiting in the seconds before the deadlock shows which queries were queuing and how long they stalled.
  7. Reproduce locally. Open two psql sessions, run BEGIN, and issue the same DML in overlapping order to recreate the cycle. If you cannot reproduce with two sessions, the deadlock may require a third transaction or an index-gap lock.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
pg_stat_database.deadlocksCumulative count of deadlocks. A sudden increase confirms the problem is active.Non-zero delta over a 5-minute window.
pg_stat_activity.wait_event_type = 'Lock'Shows active lock waits that may be seconds away from becoming a deadlock.Sessions waiting longer than deadlock_timeout.
pg_locks ungranted countDirect view of pending lock requests. A sustained queue means contention.Any ungranted row-level or transaction ID locks persisting longer than 1 second.
idle in transaction session ageLong idle transactions hold locks and enable cycles involving DDL or DML.Any session in idle in transaction longer than 2 minutes.
log_lock_waits emissionsProactive logging of lock waits that exceed the timeout.Repeated waits on the same relation or transaction ID.
Lock wait rate vs. transaction rateRising lock-wait percentage means the workload is becoming more serial.Lock waits exceeding 5 percent of active sessions.

Fixes

Retry the victim with backoff

PostgreSQL broke the cycle by aborting one transaction. The application must retry that transaction with a jittered exponential backoff. Do not retry immediately in a tight loop; that can recreate the same cycle. Ensure the retried statement is idempotent, because the victim may have fired triggers or written to external systems before the abort. Log each retry so you can detect if the same transaction deadlocks repeatedly.

Reduce batch size

If deadlocks occur during large UPDATE or DELETE statements, shrink the batch. Fewer rows per transaction means fewer simultaneous row locks and a smaller window for ordering conflicts.

Sort keys before bulk DML

If deadlocks happen during batch upserts or updates, ensure all transactions visit rows in the same primary-key or index order. Two transactions inserting the same set of keys in different orders will deadlock because PostgreSQL acquires row locks in the order the executor visits them. Sorting the input set in the application before the UPDATE or INSERT ... ON CONFLICT is usually enough.

Use SKIP LOCKED for queue workers

If workers implement a queue with SELECT FOR UPDATE, replace it with SELECT FOR UPDATE SKIP LOCKED. This removes the race condition because workers skip rows already locked rather than waiting.

Shorten or terminate idle-in-transaction sessions

Set idle_in_transaction_session_timeout to a value appropriate for your workload. Start with two minutes. For existing offenders, terminate the backend with pg_terminate_backend(pid) only if the application can tolerate the rollback.

Guard DDL with lock_timeout

Schema changes that need AccessExclusiveLock can create cascading queues. Before running DDL, set SET lock_timeout = '1s'; so the statement fails fast instead of backing up the lock queue. Schedule heavy DDL during maintenance windows.

Shrink transaction scope

The shorter a transaction holds locks, the smaller the window for a cycle. Avoid calling external services, long-running computations, or waiting for user input inside a database transaction. If you need to validate data against an external API, do it before you open the transaction or after you commit.

Prevention

  • Establish application-level lock ordering. If multiple code paths touch the same tables or rows, define a canonical order (for example, alphabetical by table name, ascending by primary key) and enforce it everywhere.
  • Enable log_lock_waits = on. This is a production baseline. It logs lock waits longer than deadlock_timeout, giving you early warning before deadlocks form.
  • Set idle_in_transaction_session_timeout. Do not leave the default at 0. A value between 60 and 300 seconds prevents abandoned transactions from holding locks indefinitely.
  • Set lock_timeout in application sessions. A conservative lock_timeout acts as a circuit breaker: the query fails instead of joining a queue that may deadlock.
  • Adopt SKIP LOCKED for work queues. It is the correct primitive for multi-worker queue processing.
  • Keep DDL separate from heavy DML windows. ALTER TABLE, CREATE INDEX without CONCURRENTLY, and similar operations mix poorly with high concurrency.
  • Monitor pg_stat_database.deadlocks as a KPI. A steady state of zero is achievable. If deadlocks appear after a deploy, treat them as a release-blocking regression.

How Netdata helps

  • Netdata collects pg_stat_database.deadlocks, letting you correlate spikes with deploys or traffic shifts.
  • It breaks down pg_stat_activity by wait_event_type, surfacing lock-waiting sessions and block duration.
  • It tracks idle-in-transaction count and age, which often enable deadlock cycles.
  • Database metrics are shown alongside system metrics (disk I/O, CPU) to help distinguish lock contention from resource exhaustion.
  • Alarms on nonzero deadlock rates or sustained lock waits can page the on-call before the backlog cascades.