PostgreSQL 谁堵塞了谁(锁等待检测)- pg_blocking_pids, pg_safe_snapshot_blocking_pids-阿里云开发者社区

开发者社区> 德哥> 正文

PostgreSQL 谁堵塞了谁(锁等待检测)- pg_blocking_pids, pg_safe_snapshot_blocking_pids

简介: PostgreSQL 谁堵塞了谁(锁等待检测)- pg_blocking_pids, pg_safe_snapshot_blocking_pids
+关注继续查看

背景

当一个进程处于等待(被堵塞)状态时,是谁干的?可以使用如下函数,快速得到捣蛋(堵塞别人)的PID。

1、请求锁时被堵,是哪些PID堵的?

pg_blocking_pids(int)    int[]    Process ID(s) that are blocking specified server process ID from acquiring a lock  

2、请求safe快照时被堵(SSI隔离级别,请求安全快照冲突),是哪些PID堵的?

pg_safe_snapshot_blocking_pids(int)    int[]    Process ID(s) that are blocking specified server process ID from acquiring a safe snapshot  

例子

1、会话1

postgres=# begin;  
BEGIN  
postgres=# select * from tbl limit 1;  
   id   | c1 | c2   
--------+----+----  
 918943 |  1 |  0  
(1 row)  
  
postgres=# select pg_backend_pid();  
 pg_backend_pid   
----------------  
          30862  
(1 row)  

2、会话2

postgres=# begin;  
BEGIN  
postgres=# select pg_backend_pid();  
 pg_backend_pid   
----------------  
          30928  
(1 row)  
  
postgres=# truncate tbl;  
  
等待中  

3、会话3

postgres=# begin;  
BEGIN  
postgres=# select pg_backend_pid();  
 pg_backend_pid   
----------------  
          30936  
(1 row)  
  
postgres=# select * from tbl limit 1;  
  
等待中  

4、会话4

postgres=# select pg_backend_pid();  
 pg_backend_pid   
----------------  
          30999  
(1 row)  
  
postgres=# select * from tbl limit 1;  
  
等待中  

5、查看捣蛋PID

postgres=# select pid,pg_blocking_pids(pid),wait_event_type,wait_event,query from pg_stat_activity ;  
  pid  | pg_blocking_pids | wait_event_type |     wait_event      |                                           query                                             
-------+------------------+-----------------+---------------------+-------------------------------------------------------------------------------------------  
 30862 | {}               | Client          | ClientRead          | select pg_backend_pid();  
 30928 | {30862}          | Lock            | relation            | truncate tbl;  
 30936 | {30928}          | Lock            | relation            | select * from tbl limit 1;  
 30999 | {30928}          | Lock            | relation            | select * from tbl limit 1;  

代码

src/backend/utils/adt/lockfuncs.c

/*  
 * pg_blocking_pids - produce an array of the PIDs blocking given PID  
 *  
 * The reported PIDs are those that hold a lock conflicting with blocked_pid's  
 * current request (hard block), or are requesting such a lock and are ahead  
 * of blocked_pid in the lock's wait queue (soft block).  
 *  
 * In parallel-query cases, we report all PIDs blocking any member of the  
 * given PID's lock group, and the reported PIDs are those of the blocking  
 * PIDs' lock group leaders.  This allows callers to compare the result to  
 * lists of clients' pg_backend_pid() results even during a parallel query.  
 *  
 * Parallel query makes it possible for there to be duplicate PIDs in the  
 * result (either because multiple waiters are blocked by same PID, or  
 * because multiple blockers have same group leader PID).  We do not bother  
 * to eliminate such duplicates from the result.  
 *  
 * We need not consider predicate locks here, since those don't block anything.  
 */  
