How to verify a PostgreSQL lock waiter recovers

The symptoms

• •A foreground query in psql or an application session is stuck on a simple UPDATE, INSERT, SELECT FOR UPDATE, or DDL statement that should normally return in milliseconds.
• •Applications report request timeouts or connection-pool exhaustion while individual transactions remain in the database in an idle-in-transaction or active state.
• •PostgreSQL log entries show long-running statements and, in some configurations, "lock waits" or "waiting for" messages, but the query never completes or errors out.
• •Metrics show backend wait_event of Lock or LockTransaction, yet no deadlocks are reported by the lock manager.

Likely causes

• •The waiter is blocked on a row, tuple, relation, or transaction lock that the holder has not yet released, and recovery depends on the holder's COMMIT or ROLLBACK.
• •The holder is itself blocked on a different lock held by a third session, creating a chain where the waiter can only resume once the entire chain unwinds.
• •Long-running transactions in idle-in-transaction state from a failed or abandoned application connection hold locks that block otherwise normal traffic.
• •Autovacuum or other maintenance background workers hold conflicting locks that prevent user transactions from acquiring the same relation or tuple.
• •Misconfigured lock_timeout or statement_timeout values are not set, so the waiter waits indefinitely instead of failing fast with a predictable error.

First ten minutes

1. 01Record the time, the connection or application host, and the exact query that appears stuck, so any comparison of state is anchored to a moment.
2. 02Capture pg_stat_activity for the database in question and identify sessions whose wait_event is Lock or LockTransaction, noting pid, state, and xact_start.
3. 03Note who is holding the lock versus who is waiting, so you can answer "who is blocking whom" before touching anything.
4. 04Decide whether the holder is a real user transaction, an abandoned idle-in-transaction session, or a background worker; the recovery path differs for each.
5. 05Decide whether you need to verify automatic recovery (holder finishes normally) or forced recovery (holder is terminated); pick the verification scenario before changing anything.

Evidence to collect

• •pid, state, wait_event, wait_event_type, query, xact_start, and query_start for both the suspected waiter and the suspected holder from pg_stat_activity.
• •Lock rows from pg_locks joined to pg_stat_activity showing locktype, relation, tuple, transactionid, granted, and pid, so waiter-versus-holder is unambiguous.
• •Current setting and application_name of both sessions, to distinguish application transactions from autovacuum or replication workers.
• •Whether lock_timeout is set on the waiter session, because an unset lock_timeout means the wait will not fail predictably on its own.
• •PostgreSQL log timestamps for the waiter's statement and the holder's statement, to confirm duration and ordering against the time the verification started.

Where to look

• •pg_stat_activity and pg_locks in the target database, joined on pid, are the canonical place to see live waits and their blockers.
• •The PostgreSQL log directory for the cluster, focusing on statements that started before the wait began and any "waiting for" or "process N acquired" lines.
• •The application or connection-pool layer, because a stuck application connection is the surface that surfaces the underlying lock wait.
• •The database's lock_timeout and idle_in_transaction_session_timeout settings, because they decide whether a wait will time out or wait indefinitely.
• •pg_stat_progress_vacuum and pg_stat_activity for autovacuum workers, if a maintenance worker is the holder and you need to confirm it is making progress.

Diagnostic steps

1. 01Determine which sessions are blocked by running a query that lists waiters and the pid of the blocking session, joined from pg_locks, and confirm the wait_event is Lock or LockTransaction.
2. 02For each waiter, trace the blocking chain: if the waiter's blocker is itself blocked by another session, record the full chain so you know who must release first.
3. 03Classify the holder: an active user transaction, an idle-in-transaction session, an autovacuum worker, or a replication-related process. Each class has a different recovery behavior.
4. 04Confirm the waiter's lock_timeout: if it is zero, the waiter will wait until the holder releases; if it is set, the waiter will fail with a timeout error after the configured interval.
5. 05Decide the verification scenario: a clean holder commit, a holder rollback, or a holder terminated by pg_terminate_backend; pick the one that matches the production evidence.
6. 06Verify the holder is not itself blocked indefinitely before pushing it forward, otherwise the waiter recovery will not be observable in a reasonable window.
7. 07Re-snapshot pg_stat_activity and pg_locks immediately after the holder releases, and confirm the waiter's wait_event changes from Lock away within a short, bounded window.

