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

我想不断追踪邮差的共同锁。

我偶然看到了 锁具监察文章，并试图运行以下查询:

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;

然后返回等待获取锁的查询。但是我不能设法更改它，以便它可以同时返回阻塞和阻塞查询。

有什么想法吗？

223692 次浏览
小开

我发现这些工具中经常缺少的一个功能是查找行锁的能力。至少在我研究过的大型数据库中，pg _ lock 中没有显示行锁(如果有的话，pg _ lock 会大得多，而且没有真正的数据类型来正确地显示视图中锁定的行)。

我不知道是否有一个简单的解决方案，但是通常我所做的是查看锁在等待的表，并搜索 xmax 小于事务 id 的行。这通常给了我一个开始的地方，但它是一个有点实际操作和不友好的自动化。

注意，显示了对这些表上的行的未提交写操作。一旦提交，这些行在当前快照中不可见。但对于大桌子来说，这是一种痛苦。

小开

从这个 关于 Postgres 查询锁的优秀文章中，可以从以下查询获得阻塞查询和阻塞查询及其信息。

CREATE VIEW lock_monitor AS(
SELECT
COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
blockeda.query as blocked_query, blockedl.mode as blocked_mode,
blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
( (blockingl.transactionid=blockedl.transactionid) OR
(blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database()
);


SELECT * from lock_monitor;

由于这个查询很长但是很有用，文章作者为它创建了一个视图来简化它的用法。

小开

自从9.6以来，这就简单多了，因为它引入了函数 pg_blocking_pids()来查找阻塞另一个会话的会话。

所以你可以使用这样的东西:

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
activity.pid,
activity.usename,
activity.query,
blocking.pid AS blocking_id,
blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

这有助于您理解哪些操作正在相互干扰(即使阻塞来自前一个查询) ，帮助您理解终止一个会话的影响，并弄清楚如何防止将来出现阻塞。

小开

Postgres 有一个通过 SQL 表公开的非常丰富的系统目录。 PG 的统计数据收集器是一个子系统，支持收集和报告关于服务器活动的信息。

现在可以简单地查询 pg_stat_activity来计算阻塞 PID。

select pg_blocking_pids(pid) as blocked_by
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

若要获取与阻塞 PID 相对应的查询，可以在子查询中自联接或使用它作为 where 子句。

SELECT query
FROM pg_stat_activity
WHERE pid IN (select unnest(pg_blocking_pids(pid)) as blocked_by from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0);

注意: 由于 pg_blocking_pids(pid)返回一个 Integer [] ，因此在 WHERE pid IN子句中使用它之前，需要先将它设置为 unnest。

寻找慢速查询有时会很乏味，所以要有耐心。

小开

对于没有 pg_blocking_pids函数的 postgreql 9.6之前的 postgreql 版本，您可以使用以下查询来查找阻塞查询和阻塞查询。

SELECT w.query                          AS waiting_query,
w.pid                            AS waiting_pid,
w.usename                        AS waiting_user,
now() - w.query_start            AS waiting_duration,
l.query                          AS locking_query,
l.pid                            AS locking_pid,
l.usename                        AS locking_user,
t.schemaname || '.' || t.relname AS tablename,
now() - l.query_start            AS locking_duration
FROM pg_stat_activity w
JOIN pg_locks l1 ON w.pid = l1.pid AND NOT l1.granted
JOIN pg_locks l2 ON l1.relation = l2.relation AND l2.granted
JOIN pg_stat_activity l ON l2.pid = l.pid
JOIN pg_stat_user_tables t ON l1.relation = t.relid
WHERE w.waiting;
小开

如何显示所有阻塞的查询。

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

提交答案