MySQL Waiting for table metadata lock故障分析

《MySQL Waiting for table metadata lock故障分析》要点：
本文介绍了MySQL Waiting for table metadata lock故障分析，希望对您有用。如果有疑问，可以联系我们。

源起

线上执行ALTER时,通过show processlist查看到出现Waiting for table metadata lock?,导致后面的查询都无法执行.

5217122 | create_table_04 | 172.100.207.148:31291 | finance | Query   | 1829 | Waiting for table metadata lock  | ALTER TABLE `Pay` MODIFY COLUMN `pay`  smallint(6) NULL DEFAULT 0 COMMENT '付??'
5217155 | bx_live_dml     | 172.100.210.4:34730   | finance | Prepare | 1714 | Waiting for table metadata lock  | select * from Pay where (pay =4 or pay =8) and projectId ='CSZY0'

由于当时直接把ALTER给kill了,所以基本没有现场,所以下面模拟什么情况MySQL产生这种事故.

MySQL元数据锁

MySQL DBA对于Waiting for table metadata lock肯定不会陌生,一般都是进行alter操作时被堵住了,导致了我们在show processlist 时,看到线程的状态是在等metadata lock.

为了在并发环境下维护表元数据的数据一致性,在表上有活动事务(显式或隐式)的时候,不可以对元数据进行写入操作.因此从MySQL5.5版本开始引入了MDL锁(metadata lock),来保护表的元数据信息,用于解决或者保证DDL操作与DML操作之间的一致性.对于引入MDL,其主要解决了2个问题,一个是事务隔离问题,比如在可重复隔离级别下,会话A在2次查询期间,会话B对表结构做了修改,两次查询结果就会不一致,无法满足可重复读的要求；另外一个是数据复制的问题,比如会话A执行了多条更新语句期间,另外一个会话B做了表结构变更并且先提交,就会导致slave在重做时,先重做alter,再重做update时就会出现复制错误的现象.

所以在对表进行上述操作时,如果表上有活动事务(未提交或回滚),请求写入的会话会等待在Metadata lock wait .例如下面的这种情形：

若没有MDL锁的保护,则事务2可以直接执行DDL操作,并且导致事务1出错,5.1版本即是如此.5.5版本加入MDL锁就在于保护这种情况的发生,由于事务1开启了查询,那么获得了MDL锁,锁的模式为SHARED_READ,事务2要执行DDL,则需获得EXCLUSIVE锁,两者互斥,所以事务2需要等待.

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

引起Metadata lock wait出现的场景：Waiting for table metadata lock

场景一：当前有执行DML操作时执行ALTRE操作.

# SESSION A
mysql> insert into sbtest2 select * from sbtest1;

# SESSION B
mysql> alter table sbtest2 add test1 int;   //等待SESSION A执行完;

# SESSION C
mysql> show processlist;
+-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+
| Id  | User | Host      | db     | Command | Time | State                           | Info                                      |
+-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+
| 267 | root | localhost | sbtest | Query   |    7 | Sending data                    | insert into sbtest2 select * from sbtest1 |
| 271 | root | localhost | sbtest | Query   |    3 | Waiting for table metadata lock | alter table sbtest2 add test1 int         |
| 272 | root | localhost | NULL   | Query   |    0 | starting                        | show processlist                          |
+-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+
3 rows in set (0.00 sec)

# SESSION D
mysql> select * from sbtest2 limit 10;    //等待元数据锁;

# SESSION E
mysql> show processlist;
+-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+
| Id  | User | Host      | db     | Command | Time | State                           | Info                                      |
+-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+
| 267 | root | localhost | sbtest | Query   |   20 | Sending data                    | insert into sbtest2 select * from sbtest1 |
| 271 | root | localhost | sbtest | Query   |   13 | Waiting for table metadata lock | alter table sbtest2 add test1 int         |
| 272 | root | localhost | NULL   | Query   |    0 | starting                        | show processlist                          |
| 308 | root | localhost | sbtest | Query   |    3 | Waiting for table metadata lock | select * from sbtest2 limit 10            |
+-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+
4 rows in set (0.00 sec)

从上述例子可以看出,我们在执行DDL语句的时候得事先看一下,进程中是否已经存在某些DML语句占用了表的元数据锁,这样会导致DDL语句处于锁等待状态.一旦出现Waiting for table metadata lock等待现象,包括读操作,业务受影响.

场景二：当前有对表的长时间查询或使用mysqldump/mysqlpump时,使用alter会被堵住.

# SESSION A
mysql> select *,sleep(10) from sbtest2;

# SESSION B
mysql> alter table sbtest2 add test2 int;   //等待SESSION A执行完;

# SESSION C
mysql> show processlist;
+-----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------+
| Id  | User | Host      | db     | Command | Time | State                           | Info                                  |
+-----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------+
| 267 | root | localhost | sbtest | Query   |   12 | User sleep                      | select *,sleep(10) from sbtest2       |
| 271 | root | localhost | sbtest | Query   |    8 | Waiting for table metadata lock | alter table sbtest2 add test3 int     |
| 272 | root | localhost | NULL   | Query   |    0 | starting                        | show processlist                      |
| 311 | root | localhost | NULL   | Query   |    3 | Waiting for table metadata lock | select * from sbtest.sbtest2 limit 10 |
+-----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------+
4 rows in set (0.00 sec)

（编辑：ASP站长网）