说明：本站的技术类文章，均为内部学习交流使用，并不能代表产品厂家，或者是第三方的观点，非专业技术类人员，请勿对服务器设备进行操作，以免造成设备不可使用或数据丢失。同时凯铧互联小编建议用户定期对云服务器数据进行备份保存！

---

北京凯铧互联科技有限公司（简称凯铧互联）由多名前阿里云资深技术专家创立,核心员工来自阿里云、腾讯云等,作为阿里云重要的合作伙伴,专注于为企业用户提供云计算及云计算的解决方案。阿里云优惠购买专线：158-0160-3153（微信同步）

发布：凯铧互联

问题场景：
1. Metadata lock wait 出现的场景

2. Metadata lock wait 的含义

3. 导致 Metadata lock wait 等待的活动事务

4. 解决方案

5. 如何避免出现长时间 Metadata lock wait 导致表上相关查询阻塞，影响业务

下面凯铧互联小编详细进行说明

1. Metadata lock wait 出现的场景

创建、删除索引

修改表结构

表维护操作（optimize table、repair table 等）

删除表

获取表上表级写锁 (lock table tab_name write)

注：

支持事务的 InnoDB 引擎表和 不支持事务的 MyISAM 引擎表，都会出现 Metadata Lock Wait 等待现象。
一旦出现 Metadata Lock Wait 等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。

2. Metadata lock wait 的含义

为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此 MySQL 引入了 metadata lock ，来保护表的元数据信息。

因此在对表进行上述操作时，如果表上有活动事务（未提交或回滚），请求写入的会话会等待在 Metadata lock wait 。

3. 导致 Metadata lock wait 等待的活动事务

当前有对表的长时间查询

显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚。

表上有失败的查询事务

4. 解决方案

show processlist 查看会话有长时间未完成的查询，使用kill 命令终止该查询。

查询 information_schema.innodb_trx 看到有长时间未完成的事务， 使用 kill 命令终止该查询。

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);

-- 请根据具体的情景修改查询语句
-- 如果导致阻塞的语句的用户与当前用户不同，请使用导致阻塞的语句的用户登录来终止会话

注：关于清理会话，请参考：RDS MySQL 如何终止会话

如果上面两个检查没有发现，或者事务过多，建议使用下面的查询将相关库上的会话终止

-- RDS for MySQL 5.6

select
concat('kill ', a.owner_thread_id, ';')
from
information_schema.metadata_locks a
left join
(select
b.owner_thread_id
from
information_schema.metadata_locks b, information_schema.metadata_locks c
where
b.owner_thread_id = c.owner_thread_id
and b.lock_status = 'granted'
and c.lock_status = 'pending') d ON a.owner_thread_id = d.owner_thread_id
where
a.lock_status = 'granted'
and d.owner_thread_id is null;

-- RDS for MySQL 5.5

select
concat('kill ', p1.id, ';')
from
information_schema.processlist p1,
(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', 'trunc'))) p2
where
p1.time >= p2.time
and p1.command in ('Sleep' , 'Query')
and p1.id not in (connection_id() , p2.id);

-- RDS for MySQL 5.5 语句请根据具体的 DDL 语句情况修改查询的条件；
-- 如果导致阻塞的语句的用户与当前用户不同，请使用导致阻塞的语句的用户登录来终止会话

5. 如何避免出现长时间 metadata lock wait 导致表上相关查询阻塞，影响业务

在业务低峰期执行上述操作，比如创建删除索引。

在到RDS的数据库连接建立后，设置会话变量 autocommit 为 1 或者 on，比如 set autocommit=1; 或 set autocommit=on; 。

考虑使用事件来终止长时间运行的事务，比如下面的例子中会终止执行时间超过60分钟的事务。

create event my_long_running_trx_monitor
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;

注：请根据您自身情况，自行修改运行间隔和事务执行时长。

执行上述1中操作前，设置会话变量 lock_wait_timeout 为较小值，比如 set lock_wait_timeout=30; 命令可以设置 metadata lock wait 的最长时间为 30 秒；避免长时间等待元数据锁影响表上其他业务查询。

如问题还未解决,请联系售后技术支持。

如果您不是专业人员，又对服务器的安全非常担忧，可以购买阿里云管家服务或相关防护软件，通过凯铧互联购买可以获得折上折优惠！如您的问题还未解决,请联系我方售后技术支持。若您需要帮助可以直接联系我方客服，阿里云代理商凯铧互联专业技术团队为您提供全面便捷专业的7x24技术服务。

为什么选择我们：北京凯铧互联科技有限公司（简称凯铧互联）由多名前阿里云资深技术专家创立,核心员工来自阿里云、腾讯云等,作为阿里云,腾讯云,百度云,金山云重要的合作伙伴,专注于为企业用户提供云计算及云计算的解决方案。总部设在北京,并在内蒙设有办事处。做为一家综合性方案商,凯铧互联向各行业用户提供基于云计算的各种解决方案。为用户获得优质服务的同时,秉承"专业规划、周到服务"的服务理念,根据用户的实际情况,充分考虑各种网络资源的特点及功效,为用户量身定做一套适合于其实际应用需求的网络应用方案。帮助用户利用互联网的力量展开新的营销方式,并大大缩短了项目实施周期,获得用户的一致好评。

凯铧互联专属服务：阿里云代理商凯铧互联拥有专业的网络架构团队、云服务解决团队、阿里云产品服务团队能够帮助用户提供快捷、全面、高效的云上解决方案 。凯铧互联为每一个用户提供专属网络架构服务，提供7x24一对一技术服务，远程协助等。同时还能提供阿里云服务器ECS、阿里云CDN等产品等的专属折扣优惠购买，让用户能够便捷、更省的上云。如果您需要详细的为您的企业选择最适合自己的服务器配置类型，请您联系客服，专业人员为您提供服务，同时还能获得更多的优惠折扣，电话专线：136-5130-9831，QQ:3398234753。