Postgresql 锁等待检测及处理

背景

对于多数数据库，dba技能之一就是查找锁。锁的存在有效合理的在多并发场景下保证业务有序进行。下面我们看一下Postgresql中查找阻塞的方法。

1、找到"被阻塞者"，获取被堵塞的PID

select distinct pid from pg_locks where not granted;

2、找到"阻塞者"，通过被阻塞者pid找到阻塞者

## test=# select * from pg_blocking_pids(53920);

pg_blocking_pids

{53868}

(1 row)

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

test=# select * from pg_stat_activity where pid=53920;

-[ RECORD 1 ]----+------------------------------

datid | 13285

datname | test

pid | 53920

usesysid | 10

usename | system

application_name | ksql

client_addr |

client_hostname |

client_port | -1

backend_start | 2022-04-22 10:20:29.124634+08

xact_start | 2022-04-22 10:20:30.962902+08

query_start | 2022-04-22 10:20:30.962902+08

state_change | 2022-04-22 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，当前的锁等待内容

test=# 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)

"阻塞者"

1、找到"阻塞者"当前的状态，（注意，有可能当前会话内容看不出阻塞动作）

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

test=# select * from pg_stat_activity where pid= any (pg_blocking_pids(53920));

-[ RECORD 1 ]----+------------------------------

datid | 13285

datname | test

pid | 53868

usesysid | 10

usename | system

application_name | psql

client_addr |

client_hostname |

client_port | -1

backend_start | 2019-04-22 10:20:21.377909+08

xact_start | 2019-04-22 10:20:23.832489+08

query_start | 2019-04-22 10:20:25.529063+08

state_change | 2019-04-22 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导致的锁，则需要从审计日志中查找（开启log_statements='all'）。重点关注wait_event_type和state字段。这里说明该holder执行完事务后处于空闲状态，正等待客户端发送新请求，常见于业务框架代码忘记提交的场景或假死状态。

2、找到"阻塞者"的"犯罪"证据：

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

test=# 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、最后梳理一下
"被阻塞者" ：对13285.1907887对象需要如下锁

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

"阻塞者" ：对13285.1907887对象已持有如下锁

relation | 13285 | 1907887 | | | | | | | | 4/1372747 | 53868 | ShareLock | t | f
relation | 13285 | 1907887 | | | | | | | | 4/1372747 | 53868 | AccessExclusiveLock | t | f
两者冲突，因此发生锁等待。最后和应用确认持锁者是否是活动事务，可否正确结束事务。否则，通过

select pg_terminate_backend(53868);终止此session。

巴特西

Postgresql 锁等待检测及处理

背景

"阻塞者"

最新文章

热门文章