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,下面模拟一个简单的场景介绍这个函数的使用。

模拟测试

创建测试表

1
2
3
4
5
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

会话一

1
2
3
4
5
6
7
8
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 的记录,并不提交。

会话二

1
2
3
4
5
6
7
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

1
2
3
4
5
postgres=# select pg_blocking_pids(22845);
pg_blocking_pids
------------------
{22814}
(1 row)

备注:22814 正是 blocking SQL, 22845 为 blocked SQL。

总结

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

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%