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 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模板，等到线上环境出现类似问题时，可以快速使用，可大大减少解决问题的时间，快速恢复生产正常使用。