MySQL 5.5版本开始,引入了MDL锁,用于解决或者保证DDL操作与DML操作之间的一致性,但是在部分场景下会出现阻塞,例如执行DML操作时执行ALTER操作、存在长时间查询时执行ALTER操作等。 1、登录RDS数据库。 2、在SQL窗口执行show full processlist命令,查看数据库所有线程状态。 3、查看State列是否存在大量Waiting for table metadata lock,出现Waiting for table metadata lock即表示出现阻塞。 4、查找造成阻塞的会话ID。 (1) 查看状态为Waiting for table metadata lock会话的Info列,找到该会话操作的表,例如sbtest2。 (2) 查看其他会话的Info列,找到正在对表sbtest2进行操作的会话,记录会话Id。 说明 这里需要找到的是一直在占用操作该表的会话,而不是正在等待MDL锁(状态为Waiting for table metadata lock)解除的会话,注意区分。可以根据State列的状态和Info列的命令内容来进行分析判断。 例如,下图中State为Waiting for table metadata lock的会话,从其Info列的命令判断,此会话需要对表sbtest2进行操作;其他需要操作表sbtest2的会话中,从Id为267会话的State列状态可以判断,此会话正在对表sbtest2进行操作,造成了阻塞。 说明 本文以状态Sending data为例,请根据实际的会话状态进行判断。 您也可以用如下命令查询长时间未完成的事务,如果导致阻塞的语句的用户与当前登录用户不同,请使用导致阻塞的语句的用户登录来终止会话。 5、在命令行输入kill 会话ID,例如 kill 267,即可中断会话,解除MDL锁。 https://help.aliyun.com/document_detail/94566.htm?spm=a2c4g.11186623.0.0.712b1a601x66k8#concept-csn-5tt-4fb背景信息
出现场景
原因
操作步骤
select concat('kill ',i.trx_mysql_thread_id,';') from information_schema.innodb_trx i,
(select
id, time
from
information_schema.processlist
where
time = (select
max(time)
from
information_schema.processlist
where
state = 'Waiting for table metadata lock'
and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat'))) p
where timestampdiff(second, i.trx_started, now()) > p.time
and i.trx_mysql_thread_id not in (connection_id(),p.id);
后续维护
on schedule every 60 minute
starts '2015-09-15 11:00:00'
on completion preserve enable do
begin
declare v_sql varchar(500);
declare no_more_long_running_trx integer default 0;
declare c_tid cursor for
select concat ('kill ',trx_mysql_thread_id,';')
from information_schema.innodb_trx
where timestampdiff(minute,trx_started,now()) >= 60;
declare continue handler for not found
set no_more_long_running_trx=1;
open c_tid;
repeat
fetch c_tid into v_sql;
set @v_sql=v_sql;
prepare stmt from @v_sql;
execute stmt;
deallocate prepare stmt;
until no_more_long_running_trx end repeat;
close c_tid;
end;附录