PostgreSQL9.6：新增pg_blocking_pids函数准确定位 Blocking SQL

PosttgreSQL 的SQL被锁情况在数据库维护过程中非常常见，之前博客 PostgreSQL 锁分析 演示了 PostgreSQL 锁的一些场景，在开始本文的介绍之前特做以下说明，假如会话A堵住会话B，我们称会话B为 blocked 会话，会话A为 blokcing 会话，后续介绍时都用这两个词；当数据库出现锁时，如果对应用有影响，DBA应该在最短的时间内找到 blocking 会话并快速处理，在 9.6 版本前查找 blocking SQL 通常需要查询 pg_stat_activity、 pg_locks 等一系列视图，增加了故障分析的时间，9.6 版本新增 pg_blocking_pids() 函数，能够快速找到 blocking SQL，下面模拟一个简单的场景介绍这个函数的使用。

--创建测试表

francs=> create table test_lock(id int4,name text);
CREATE TABLE

francs=> insert into test_lock values(1,'a'),(2,'b'),(3,'c');
INSERT 0 3

--会话一

francs=> select pg_backend_pid();
 pg_backend_pid 
----------------
          22814
francs=> begin;
BEGIN
          
francs=> update test_lock set name='cc' where id=3;
UPDATE 1

备注：会话一在事务里更新 ID=3 的记录，并不提交。

--会话二

francs=> select pg_backend_pid();
 pg_backend_pid 
----------------
          22845
(1 row)

francs=> delete from test_lock where id=3;

备注：会话二删除ID=3的记录，此时由于这条记录之前被UPDATE并没有提交，这句DELETE仍然处于等待状态。

--监控

备注：从图中看到之前操作的两条 SQL，为什么 22845 会话处于等待状态呢，运行 pg_blocking_pids 函数可以找到 blocking 会话，如下：

--查找 blocking SQL

postgres=# select pg_blocking_pids(22845);
 pg_blocking_pids 
------------------
 {22814}
(1 row)

备注：22814 正是 blocking SQL， 22845 为 blocked SQL。

--总结

这篇博客仅模拟了一个简单场景，并通过 pg_blocking_pids 函数查找 blocking
SQL，真实生产环境锁的案例远比这复杂，具体情况具体分析。

--参考
PostgreSQL 锁分析