如何排查阻塞语句

文章目录文档用途详细信息相关文档文档用途查询阻塞当前sql的语句并结束阻塞语句。详细信息查询阻塞当前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|updatetable1setid2whereid1;12784|{}|Activity|WalSenderMain|START_REPLICATION1/4B000000 TIMELINE216725|{16717}|Lock|transactionid|updatetable1setid2whereid1;16730|{}|||selectpid,pg_blocking_pids(pid),wait_event_type,wait_event,queryfrompg_stat_activity;12776|{}|Activity|BgWriterHibernate|12779|{}|Activity|ArchiverMain|12775|{}|Activity|CheckpointerMain|12777|{}|Activity|WalWriterMain|(10rows)说明①通过查询可以发现当前update语句的pid为16725被pid为16717的update语句阻塞。②pg_blocking_pids函数 返回阻止具有指定进程 ID 的服务器进程获取锁的会话的进程 ID 数组如果没有此类服务器进程或未被阻止则返回空数组。频繁调用此函数可能会对数据库性能产生一些影响因为它需要在短时间内对锁管理器的共享状态进行独占访问。2、通过系统表pg_class和pg_locks查找表对应锁highgo# select oid,relname from pg_class where relnametable1;oid|relname----------------16447|table1(1row)highgo# select locktype,database,pid,relation ,mode from pg_locks where relation16447;locktype|database|pid|relation|mode-------------------------------------------------------relation|14743|16717|16447|RowExclusiveLock relation|14743|16725|16447|RowExclusiveLock tuple|14743|16725|16447|ExclusiveLock(3rows)3、使用pg_terminate_backend函数结束阻塞语句。highgo# select pg_terminate_backend(16717);pg_terminate_backend----------------------t(1row)注意事项①超级用户或已授予 pg_signal_backend 调用角色的角色的成员可以执行该函数。②该函数会终止进程一旦进程被终止未完成的事务可能会回滚并且可能会丢失数据。4、再次查看已无阻塞可以执行需要执行的sqlhighgo# select locktype,database,pid,relation ,mode from pg_locks where relation16447;locktype|database|pid|relation|mode-----------------------------------------(0rows)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_REPLICATION1/4B000000 TIMELINE216730|{}|||selectpid,pg_blocking_pids(pid),wait_event_type,wait_event,queryfrompg_stat_activity;12776|{}|Activity|BgWriterHibernate|12779|{}|Activity|ArchiverMain|12775|{}|Activity|CheckpointerMain|12777|{}|Activity|WalWriterMain|(9rows)