关于sql: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);