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

I want to track mutual locks in postgres constantly.

我想在postgres中持续跟踪相互锁。

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?

什么好主意吗?

3 个解决方案

#1

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.

因为9.6，这比引入函数pg_blocking_pids()来查找阻塞另一个会话的会话要容易得多。

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;

#2

From this excellent article on query locks in Postgres, one can get blocked query and blocker query and their information from the following query.

从这篇关于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;

As the query is long but useful, the article author has created a view for it to simplify it's usage.

由于查询很长但很有用，本文作者为它创建了一个视图，以简化它的使用。

#3

One thing I find that is often missing from these is an ability to look up row locks. At least on the larger databases I have worked on, row locks are not shown in pg_locks (if they were, pg_locks would be much, much larger and there isn't a real data type to show the locked row in that view properly).

我发现经常缺少的一件事是查找行锁的能力。至少在我处理过的较大的数据库上，行锁不会显示在pg_locks中(如果显示的话，pg_locks会大得多，而且没有真正的数据类型来正确地显示该视图中的锁行)。

I don't know that there is a simple solution to this but usually what I do is look at the table where the lock is waiting and search for rows where the xmax is less than the transaction id present there. That usually gives me a place to start, but it is a bit hands-on and not automation friendly.

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

Note that shows you uncommitted writes on rows on those tables. Once committed, the rows are not visible in the current snapshot. But for large tables, that is a pain.

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

#1