合 PG中查询阻塞会话的函数pg_blocking_pids
Tags： PGPostgreSQL阻塞会话查询
👉 本文共约592个字，系统预计阅读时间或需3分钟。
SQL语句
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | -- 慢查询会话和阻塞会话，从pg13开始有leader_pid列 SELECT pgsa.pid, pg_blocking_pids(pid) AS blocking_pid, TO_CHAR(INTERVAL '1 second' * trunc(EXTRACT(epoch FROM (NOW() - pgsa.query_start))), 'HH24:MI:SS') AS query_runtime, TO_CHAR(INTERVAL '1 second' * trunc(EXTRACT(epoch FROM (NOW() - pgsa.xact_start))), 'HH24:MI:SS') AS xact_runtime, (SELECT count(*) from pg_stat_activity a where a.leader_pid=pgsa.pid ) parallel_worker_cnt, pgsa.client_port, pgsa.datname AS datname, pgsa.usename AS usename, pgsa.client_addr client_addr, pgsa.application_name AS application_name, pgsa.state AS state, pgsa.wait_event, pgsa.wait_event_type, pgsa.backend_xid, pgsa.backend_xmin, pgsa.backend_start AS backend_start, pgsa.xact_start AS xact_start, state_change, pgsa.query_start AS query_start, TRUNC(EXTRACT( epoch FROM (NOW() - pgsa.xact_start) )) AS xact_stay, trunc(EXTRACT( epoch FROM (NOW() - pgsa.query_start) )) AS query_stay, -- REPLACE(pgsa.QUERY, chr(10), ' ') AS QUERY, pgsa.QUERY, pgsa.backend_type, 'select pg_terminate_backend('||pgsa.pid||');' kill1, 'select pg_cancel_backend('||pgsa.pid||');' kill2 FROM pg_stat_activity AS pgsa WHERE pgsa.state not in ( 'idle' ,'idle in transaction (aborted)' ) and pgsa.pid not in (pg_backend_pid()) and pgsa.leader_pid is null ORDER BY query_stay DESC,xact_stay DESC,pid LIMIT 1000; -- 查询阻塞的进程id SELECT pid, usename, pg_blocking_pids(pid) AS blocking_pid, query AS blocked_query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0; |
pg_blocking_pids函数
在PG 9.6中就已经提供了该函数了。
pg_blocking_pids返回阻止服务器进程的会话的进程ID数组，该进程ID与指定的进程ID一起获取锁定，如果没有这样的服务器进程或者没有被阻塞，则返回一个空数组。
如果一个服务器进程持有一个与被阻塞进程的锁请求冲突的锁(硬阻塞)，或者正在等待一个与被阻塞进程的锁请求冲突并且在等待队列中位于其前面的锁(软阻塞)，那么这个服务器进程就会阻塞另一个服务器进程。 当使用并行查询时结果总是列出客户端可见的进程ID(即pg_backend_pid的结果)，即使实际的锁是由子工作进程持有或等待的。 因此，结果中可能存在重复的pid。还要注意当准备好的事务持有冲突锁时，它将用零进程ID表示。
频繁调用这个函数可能会对数据库性能产生一些影响，因为它需要在短时间内独占访问锁管理器的共享状态。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
