如何检测在 Postgres 中持有锁的查询？

Question

我想不断跟踪 postgres 中的互锁。

我遇到了 Locks Monitoring 文章并尝试运行以下查询：

 SELECT bl.pid     AS blocked_pid,
     a.usename  AS blocked_user,
     kl.pid     AS blocking_pid,
     ka.usename AS blocking_user,
     a.query    AS blocked_statement
FROM  pg_catalog.pg_locks         bl
 JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
 JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
 JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;

不幸的是，它从不返回非空结果集。如果我将给定的查询简化为以下形式：

 SELECT bl.pid     AS blocked_pid,
     a.usename  AS blocked_user,
     a.query    AS blocked_statement
FROM  pg_catalog.pg_locks         bl
 JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
WHERE NOT bl.granted;

然后它返回正在等待获取锁的查询。但我无法设法更改它，以便它可以返回阻塞查询和阻塞查询。

有任何想法吗？

原文由 Roman 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

如何显示所有被阻止的查询。

 select pid,
       usename,
       pg_blocking_pids(pid) as blocked_by,
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

您可以使用以下命令终止被阻止的查询。

 SELECT pg_cancel_backend(a.pid), pg_terminate_backend(a.pid);

您可以使用它终止所有被阻止的查询。

 SELECT pg_cancel_backend(a.pid), pg_terminate_backend(a.pid)
FROM( select pid,
       usename,
       pg_blocking_pids(pid) as blocked_by,
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0) a

原文由 M. Hamza Rajput 发布，翻译遵循 CC BY-SA 4.0 许可协议