Database lock-wait debugging checklist

The symptoms

• •Application queries hang or time out with errors referencing serialization failure, deadlock detected, or lock wait timeout (SQLSTATE 55P03, 40P01) while other transactions continue normally.
• •Specific worker processes show non-zero wait_event_type 'Lock' with wait_event names such as 'relation', 'extend', 'tuple', 'transactionid', or 'object' in pg_stat_activity.
• •Latency for a single table or row rises sharply even though CPU, IO, and replication are within baseline, and the spike correlates with the start of a long-running transaction elsewhere.
• •Idle-in-transaction sessions accumulate on the database, and removing the oldest one immediately unblocks downstream queries.
• •Batched jobs stall mid-stream while the same application continues to serve unrelated reads on other tables without contention.

Likely causes

• •An exclusive ACCESS EXCLUSIVE or row-level FOR UPDATE lock is held by a transaction that also performs slow IO, blocking readers and writers on the same relation or row.
• •Long-running or idle-in-transaction sessions accumulate because the application failed to close transactions on error paths, creating a lock-holder chain.
• •Schema migrations, VACUUM FULL, REINDEX, or ALTER TABLE run in the same window as application traffic and acquire locks incompatible with normal DML.
• •Application logic escalates from row-level to relation-level locks unnecessarily, for example by issuing LOCK TABLE inside a routine code path.
• •Hot-row contention where many transactions update the same tuple, causing transactionid waits visible only under concurrency rather than steady load.

First ten minutes

1. 01Confirm the symptom is a lock wait and not CPU, IO, or network saturation by inspecting wait_event_type distribution in pg_stat_activity for affected processes.
2. 02Capture the current snapshot of pg_locks joined to pg_stat_activity to identify each blocked session, the lock type, and the blocking pid; do not terminate anything yet.
3. 03Record the application statement, xact_start, query_start, and state of both the blocked and blocking sessions so the cause can be reasoned about before any change.
4. 04Classify each wait into relation-level, tuple-level, transactionid, or object lock; this determines whether the fix is at the query, schema, or transaction-management layer.
5. 05Cross-reference blocking pids with recent application deploys, migration windows, or maintenance jobs to localize the responsible component before contacting owners.
6. 06Decide whether to wait, fail fast, or escalate based on whether the blocking transaction is making forward progress; a static wait_event across samples means the holder is stalled, not busy.

Evidence to collect

• •pg_stat_activity rows for blocked sessions showing wait_event_type 'Lock' and a non-null wait_event, plus xact_start and query_start timestamps.
• •pg_locks rows that are granted=false, with locktype, relation::regclass, mode, granted, and the blocking pid derived from the blocking_locks view.
• •The blocking session's current query, application_name, client_addr, and state to attribute the wait to a specific service or job.
• •Lock-type distribution over the incident window, distinguishing relation, tuple, transactionid, extend, and object waits.
• •Idle-in-transaction age distribution and a count of sessions whose xact_start is older than a chosen threshold to quantify stranded transactions.
• •Server log entries for log_lock_waits, deadlock detection, and the timestamps where lock_wait_timeout fires, matched to the captured pids.

Where to look

• •System catalog views pg_locks and pg_stat_activity, plus the diagnostic view pg_blocking_pids and the helper function pg_blocking_pids(pid).
• •PostgreSQL wait-event documentation to map wait_event names to their underlying lock semantics rather than guessing from the string.
• •Server log when log_lock_waits is enabled, so blocked sessions and their blockers are persisted with statement text and timing.
• •Application connection-pool boundary, including pool configuration for idle-in-transaction timeout and statement timeout, since the pooler often hides long-held transactions from the database log.
• •Migration and maintenance job runners, because schema changes and autovacuum-related work are the most common sources of unexpected ACCESS EXCLUSIVE holds.

Diagnostic steps

1. 01Run a read-only join of pg_stat_activity and pg_locks restricted to rows where wait_event_type='Lock' to enumerate blocked sessions and the lock modes they are waiting for.
2. 02For each blocked pid, call pg_blocking_pids(pid) to walk the blocker chain, and capture the head session's xact_start and query to identify the responsible transaction.
3. 03Classify waits by locktype: relation and extend point to DDL or table-level contention; tuple and transactionid point to row-level contention under FOR UPDATE or UPDATE; object points to advisory or user-lock contention.
4. 04Compare blocking xact_start against application batch boundaries and migration timestamps to determine whether the holder is a routine job, a stuck worker, or an idle connection.
5. 05Enable log_lock_waits in a non-production setting or during a controlled window, then re-trigger the symptom to obtain persisted statement text for both blocker and blocked sessions.
6. 06Sample pg_stat_activity every few seconds during the symptom; if the blocking session's query changes, it is making progress and waiting is rational; if it is static, the holder is stalled and intervention is justified.
7. 07Validate that the symptom is not actually lock_manager contention by checking wait_event names distinct from Lock, such as LWLock or BuffPins, before attributing the stall to user-visible locks.

