如何检测在Postgres中拥有锁的查询? [英] How to detect query which holds the lock in Postgres?

问题描述

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

我遇到了锁定监控文章，并尝试运行以下查询:

  SELECT bl.pid AS被阻止_pid，a.用户名AS被阻止的用户，kl.pid AS Blocking_pid，ka.usename AS阻塞用户，a.查询AS Blocked_statement从pg_catalog.pg_locks bl加入pg_catalog.pg_stat_activity a ON a.pid = bl.pidJOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid！= bl.pid加入pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid不授权的地方； 

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

  SELECT bl.pid AS被阻止_pid，a.usename AS blocks_user,a.查询AS Blocked_statement从pg_catalog.pg_locks bl加入pg_catalog.pg_stat_activity a ON a.pid = bl.pid不授权的地方； 

然后返回等待获取锁的查询.但是我无法对其进行更改，以便它可以返回阻止查询和阻止查询.

有什么想法吗?

解决方案

从9.6开始，这变得容易得多，因为它引入了功能 pg_blocking_pids()来查找正在阻止另一个会话的会话./p>

因此您可以使用类似这样的内容:

 选择pid，使用名称，pg_blocking_pids(pid)作为blocked_by，查询为blocked_query来自pg_stat_activity其中基数(pg_blocking_pids(pid))>0; 

I want to track mutual locks in postgres constantly.

I came across Locks Monitoring article and tried to run the following query:

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;

Unfortunately, it never returns non-empty result set. If I simplify given query to the following form:

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;

then it returns queries which are waiting to acquire a lock. But I cannot manage to change it so that it can return both blocked and blocker queries.

Any ideas?

解决方案

Since 9.6 this is a lot easier as it introduced the function pg_blocking_pids() to find the sessions that are blocking another session.

So you can use something like this:

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;

这篇关于如何检测在Postgres中拥有锁的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！