TDSQL 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);