Datum  
pg_blocking_pids(PG_FUNCTION_ARGS)  
{  
  
  
...............  
  
/*  
 * pg_safe_snapshot_blocking_pids - produce an array of the PIDs blocking  
 * given PID from getting a safe snapshot  
 *  
 * XXX this does not consider parallel-query cases; not clear how big a  
 * problem that is in practice  
 */  
Datum  
pg_safe_snapshot_blocking_pids(PG_FUNCTION_ARGS)  
{  
...........  

src/backend/storage/lmgr/predicate.c

/*  
 * GetSafeSnapshotBlockingPids  
 *              If the specified process is currently blocked in GetSafeSnapshot,  
 *              write the process IDs of all processes that it is blocked by  
 *              into the caller-supplied buffer output[].  The list is truncated at  
 *              output_size, and the number of PIDs written into the buffer is  
 *              returned.  Returns zero if the given PID is not currently blocked  
 *              in GetSafeSnapshot.  
 */  
int  
GetSafeSnapshotBlockingPids(int blocked_pid, int *output, int output_size)  
{  
        int                     num_written = 0;  
        SERIALIZABLEXACT *sxact;  
  
        LWLockAcquire(SerializableXactHashLock, LW_SHARED);  
  
        /* Find blocked_pid's SERIALIZABLEXACT by linear search. */  
        for (sxact = FirstPredXact(); sxact != NULL; sxact = NextPredXact(sxact))  
        {  
                if (sxact->pid == blocked_pid)  
                        break;  
        }  
  
        /* Did we find it, and is it currently waiting in GetSafeSnapshot? */  
        if (sxact != NULL && SxactIsDeferrableWaiting(sxact))  
        {  
                RWConflict      possibleUnsafeConflict;  
  
                /* Traverse the list of possible unsafe conflicts collecting PIDs. */  
                possibleUnsafeConflict = (RWConflict)  
                        SHMQueueNext(&sxact->possibleUnsafeConflicts,  
                                                 &sxact->possibleUnsafeConflicts,  
                                                 offsetof(RWConflictData, inLink));  
  
                while (possibleUnsafeConflict != NULL && num_written < output_size)  
                {  
                        output[num_written++] = possibleUnsafeConflict->sxactOut->pid;  
                        possibleUnsafeConflict = (RWConflict)  
                                SHMQueueNext(&sxact->possibleUnsafeConflicts,  
                                                         &possibleUnsafeConflict->inLink,  
                                                         offsetof(RWConflictData, inLink));  
                }  
        }  
  
        LWLockRelease(SerializableXactHashLock);  
  
        return num_written;  
}  

参考

https://www.postgresql.org/docs/11/functions-info.html

《PostgreSQL 锁等待排查实践 - 珍藏级 - process xxx1 acquired RowExclusiveLock on relation xxx2 of database xxx3 after xxx4 ms at xxx》

《PostgreSQL 锁等待监控 珍藏级SQL - 谁堵塞了谁》

《PostgreSQL 锁等待跟踪》

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议,奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等,奖品丰富,快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
一个跨平台的 C++ 内存泄漏检测器
基本使用 对于下面这样的一个简单程序test.cpp: int main() { int* p1 = new int; char* p2 = new char[10]; return 0; } 我们的基本需求当然是对于该程序报告存在两处内存泄漏。
591 0
PostgreSQL 谁堵塞了谁(锁等待检测)- pg_blocking_pids, pg_safe_snapshot_blocking_pids
PostgreSQL 谁堵塞了谁(锁等待检测)- pg_blocking_pids, pg_safe_snapshot_blocking_pids
1061 0
redis分布式锁,无须设置有效期,自动检测hold锁的节点是否存活
基于redis实现分布式锁,无需设置有效期,自动检测hold锁的节点是否存活。
1938 0
使用OpenApi弹性释放和设置云服务器ECS释放
云服务器ECS的一个重要特性就是按需创建资源。您可以在业务高峰期按需弹性的自定义规则进行资源创建,在完成业务计算的时候释放资源。本篇将提供几个Tips帮助您更加容易和自动化的完成云服务器的释放和弹性设置。
11972 0
CV:基于keras利用cv2自带两步检测法对《跑男第六季第五期》之如花片段(或调用摄像头)进行实时脸部表情检测
CV:基于keras利用cv2自带两步检测法对《跑男第六季第五期》之如花片段(或调用摄像头)进行实时脸部表情检测
37 0
sql server 性能调优 资源等待之网络I/O
原文:sql server 性能调优 资源等待之网络I/O 一.概述    与网络I/O相关的等待的主要是ASYNC_NETWORK_IO,是指当sql server返回数据结果集给客户端的时候,会先将结果集填充到输出缓存里(ouput cache),同时网络层会开始将输出缓存里的数据打包,由客户端接收。
973 0
【翻译】split lock检测与处理
> 本文在解读 https://lwn.net/Articles/790464/ 的基础上,加入自己理解,与原文存在差异。 ### 从地址不对齐访问到split lock Intel CPU微架构允许不对齐的内存访问,但ARM、RISC-V等架构却不允许。在众多的不对齐中,一个特殊的场景是:原子操作的操作数(由于地址不对齐)跨越两个cache lines,Intel将之叫做spl
1635 0
+关注
德哥
公益是一辈子的事, I&#39;m digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载