RDS for MySQL 表上 Metadata Lock 的产生和处理

注：

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

 

2. Metadata lock wait 的含义

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

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

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

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

-- 请根据具体的情景修改查询语句

-- 如果导致阻塞的语句的用户与当前用户不同，请使用导致阻塞的语句的用户登录来终止会话

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 秒；避免长时间等待元数据锁影响表上其他业务查询。

《云数据库RDS MySQL从入门到高阶》电子版地址 MySQL 是当今最主流的开源数据库，它以稳定性、安全性、灵活性、低成本等优势获得了大量用户的认可，在国内也有广泛受众群体。本手册为您重点介绍My SQL数据库基础的架构、SQL性能调优等内容，以及高阶的My SQL MGR等特性，让您对My SQL数据库在短时间内有初步了解。
PolarDB MySQL 5.7/RDS 5.7升级到PolarDB MySQL 8.0最佳实践 升级概述PolarDB MySQL 5.7/RDS 5.7 向 8.0 升级过程中，经常遇到的问题主要是性能问题、语法兼容性问题，以及周边组件是否的支持，查询的性能问题一般是由于优化器升级导致执 行计划有变，此类问题需要对性能低下的语句进行针对性的性能优化，但性能问题基本不会引发业务报错以及代码的改写问题，此类问题不在本文讨论范围之内。本文主要讨论真实的兼容性问题，此类问题需要在数据库升级过程中，
《云数据库RDS MySQL从入门到高阶》电子版下载地址 MySQL 是当今最主流的开源数据库，它以稳定性、安全性、灵活性、低成本等优势获得了大量用户的认可，在国内也有广泛受众群体。本手册为您重点介绍My SQL数据库基础的架构、SQL性能调优等内容，以及高阶的My SQL MGR等特性，让您对My SQL数据库在短时间内有初步了解。
【走进RDS】之MySQL内存分配与管理（上篇） MySQL的内存分配、使用、管理的模块较多，总体上分为上中下三篇介绍：上篇文章主要介绍InnoDB层和SQL层内存分配管理器；中篇介绍InnoDB的内存结构和使用特点；下篇介绍内存使用限制。本篇为上篇，代码版本主要基于8.0.25。