关于RDS MySQL 表上 Metadata lock 的产生和处理方法

　　1. Metadata lock wait 出现的场景

　　创建、删除索引

　　修改表结构

　　表维护操作(repair table 、optimize table等)

　　删除表

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

　　注：在不支持事务的 MyISAM 引擎表和支持事务的 Innodb 引擎表上，都会出现 Metadata Lock Wait 等待现象。

　　2. Metadata lock wait 的含义

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

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

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

　　表上有失败的查询事务

        当前有对表的长时间查询

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

　　4. 解决方案：

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

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

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

　　select concat('kill ',id,';') from information_schema.processlist where id <> connection_id() and command='sleep' and db = 'TAB_DB_NAME';

　　注：请将 TAB_DB_NAME 替换为您的实际database 名称。

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

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