PostgreSQL查找堵塞会话
背景
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
两者冲突。
参考转载:
《PostgreSQL 谁堵塞了谁（锁等待检测）- pg_blocking_pids, pg_safe_snapshot_blocking_pids》
