作者
digoal
日期
2019-03-04
标签
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》
《PostgreSQL 商用版本EPAS(阿里云ppas(Oracle 兼容版)) NUMA 架构spin锁等待优化》
《PostgreSQL 锁等待监控 珍藏级SQL - 谁堵塞了谁》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
