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

Roman
2014-10-21  
199339  
    
7  

我想不断跟踪 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;

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

有任何想法吗？

sql ,  postgresql ,  locking
stack overflow How to detect query which holds the lock in Postgres?
原文答案

答案:

作者头像
a_horse_with_no_name
2017-04-12    221      原文

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;
作者头像
Devi
2017-04-12    66      原文

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

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.

作者头像
jpmc26
2019-02-05    32      原文

a_horse_with_no_name's answer 的这种修改将为您提供阻塞会话的最后一个（或当前，如果它仍在运行中）查询，除了阻塞会话：

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

这可以帮助您了解哪些操作相互干扰（即使阻塞来自先前的查询），让您了解终止一个会话的影响，并弄清楚如何防止将来阻塞。

作者头像
Lokesh Devnani
2019-12-17    10      原文

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.

Now to figure out the blocking PIDs you can simply query 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.

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 unnest it before you use it in a WHERE pid IN clause.

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

作者头像
52Hertz
2021-04-07    4      原文

for postgresql versions earlier than postgresql 9.6 which does not have pg_blocking_pids function，you can use following query to find blocked query and blocking query.

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;
作者头像
Chris Travers
2017-03-21    2      原文

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

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.

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.

作者头像
M. Hamza Rajput
2022-05-25    0      原文

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

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

相关问题

标签