PostgreSQL 谁堵塞了谁（锁等待检测）- pg_blocking_pids

2022-01-01 19:09:14

背景

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)

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)

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

两者冲突。