Common mistakes

• •Killing the blocked session instead of the holder, which removes the symptom but preserves the underlying stuck transaction and risks data divergence if the holder later commits.
• •Misreading granted=false in pg_locks as the head of the blocker chain; granted can be true on the head, so always derive the blocker via pg_blocking_pids rather than the granted flag alone.
• •Treating every long transaction as harmful without verifying the lock mode it actually holds; a long read-only transaction does not block writes unless it has acquired an explicit lock.
• •Assuming tuple waits are always row-level contention; some wait_event names such as 'extend' indicate relation-extension locks and require a different fix path.
• •Restarting the database to clear locks, which obscures the root cause and can corrupt in-flight migrations if an ACCESS EXCLUSIVE lock was mid-acquire.
• •Disabling autovacuum as a response to lock waits, which converts a one-time symptom into ongoing bloat and future lock escalation.

Safe fixes

• •If evidence shows an idle-in-transaction holder older than a documented threshold, terminate only that session with pg_terminate_backend after notifying its owner, then re-check pg_stat_activity.
• •If evidence shows a long but progressing maintenance operation (for example REINDEX or VACUUM FULL) holding ACCESS EXCLUSIVE, defer it to a low-traffic window rather than killing it mid-run.
• •If evidence shows row-level contention on a hot tuple, reduce transaction scope by committing smaller batches, retrying on serialization failure, and moving the hot path to a single-writer queue.
• •If evidence shows routine code acquiring relation locks, remove the LOCK TABLE statement and rely on row-level locking, validating with a load test that mimics the contention pattern.
• •If evidence shows the application's idle_in_transaction_session_age or statement_timeout is unset, configure them to values aligned with the documented SLOs and observe the next incident.
• •If evidence shows migration locks colliding with traffic, separate the migration runner's connection pool from the application's and route DDL through a dedicated, low-concurrency channel.

Prove the fix

1. 01During a regression test that reproduces the original query mix, the count of pg_stat_activity rows with wait_event_type='Lock' returns to its documented baseline within one sampling interval.
2. 02pg_blocking_pids returns an empty array for the previously affected pids under the same load profile, and no new blocked sessions appear over a 30-minute observation window.
3. 03Application p99 latency for the affected query path returns to within an agreed tolerance of the pre-incident baseline, while unrelated query paths remain unchanged.
4. 04Idle-in-transaction session count stays below the configured threshold across one full business cycle, confirmed by a periodic snapshot of pg_stat_activity.
5. 05No log_lock_waits entries are emitted during the regression test, and any prior deadlock-detected entries do not recur under the same workload shape.

Prevention and next steps

• •Set idle_in_transaction_session_timeout and statement_timeout to values derived from the application's SLOs, and surface violations through alerting rather than waiting for user-visible timeouts.
• •Route schema changes and maintenance through a dedicated, low-concurrency migration channel that is documented to take relation-level locks, and schedule it outside peak traffic windows.
• •Keep transactions short by committing per logical unit of work, and audit code paths that open a transaction before user input is consumed.
• •Capture a periodic snapshot of pg_blocking_pids distribution to baseline the normal blocker graph and detect drift before it becomes an outage.

Safe commands and checks

SELECT pid, usename, application_name, state, wait_event_type, wait_event, xact_start, query FROM pg_stat_activity WHERE wait_event_type='Lock' ORDER BY xact_start;
SELECT blocked.pid AS blocked_pid, blocked.wait_event, blocked.query AS blocked_query, pg_blocking_pids(blocked.pid) AS blocking_pids FROM pg_stat_activity AS blocked WHERE blocked.wait_event_type='Lock';
SELECT locktype, mode, granted, pid, relation::regclass FROM pg_locks WHERE NOT granted ORDER BY locktype, relation;
SELECT pid, xact_start, state, query FROM pg_stat_activity WHERE state IN ('idle in transaction','idle in transaction (aborted)') ORDER BY xact_start;
SELECT name, setting, unit FROM pg_settings WHERE name IN ('log_lock_waits','idle_in_transaction_session_timeout','statement_timeout','lock_timeout');