如何排查阻塞语句

瀚高数据库
目录
文档用途
详细信息

文档用途
查询阻塞当前sql的语句，并结束阻塞语句。

详细信息
1、通过pg_stat_activity视图和pg_blocking_pids函数查找阻塞sql。

highgo=# select pid,pg_blocking_pids(pid),wait_event_type,wait_event,query from pg_stat_activity ;
  pid  | pg_blocking_pids | wait_event_type |     wait_event      |                                           query

-------+------------------+-----------------+---------------------+-------------------------------------------------------------------
------------------------
 12781 | {}               | Activity        | LogicalLauncherMain |
 12778 | {}               | Activity        | AutoVacuumMain      |
 16717 | {}               | Client          | ClientRead          | update table1 set id=2 where id=1;
 12784 | {}               | Activity        | WalSenderMain       | START_REPLICATION 1/4B000000 TIMELINE 2
 16725 | {16717}          | Lock            | transactionid       | update table1 set id=2 where id=1;
 16730 | {}               |                 |                     | select pid,pg_blocking_pids(pid),wait_event_type,wait_event,query
from pg_stat_activity ;
 12776 | {}               | Activity        | BgWriterHibernate   |
 12779 | {}               | Activity        | ArchiverMain        |
 12775 | {}               | Activity        | CheckpointerMain    |
 12777 | {}               | Activity        | WalWriterMain       |
(10 rows)

说明：

①通过查询可以发现，当前update语句的pid为16725，被pid为16717的update语句阻塞。

②pg_blocking_pids函数： 返回阻止具有指定进程 ID 的服务器进程获取锁的会话的进程 ID 数组，如果没有此类服务器进程或未被阻止，则返回空数组。

频繁调用此函数可能会对数据库性能产生一些影响，因为它需要在短时间内对锁管理器的共享状态进行独占访问。

2、通过系统表pg_class和pg_locks查找表对应锁

highgo=# select oid,relname from pg_class where relname='table1';
  oid  | relname
-------+---------
 16447 | table1
(1 row)

highgo=# select locktype,database,pid,relation ,mode from pg_locks where relation='16447';
 locktype | database |  pid  | relation |       mode
----------+----------+-------+----------+------------------
 relation |    14743 | 16717 |    16447 | RowExclusiveLock
 relation |    14743 | 16725 |    16447 | RowExclusiveLock
 tuple    |    14743 | 16725 |    16447 | ExclusiveLock
(3 rows)

3、使用pg_terminate_backend函数结束阻塞语句。

highgo=# select pg_terminate_backend('16717');
 pg_terminate_backend
----------------------
 t
(1 row)

注意事项：

①超级用户或已授予 pg_signal_backend 调用角色的角色的成员可以执行该函数。

②该函数会终止进程，一旦进程被终止，未完成的事务可能会回滚，并且可能会丢失数据。

4、再次查看已无阻塞，可以执行需要执行的sql

highgo=# select locktype,database,pid,relation ,mode from pg_locks where relation='16447';
 locktype | database | pid | relation | mode
----------+----------+-----+----------+------
(0 rows)
highgo=# select pid,pg_blocking_pids(pid),wait_event_type,wait_event,query from pg_stat_activity ;
  pid  | pg_blocking_pids | wait_event_type |     wait_event      |                                           query

-------+------------------+-----------------+---------------------+-------------------------------------------------------------------
------------------------
 12781 | {}               | Activity        | LogicalLauncherMain |
 12778 | {}               | Activity        | AutoVacuumMain      |
 12784 | {}               | Activity        | WalSenderMain       | START_REPLICATION 1/4B000000 TIMELINE 2
 16730 | {}               |                 |                     | select pid,pg_blocking_pids(pid),wait_event_type,wait_event,query
from pg_stat_activity ;
 12776 | {}               | Activity        | BgWriterHibernate   |
 12779 | {}               | Activity        | ArchiverMain        |
 12775 | {}               | Activity        | CheckpointerMain    |
 12777 | {}               | Activity        | WalWriterMain       |
(9 rows)