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

How to detect query which holds the lock in Postgres?

提问by Roman

I want to track mutual locks in postgres constantly.

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

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

我遇到了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;

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?

有任何想法吗？

回答by a_horse_with_no_name

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;

回答by Devi

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.

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

回答by jpmc26

This modification of a_horse_with_no_name's answerwill give you the blocking queries in addition to just the blocked sessions:

除了被阻止的会话之外，对a_horse_with_no_name 答案的修改还将为您提供阻止查询：

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));

回答by Lokesh Devnani

Postgres has a very rich system catalog exposed via SQL tables. PG's statistics collector is a subsystem that supports collection and reporting of information about server activity.

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

Now to figure out the blocking PIDs you can simply query pg_stat_activity.

现在要找出阻塞 PID，您可以简单地查询pg_stat_activity.

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

To, get the query corresponding to the blocking PID, you can self-join or use it as a where clause in a subquery.

要获取阻塞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);

Note: Since pg_blocking_pids(pid)returns an Integer[], so you need to unnestit before you use it in a WHERE pid INclause.

注意：由于pg_blocking_pids(pid)返回一个 Integer[]，所以unnest在WHERE pid IN子句中使用它之前需要它。

Hunting for slow queries can be tedious sometimes, so have patience. Happy hunting.

寻找缓慢的查询有时会很乏味，所以要有耐心。狩猎快乐。

回答by Chris Travers

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.

请注意，这会显示您对这些表上的行的未提交写入。提交后，行在当前快照中不可见。但是对于大表来说，这很痛苦。