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虛拟機