MySQL生产堵塞严重，定位堵塞源头

1、来自于生产事故的压力

在一次做生产环境DDL的操作中，误判了产生的影响，造成了大量的堵塞（查询了需要DDL的表，发现表行数很小，行数在万行以下，所以认为DDL会很快完成，但没有判断出这个表的并发度，这个表在业务中属于热点表），生产环境因此大量的堵塞，造成了严重问题，当不断有客户反馈软件不能正常使用。然后我们查询到大量堵塞。研发，测试，客服，包括领导都全站在我身后，里里外外围了几层，可想其中的压力。 由于对系统表不是太熟，还需要去官网做一定的查询，拖延了解决问题的时间，解决后为了以后能快速解决此类问题，所以在测试环境进行了复盘，并写成了语句模板，方便遇到问题快速解决。

2、问题复现

create table locktest1
( id int,
  num int,
  primary key(id)
);
create table locktest2
( id int,
  num int,
  primary key(id)
);
create table locktest3
( id int,
  num int,
  primary key(id)
);

insert into locktest1
values(1,10),(2,20),(3,30);

insert into locktest2
select * from locktest1;
insert into locktest3
select * from locktest1
复制

在session1中执行以下语句

begin;
update locktest1 set num = num + 1 where id = 1;
update locktest1 set num = num + 1 where id = 2;
复制

在session2中执行以下语句

begin;
update locktest2 set num = num + 1 where id = 1;
update locktest1 set num = num + 1 where id = 2;  -- 此语句会因为session1的语句2未提交被堵塞
复制

在session3中执行以下语句

begin;
update locktest3 set num = num + 1 where id = 1;
update locktest2 set num = num + 1 where id = 1; -- 此语句会因为session2的语句2未提交被堵塞
复制

上述三个session造成了连环堵塞。

set @ord := 1;
with recursive cte as
(
  select a.requesting_thread_id,a.blocking_thread_id, @ord as killorder from performance_schema.data_lock_waits a
  where not exists(select 1 from performance_schema.data_lock_waits b where a.blocking_thread_id = b.requesting_thread_id)
  union all
  select a.requesting_thread_id,a.blocking_thread_id, @ord:= @ord + 1  as killorder  from  performance_schema.data_lock_waits  a
  inner join cte b on a.blocking_thread_id = b.requesting_thread_id
)
select a.*,d.processlist_id as killid from cte a
INNER JOIN performance_schema.threads d  on a.blocking_thread_id = d.thread_id
order by killorder;
复制

执行后在session4去执行上述语句。 会直接列出堵塞顺序，并需要kill的ID

上面可以看到线程 104 被114堵塞 ,而 114又被115堵塞

所以kill顺序该是先kill 60 然后再执行 Kill 59

如果要看上述被kill语句的整个事务语句情况，可使用以下模板

set @killid = 60;
SELECT DATE_SUB(now(), INTERVAL (
         SELECT variable_value 
           FROM performance_schema.global_status 
           WHERE variable_name='UPTIME')-TIMER_START*10e-13 second) `start_time`,
       SQL_TEXT 
  FROM performance_schema.events_statements_history  
 WHERE nesting_event_id=(
               SELECT distinct EVENT_ID 
                 FROM performance_schema.events_transactions_current t   
                 LEFT JOIN sys.processlist p ON p.thd_id=t.thread_id  
                 WHERE conn_id= @killid) 
 ORDER BY event_id;
复制

3、给一个总结

熟练使用系统表也是DBA必备技能之一。
平时积累一些SQL模板，等到线上环境出现类似问题时，可以快速使用，可大大减少解决问题的时间，快速恢复生产正常使用。