PostgreSQL is waiting for a lock. How do you find the blocking transaction?

First confirm that the wait is for a lock

For an initial overview, run the read-only query: SELECT pid, pg_blocking_pids(pid) AS blockers FROM pg_stat_activity WHERE wait_event_type = 'Lock'; It lists the relevant waiting PIDs and their blocking predecessors. If the result is empty, do not force a lock-based explanation onto the problem. Record the observation time and compare it with a specific failing request.

Also record the application name and affected database. When sharing diagnostics, restrict the output to the details needed; query text can contain sensitive values. Establish the distinction between observing and intervening before taking action. Obtaining an overview alone is no reason to cancel queries or disconnect clients in bulk.

Follow the chain to the original blockage

pg_blocking_pids() can return a conflicting lock holder or a waiting session ahead of you in the queue. For each PID you find, therefore check whether it is also blocked. The first predecessor may not be where the problem begins. Do not choose an intervention target solely by the longest-waiting user request; it may be another victim in the chain.

The idle in transaction state means an open transaction with no query currently running. Compare xact_start, application_name and the session owner. Viewing other sessions' details may require pg_read_all_stats. Establish whether the work is a planned batch, a deployment or a forgotten operation. Judge transaction age by its purpose and impact rather than a universal threshold.

Choose the intervention according to session state

pg_cancel_backend(pid) cancels the current query; pg_terminate_backend(pid) ends the entire session. An idle open transaction has no running query to cancel. Agree with its owner how to end the transaction; ROLLBACK on its original connection discards uncommitted changes. Do not issue COMMIT merely to get other requests moving if you do not know what you would be committing.

Before considering session termination, check its identity again and assess the impact on the operation in progress. These functions require appropriate privileges. A true result from pg_terminate_backend(pid) without a timeout confirms that the signal was sent, not that termination has finished. Afterwards, check the session, blocking chain and application outcome. One successful administrative command does not close the incident.

Bound the wait and verify the user outcome

lock_timeout limits each lock acquisition attempt; statement_timeout limits statement duration. When using both, a shorter lock_timeout lets you detect lock waiting separately. SET LOCAL lasts only for the current transaction. Choose values to suit the operation and test them together with the application's error handling. A timeout bounds the wait; it does not repair the blocking work.

Row and table locks are normally held until the transaction ends. Shorten the part of the operation that needs them, and access multiple objects in a consistent order. Test the change with concurrent operations in a separate environment. In production, then compare waiting and error rates and repeat the order save that originally failed. Successfully loading the home page does not test that write.

• Record waiting PIDs, the time and the affected operation.
• Follow the predecessor chain and identify the blocking work's owner.
• Distinguish query cancellation, rollback and session termination.
• Verify that waiting clears and the application write succeeds.