PostgreSQL 谁堵塞了谁（锁等待检测）- pg_blocking_pids1 "被害人"2 "嫌疑人"3 当场对峙

标签

PostgreSQL , 锁等待 , 队列

https://github.com/digoal/blog/blob/master/201903/20190304_01.md#%E8%83%8C%E6%99%AF 背景

https://github.com/digoal/blog/blob/master/201903/20190304_01.md#1-%E8%A2%AB%E5%AE%B3%E4%BA%BA 1 "被害人"

1、找到"被害人"，获取被锁堵塞的PID

select distinct pid from pg_locks where not granted;  
           

2、找到"嫌疑人"，获取被锁堵塞的PID是被哪些PID堵塞的

postgres=# select * from pg_blocking_pids(53920);  
 pg_blocking_pids   
------------------  
 {53868}  
(1 row)  
           

3、找到"被害人" 受侵害的证据

被锁堵塞的PID，当前的会话内容

postgres=# select * from pg_stat_activity where pid=53920;  
-[ RECORD 1 ]----+------------------------------  
datid            | 13285  
datname          | postgres  
pid              | 53920  
usesysid         | 10  
usename          | postgres  
application_name | psql  
client_addr      |   
client_hostname  |   
client_port      | -1  
backend_start    | 2019-03-04 10:20:29.124634+08  
xact_start       | 2019-03-04 10:20:30.962902+08  
query_start      | 2019-03-04 10:20:30.962902+08  
state_change     | 2019-03-04 10:20:30.962905+08  
wait_event_type  | Lock  
wait_event       | relation  
state            | active  
backend_xid      | 1286297005  
backend_xmin     | 1286297004  
query            | drop table a;  
backend_type     | client backend  
           

被锁堵塞的PID，当前的锁等待内容

postgres=# select * from pg_locks where pid=53920 and not granted;  
 locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |        mode         | granted | fastpath   
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------  
 relation |    13285 |  1907887 |      |       |            |               |         |       |          | 5/1358301          | 53920 | AccessExclusiveLock | f       | f  
(1 row)  
           

https://github.com/digoal/blog/blob/master/201903/20190304_01.md#2-%E5%AB%8C%E7%96%91%E4%BA%BA 2 "嫌疑人"

1、找到"嫌疑人"当前的状态，（注意，有可能当前会话内容看不出侵害动作）

堵塞这个PID的PIDs，当前的会话内容

postgres=# select * from pg_stat_activity where pid= any (pg_blocking_pids(53920));  
-[ RECORD 1 ]----+------------------------------  
datid            | 13285  
datname          | postgres  
pid              | 53868  
usesysid         | 10  
usename          | postgres  
application_name | psql  
client_addr      |   
client_hostname  |   
client_port      | -1  
backend_start    | 2019-03-04 10:20:21.377909+08  
xact_start       | 2019-03-04 10:20:23.832489+08  
query_start      | 2019-03-04 10:20:25.529063+08  
state_change     | 2019-03-04 10:20:25.53116+08  
wait_event_type  | Client  
wait_event       | ClientRead  
state            | idle in transaction  
backend_xid      | 1286297004  
backend_xmin     |   
query            | truncate a;  
backend_type     | client backend  
           

如果当前状态没有找到具体是哪条SQL干的坏事，则需要从审计日志中查找。

2、找到"嫌疑人"的"犯罪"证据：

堵塞这个PID的PIDs，当前的锁内容

postgres=# select * from pg_locks where pid=any (pg_blocking_pids(53920)) order by pid;  
   locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |        mode         | granted | fastpath   
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------  
 virtualxid    |          |          |      |       | 4/1372747  |               |         |       |          | 4/1372747          | 53868 | ExclusiveLock       | t       | t  
 relation      |    13285 |  1907887 |      |       |            |               |         |       |          | 4/1372747          | 53868 | ShareLock           | t       | f  
 relation      |    13285 |  1907887 |      |       |            |               |         |       |          | 4/1372747          | 53868 | AccessExclusiveLock | t       | f  
 transactionid |          |          |      |       |            |    1286297004 |         |       |          | 4/1372747          | 53868 | ExclusiveLock       | t       | f  
(4 rows)  
           

https://github.com/digoal/blog/blob/master/201903/20190304_01.md#3-%E5%BD%93%E5%9C%BA%E5%AF%B9%E5%B3%99 3 当场对峙

1、"被害人" 对13285.1907887对象需要如下锁

relation |    13285 |  1907887 |      |       |            |               |         |       |          | 5/1358301          | 53920 | AccessExclusiveLock | f       | f  
           

2、"嫌疑人" 对13285.1907887对象已持有如下锁

relation      |    13285 |  1907887 |      |       |            |               |         |       |          | 4/1372747          | 53868 | ShareLock           | t       | f  
 relation      |    13285 |  1907887 |      |       |            |               |         |       |          | 4/1372747          | 53868 | AccessExclusiveLock | t       | f  
           

两者冲突。

https://github.com/digoal/blog/blob/master/201903/20190304_01.md#%E5%8F%82%E8%80%83 参考

《PostgreSQL 谁堵塞了谁（锁等待检测）- pg_blocking_pids, pg_safe_snapshot_blocking_pids》 《PostgreSQL 锁等待排查实践 - 珍藏级 - process xxx1 acquired RowExclusiveLock on relation xxx2 of database xxx3 after xxx4 ms at xxx》 《PostgreSQL 商用版本EPAS(阿里云ppas(Oracle 兼容版)) NUMA 架构spin锁等待优化》 《PostgreSQL 锁等待监控 珍藏级SQL - 谁堵塞了谁》 《PostgreSQL 锁等待跟踪》

https://github.com/digoal/blog/blob/master/201903/20190304_01.md#%E5%85%8D%E8%B4%B9%E9%A2%86%E5%8F%96%E9%98%BF%E9%87%8C%E4%BA%91rds-postgresql%E5%AE%9E%E4%BE%8Becs%E8%99%9A%E6%8B%9F%E6%9C%BA 免费领取阿里云RDS PostgreSQL实例、ECS虚拟机