Common mistakes

• •Treating "no deadlock reported" as proof that recovery will happen quickly; absence of a deadlock graph only means the manager did not pick a victim, not that waits are short.
• •Terminating the wrong session: the waiter and the holder look similar in pg_stat_activity, and killing the waiter drops the wait but does not prove recovery.
• •Ignoring a blocking chain where the apparent holder is itself waiting on another session, so the "release" never reaches the original waiter.
• •Forgetting that idle-in-transaction sessions hold locks even when they issue no further statements, so the holder may have stopped working long ago.
• •Assuming lock_timeout is set when it is not; without a timeout, the verifier must release the holder manually or wait for it to finish.

Safe fixes

• •On a non-production copy, run a controlled two-session reproduction: session A holds a row lock, session B attempts to update the same row, then confirm B is blocked.
• •Ask the holder to COMMIT or ROLLBACK explicitly; this is the safest recovery path and produces the cleanest verification of waiter advancement.
• •If the holder is an abandoned idle-in-transaction session, terminate it with pg_terminate_backend(<pid>) only after confirming the pid and that the session is not the waiter.
• •Set a bounded lock_timeout on the waiter session before re-running the verification, so a future failure mode is predictable rather than silent.
• •For autovacuum blockers, wait for the worker to finish its current relation or vacuum, and verify the waiter resumes; do not disable autovacuum as a recovery step.
• •Document the pid, the lock relation, and the time of release so the verification can be replayed and reviewed by another engineer.

Prove the fix

1. 01After the holder releases, the waiter's wait_event in pg_stat_activity transitions from Lock or LockTransaction to another value (typically none, ClientRead, or CPU/IO) within a bounded window.
2. 02The originally blocked statement completes and returns its result, or, if lock_timeout was set, fails with a 55P03 "lock_not_available" error rather than hanging.
3. 03pg_locks no longer shows a granted=false row for the waiter's transactionid or relation that matches the originally observed lock tuple.
4. 04A second observation of the same workflow with the same inputs produces the same waiter-then-resume sequence, so the recovery is reproducible, not a one-off.
5. 05Application-level metrics for the affected query or endpoint return to their pre-incident latency and error rate within the agreed SLO window.

Prevention and next steps

• •Set lock_timeout and idle_in_transaction_session_timeout at the role or database level so abandoned sessions fail or close rather than block indefinitely.
• •Keep transactions short and perform row updates in a deterministic order across services to reduce the chance of cross-application lock waits.
• •Monitor pg_stat_activity for sessions in idle-in-transaction state longer than a configured threshold and alert on them.
• •Document the expected recovery behavior for each common lock-wait scenario so on-call engineers can verify, not guess, when a waiter has recovered.

Safe commands and checks

SELECT pid, state, wait_event_type, wait_event, xact_start, query FROM pg_stat_activity WHERE datname = current_database() AND wait_event_type = 'Lock' ORDER BY xact_start;
SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query, blocking.pid AS blocking_pid, blocking.query AS blocking_query, blocked.wait_event FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocking.pid <> blocked.pid WHERE EXISTS (SELECT 1 FROM pg_locks l WHERE l.pid = blocked.pid AND NOT l.granted);
SELECT locktype, relation::regclass, page, tuple, transactionid, mode, granted, pid FROM pg_locks WHERE pid IN (<blocker_pid>, <waiter_pid>) ORDER BY granted DESC, pid;
SHOW lock_timeout; SHOW idle_in_transaction_session_timeout;
SELECT pid, application_name, state, xact_start, query FROM pg_stat_activity WHERE pid = <waiter_pid> AND wait_event_type = 'Lock';
SELECT pg_terminate_backend(<holder_pid>); -- only after confirming the pid is the holder, not the waiter, and only on a non-production or approved environment.
SELECT relname, pid, mode, granted FROM pg_locks l JOIN pg_class c ON c.oid = l.relation WHERE locktype = 'relation' AND relation = <relation_oid> ORDER BY granted DESC;