| 导语 数据库在执行过程中经常会遇到有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