Follow the Blocking Chain With pg_locks and the Blocking PIDs Function
When one session waits on a row or table lock, the session it waits for may itself be waiting, and the query users complain about is usually at the end of a chain rather than at its head. pg_blocking_pids on a waiting session gives you the process IDs it is stuck behind, and joining pg_stat_activity to pg_locks shows what each of them is doing and which object is contended. Fixing the query at the tail of the chain changes nothing; you have to find the head, which is often a long-running or idle-in-transaction session.
Questions this Concept answers
Why does fixing the query that users complain about often change nothing when lock waiting is involved?
J
jeremy
Video
Finding Blocking Transactions with pg_stat_activity in PostgreSQL
A blocking transaction in PostgreSQL occurs when one open transaction holds a row-level lock that a second transaction needs, forcing the second session to wait until the first commits or rolls back;…