PostgreSQL如何快速定位阻塞SQL

| 导语 数据库在执行过程中经常会遇到有SQL执行时间超长，互相阻塞的问题。如何快速找出罪魁祸首，并且干掉此类语句让流程继续，本文将简单为大家讲明。

当我们遇到语句简单但是执行时间超长的SQL语句时，不一定是因为SQL写得不好，很大可能是因为遇到了数据库的等待事件了，如何判断语句是因为什么原因而阻塞的呢？

我们使用一个测试场景进行模拟演习一次，首先创建一个表，然后插入部分数据，再显示的创建事务，构造一个锁等待的场景。

create table t1(id int primary key); insert into t1 select generate_series(1,10000);

begin;delete from t1;

# 再另开一个session 执行同样的语句： begin;delete from t1;

此时就可以发现在执行第二个事务的时候，SQL明显无法执行下去，因为第一个事务未提交。

当然我们可以通过一些现成的语句来直接查看锁信息，如：

SELECT blocking_activity.datname as "数据库", blocking_activity.application_name as "持锁会话程序名", blocking_activity.client_addr as "持锁会话地址", now()-blocking_activity.query_start as "阻塞时长(s)", blocked_locks.pid AS "阻塞会话ID", blocked_activity.usename AS "被阻塞用户", blocking_locks.pid AS "持锁会话ID", blocking_activity.usename AS "持锁用户", blocked_activity. QUERY AS "被锁SQL", blocking_activity. QUERY AS "持锁SQL" FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks. DATABASE IS NOT DISTINCT FROM blocked_locks. DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.GRANTED;

但是我们也需要知道其查看原理。当发现有业务卡住无法顺利进行时候，我们第一时间进入数据库中，执行语句查看当前有哪些SQL语句正在执行：

select * from pg_stat_activity;

select pid,now()-query_start as "执行时间",wait_event_type,wait_event,query from pg_stat_activity; 上面两个SQL执行的效果是一致的，直接查看整个视图信息会较全，但是比较多，可使用第二个视图，其中，wait_event wait_event_type 字段代表等待事件。不同的等待事件代表不同的含义。

可以看到我们执行时间列，发现了有部分session的执行时间已经有6分钟了。并且session中有一个等待事件的类型是 lock，说明了当前session正在执行的语句因为锁的原因导致了语句执行时间很长，那到底是什么锁，又是什么操作阻塞了这一条语句的执行呢？

此时我们就可以通过 pg_locks 这个视图来找出元凶。首先，刚刚我们通过这个视图已经知道 被阻塞的的这个session的的pid 是多少了。于是我们在pg_locks中找到对应的pid，如上图中的 31365。

执行语句：

select * from locks;

可以从上图结果中首先需要找到pid为31365 的条目，然后查看 granted字段，如果此字段 值为true，则代表当前锁条目是会阻塞别的sql运行，如果granted 是false则代表，当前锁是被阻塞的。 以此也可以应证上图中 31365 进程是被阻塞的会话。那么现在就找出pid 为31365 被锁住的操作对象是什么，可以看database和relation字段，可以发现，被锁住的是 databse:19498 和 relation:19499和 19502。

于是，我们找到其他granted 字段为true的，并且对应锁对象为databse:19498 和 relation:19499和 19502 的 进程pid 是多少，可以很清楚的查看到 pid 为30539 的持有了当前这两个对象的 RowExclusiveLock锁导致了PID为31365 session的语句正常执行。

此时根据业务的具体情况就可以判定改如何做，一般为了紧急处理问题，我们需要将阻塞的会话干掉。为此PostgreSQL提供了两个语句来kill会话或者sql。

分别是 pg_cancel_backend() 和pg_terminate_backend() 两个函数，函数入参为pid。

pg_cancel_backend() 的作用是关闭session正在执行的语句，回滚所有未提交的操作；但是不关闭整个session。pg_terminate_backend()的作用是直接关闭整个会话，回滚所有未提交的操作。

如下所示：

select pg_cacanl_backend(31365);

select pg_terminate_backend(31365);

点击链接跳转视频地址：https://cloud.tencent.com/developer/video/29376