mysql锁——元数据锁和innodb锁

一、MDL锁的作用？

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等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。

二、MDL锁类型说明

InnoDB层已经有了IS、IX这样的意向锁，有同学觉得可以用来实现上述例子的并发控制。但由于MySQL是Server-Engine架构，所以MDL锁是在Server中实现。另外，MDL锁还能实现其他粒度级别的锁，比如全局锁、库级别的锁、表空间级别的锁，这是InnoDB存储引擎层不能直接实现的锁。

但与InnoDB锁的实现一样，MDL锁也是类似对一颗树的各个对象从上至下进行加锁（对树进行加锁具体见：《MySQL技术内幕：InnoDB存储引擎》）。但是MDL锁对象的层次更多，简单来看有如下的层次：

上图中显示了最常见的4种MDL锁的对象，并且注明了常见的SQL语句会触发的锁。与InnoDB层类似的是，某些类型的MDL锁会从上往下一层层进行加锁。比如LOCK TABLE … WRITE这样的SQL语句，其首先会对GLOBAL级别加INTENTION_EXCLUSIVE锁，再对SCHEMA级别加INTENTION_EXCLUSIVE锁，最后对TABLE级别加SHARED_NO_READ_WRITE锁。

Metadata Lock既然也是一种锁，所以每个MDL都会定义锁住的对象，锁的持有时间和锁的类型。

2.1 按照对象/范围划分

属性	含义	范围/对象
GLOBAL	全局锁	范围
COMMIT	提交保护锁	范围
SCHEMA	库锁	对象
TABLE	表锁	对象
FUNCTION	函数锁	对象
PROCEDURE	存储过程锁	对象
TRIGGER	触发器锁	对象
EVENT	事件锁	对象

MDL按锁住的对象来分类，可以分为global，commit，schema，table，function，procedure，trigger，event，这些对象发生锁等待时，我们在show processlist可以分别看到如下等待信息。

1 2 3 4 5 6 7 8

Waiting for global read lock Waiting for commit lock Waiting for schema metadata lock Waiting for table metadata lock Waiting for stored function metadata lock Waiting for stored procedure metadata lock Waiting for trigger metadata lock Waiting for event metadata lock

2.2 按照锁的持有时间

属性	含义
MDL_STATEMENT	从语句开始执行时获取，到语句执行结束时释放。
MDL_TRANSACTION	在一个事务中涉及所有表获取MDL，一直到事务commit或者rollback(线程中终清理)才释放。
MDL_EXPLICIT	需要MDL_context::release_lock()显式释放。语句或者事务结束,也仍然持有，如Lock table, flush .. with lock语句等。

2.3 按照操作的对象

属性	含义	事例
MDL_INTENTION_EXCLUSIVE(IX)	意向排他锁用于global和commit的加锁。	truncate table t1; insert into t1 values(3,’abcde’);会加如下锁(GLOBAL,MDL_STATEMENT,MDL_INTENTION_EXCLUSIVE）                            (SCHEMA,MDL_TRANSACTION,MDL_INTENTION_EXCLUSIVE)
MDL_SHARED(S)	只访问元数据 比如表结构，不访问数据。	set golbal_read_only =on 加锁 (GLOBAL，MDL_EXPLICIT，MDL_SHARED）
MDL_SHARED_HIGH_PRIO(SH)	用于访问information_scheam表，不涉及数据。	select * from information_schema.tables; show create table xx; desc xxx;会加如下锁： (TABLE,MDL_TRANSACTION,MDL_SHARED_HIGH_PRIO)
MDL_SHARED_READ(SR)	访问表结构并且读表数据	select * from t1; lock table t1 read; 会加如下锁: (TABLE，MDL_TRANSACTION，MDL_SHARE_READ）
MDL_SHARED_WRITE(SW)	访问表结构并且写表数据	insert/update/delete/select .. for update 会加如下锁： (TABLE，MDL_TRANSACTION，MDL_SHARE_WRITE)
MDL_SHARED_UPGRADABLE(SU)	是mysql5.6引入的新的metadata lock, 在alter table/create index/drop index会加该锁;可以说是为了online ddl才引入的。特点是允许DML，防止DDL；	（TABLE，MDL_TRANSACTION，MDL_SHARED_UPGRADABLE）
MDL_SHARED_NO_WRITE(SNW)	可升级锁，访问表结构并且读写表数据，并且禁止其它事务写。	alter table t1 modify c bigint;(非onlineddl) (TABLE，MDL_TRANSACTION，MDL_SHARED_NO_WRITE）
MDL_SHARED_NO_READ_WRITE(SNRW)	可升级锁，访问表结构并且读写表数据，并且禁止其它事务读写。	lock table t1 write;加锁 (TABLE，MDL_TRANSACTION,MDL_SHARED_NO_READ_WRITE
MDL_EXCLUSIVE(X)	防止其他线程读写元数据	CREATE/DROP/RENAME TABLE，其他online DDL在rename阶段也持有X锁 (TABLE，MDL_TRANSACTION，MDL_EXCLUSIVE）

关于global对象，主要作用是防止DDL和写操作的过程中，执行set golbal_read_only = on或flush tables with read lock。

关于commit对象锁，主要作用是执行flush tables with read lock后，防止已经开始在执行的写事务提交。insert/update/delete在提交时都会上（COMMIT，MDL_EXPLICIT，MDL_INTENTION_EXCLUSIVE）锁。

2.4 几种典型语句的加(释放)锁流程

1. select语句操作MDL锁流程

1) Opening tables阶段，加共享锁

a) 加MDL_INTENTION_EXCLUSIVE锁

b) 加MDL_SHARED_READ锁

2) 事务提交阶段，释放MDL锁

a) 释放MDL_INTENTION_EXCLUSIVE锁

b) 释放MDL_SHARED_READ锁

2. DML语句操作MDL锁流程

1) Opening tables阶段，加共享锁

a) 加MDL_INTENTION_EXCLUSIVE锁

b) 加MDL_SHARED_WRITE锁

2) 事务提交阶段，释放MDL锁

a) 释放MDL_INTENTION_EXCLUSIVE锁

b) 释放MDL_SHARED_WRITE锁

3. alter操作MDL锁流程

1) Opening tables阶段，加共享锁

a) 加MDL_INTENTION_EXCLUSIVE锁

b) 加MDL_SHARED_UPGRADABLE锁，升级到MDL_SHARED_NO_WRITE锁

2) 操作数据，copy data，流程如下：

a) 创建临时表tmp，重定义tmp为修改后的表结构

b) 从原表读取数据插入到tmp表

3) 将MDL_SHARED_NO_WRITE读锁升级到MDL_EXCLUSIVE锁

a) 删除原表，将tmp重命名为原表名

4) 事务提交阶段，释放MDL锁

a) 释放MDL_INTENTION_EXCLUSIVE锁

b) 释放MDL_EXCLUSIVE锁

三、常见MDL锁场景

3.1 Waiting for table metadata lock

a）当前有执行DML操作时执行ALTRE操作。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

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

若没有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等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。

b）当前有对表的长时间查询或使用mysqldump/mysqlpump时，使用alter会被堵住。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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

c）显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，使用alter会被堵住。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

# SESSION A mysql > begin ; mysql > select * 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 | Sleep    |    36 |                                  | NULL                                | | 271 | root | localhost | sbtest | Query    |    30 | Waiting for table metadata lock | alter table sbtest2 add test2 int | | 272 | root | localhost | NULL    | Query    |      0 | starting                          | show processlist                    | + -- -- - + -- -- -- + -- -- -- -- -- - + -- -- -- -- + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 3 rows in set ( 0.00 sec )

d）表上有失败的查询事务，比如查询不存在的列，语句失败返回，但是事务没有提交，此时alter仍然会被堵住。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

# SESSION A mysql > begin ; mysql > select error from sbtest2 ; ERROR 1054 ( 42S22 ) : Unknown column 'error' in 'field list'   # SESSION B mysql > alter table sbtest2 add test3 int ;      / /等待 SESSION A提交或回滚 ;   # SESSION C mysql > show processlist ; + -- -- - + -- -- -- + -- -- -- -- -- - + -- -- -- -- + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + | Id    | User | Host        | db      | Command | Time | State                            | Info                                | + -- -- - + -- -- -- + -- -- -- -- -- - + -- -- -- -- + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + | 267 | root | localhost | sbtest | Sleep    |      7 |                                  | NULL                                | | 271 | root | localhost | sbtest | Query    |      3 | Waiting for table metadata lock | alter table sbtest2 add test3 int | | 272 | root | localhost | NULL    | Query    |      0 | starting                          | show processlist                    | | 311 | root | localhost | NULL    | Sleep    |    413 |                                  | NULL                                | + -- -- - + -- -- -- + -- -- -- -- -- - + -- -- -- -- + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 4 rows in set ( 0.00 sec )   # SESSION D mysql > select * from information_schema .innodb_trx ; Empty set ( 0.00 sec )

其实SESSION A中的事务并未开启，但是由于select获取表元数据的语句，语法上是有效的，虽然执行失败了，但是任然不会释放元数据锁，故而导致SESSION B的alter动作被阻塞。

通过SESSION D查看当前打开事务时，你会发现没有，从而找不到原因。所以当出现这种场景时，如何判断是哪个进程导致的呢，我们可以尝试查看表performance_schema. events_statements_current，分析进程状态来进行判断。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

mysql > select * from performance_schema . events_statements_current \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * *                THREAD_ID : 293                EVENT_ID : 32            END_EVENT_ID : 32              EVENT_NAME : statement / sql / select                  SOURCE : socket_connection .cc : 101              TIMER_START : 212721717099954000                TIMER_END : 212721717213807000              TIMER_WAIT : 113853000                LOCK_TIME : 0                SQL_TEXT : select error from sbtest2                  DIGEST : 0bbb2d5d1be45e77debea68111264885              DIGEST_TEXT : SELECT ERROR FROM ` sbtest2 `          CURRENT_SCHEMA : sbtest              OBJECT_TYPE : NULL            OBJECT_SCHEMA : NULL              OBJECT_NAME : NULL    OBJECT_INSTANCE_BEGIN : NULL              MYSQL_ERRNO : 1054        RETURNED_SQLSTATE : 42S22            MESSAGE_TEXT : Unknown column 'error' in 'field list'                  ERRORS : 1

然后找到其sid, kill掉该session，也可以kill掉DDL所在的session解决可以解决此问题。

另外，测试时SESSION A要显式开启一个事务，否则查询会隐式回滚结束，无法重现上面的场景。SESSION B执行alter后，没有立即阻塞住，而是立马开始copy to tmp table，这个过程结束后，才进行了MDL锁等待。这怎么解释呢，应该是执行alter操作主要分为创建临时新表->插入老表的数据->临时新表rename to老表三个步骤，在这种情况下，到最后一步才需要MDL锁，所以copy过程中不会阻塞。由于没有查询在进行，而且查询也没有进入innodb层 (失败返回)，所以show processlist和information_schema.innodb_trx没有可以参考的信息。

出现以上几种情况时，这个时候如果进行如下操作就会引起MDL：

• 创建、删除索引。
• 修改表结构。
• 表维护操作（optimize table、repair table等）。
• 删除表。
• 获取表上表级写锁 (lock table tab_name write)。

使用Profile分析场景三：显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，使用alter会被堵住

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

# SESSION A mysql > set profiling = on ; mysql > begin ; mysql > select * from sbtest .sbtest2 limit 1 ;   # SESSION B mysql > set profiling = on ; mysql > alter table sbtest .sbtest5 add test2 int ;    / /等待 SESSION A执行完 ;   # SESSION C mysql > set profiling = on ; mysql > select * from sbtest .sbtest2 limit 1 ;   # SESSION D mysql > set profiling = on ; mysql > show processlist ; + -- -- - + -- -- -- + -- -- -- -- -- - + -- -- -- + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Id    | User | Host        | db    | Command | Time | State                            | Info                                      | + -- -- - + -- -- -- + -- -- -- -- -- - + -- -- -- + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 325 | root | localhost | NULL | Query    |    25 | Waiting for table metadata lock | alter table sbtest .sbtest2 add test5 int | | 326 | root | localhost | NULL | Query    |      3 | Waiting for table metadata lock | select * from sbtest .sbtest2 limit 1      | | 327 | root | localhost | NULL | Query    |      0 | starting                          | show processlist                          | | 328 | root | localhost | NULL | Sleep    |    50 |                                  | NULL                                      | + -- -- - + -- -- -- + -- -- -- -- -- - + -- -- -- + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 4 rows in set ( 0.00 sec )

然后回滚SESSION A，等待SESSION B和SESSION C执行完，查看profile。

查看SESSION A

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

# SESSION A mysql > show profiles ; + -- -- -- -- -- + -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Query_ID | Duration      | Query                                      | + -- -- -- -- -- + -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + |          1 | 42.81646375 | alter table sbtest .sbtest2 add test5 int | + -- -- -- -- -- + -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in set , 1 warning ( 0.00 sec )   mysql > show profile for query 1 ; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- + | Status                          | Duration | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- + | starting                        | 0.000069 | | checking permissions            | 0.000004 | | checking permissions            | 0.000004 | | init                            | 0.000005 | | Opening tables                  | 0.000128 | | setup                            | 0.000032 | | creating table                  | 0.000654 | | After create                    | 0.000053 | | Waiting for table metadata loc | 1.000069 | . . . . . . . . . . . . . . . . . . . . . | After create                    | 0.000013 | | Waiting for table metadata loc | 0.871435 | | After create                    | 0.000042 | | System lock                      | 0.000013 | | preparing for alter table        | 0.002475 | | altering table                  | 9.752928 | | committing alter table to stor | 0.185624 | | end                              | 0.000021 | | query end                        | 0.000010 | | closing tables                  | 0.000007 | | freeing items                    | 0.000020 | | cleaning up                      | 0.000011 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- + 83 rows in set , 1 warning ( 0.00 sec )

查看SESSION C

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

mysql > show profiles ; + -- -- -- -- -- + -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Query_ID | Duration      | Query                                  | + -- -- -- -- -- + -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + |          1 | 10.75216050 | select * from sbtest .sbtest2 limit 1 | + -- -- -- -- -- + -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in set , 1 warning ( 0.00 sec )   mysql > show profile for query 1 ; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- - + | Status                          | Duration    | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- - + | starting                        |    0.000080 | | checking permissions            |    0.000007 | | Opening tables                  |    0.000012 | | Waiting for table metadata loc | 10.751829 | | Opening tables                  |    0.000094 | | init                            |    0.000019 | | System lock                      |    0.000010 | | optimizing                      |    0.000004 | | statistics                      |    0.000011 | | preparing                        |    0.000009 | | executing                        |    0.000003 | | Sending data                    |    0.000040 | | end                              |    0.000006 | | query end                        |    0.000008 | | closing tables                  |    0.000008 | | freeing items                    |    0.000014 | | cleaning up                      |    0.000009 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- - + 17 rows in set , 1 warning ( 0.00 sec )

从上述测试可以看出，SESSION C需要打开表时碰到了元数据锁。MySQL不论SESSION A执行的是select还是delete，此时alter table语句无法获取到metadata独占锁，会进行等待；所以会影响SESSION C的读取。

这是最基本的一种情形，这个和MySQL 5.6中的online ddl并不冲突。一般alter table的操作过程中，在after create步骤会获取metadata独占锁，当进行到altering table的过程时（通常是最花时间的步骤），对该表的读写都可以正常进行，这就是online ddl的表现，并不会像之前在整个alter table过程中阻塞写入，当然并不是所有ALTER语句都支持online ddl。

总之，alter table的语句是很危险的（其实他的危险其实是未提交事物或者长事务导致的），在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter table的维护任务，在无人监管的时候运行，最好通过lock_wait_timeout设置好超时时间，避免长时间的metedata锁等待。

3.2 Waiting for global read lock

构造一个Waiting for global read lock场景：

1 2	session1 : alter table t1 add c3 bigint ; / /大表执行需较长时间 session2 : set global read only = on ;        / /等待

查看进行信息：

1 2 3 4 5 6 7 8

mysql > show processlist ; + -- -- + -- -- -- + -- -- -- -- -- -- -- -- - + -- -- -- + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- + | Id | User | Host | db | Command | Time | State | Info | + -- -- + -- -- -- + -- -- -- -- -- -- -- -- - + -- -- -- + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- + | 1 | root | localhost : 5202    | test | Query | 12 | altering table | alter table t1 add c3 bigint | | 2 | root | localhost : 14699 | test | Query | 3 | Waiting for global read lock | set global read_only = on | | 3 | root | localhost : 17085 | NULL | Query | 0 | init | show processlist | + -- -- + -- -- -- + -- -- -- -- -- -- -- -- - + -- -- -- + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- +

分析：

alter table t1 add c3 bigint; 会加（GLOBAL,MDL_STATEMENT,MDL_INTENTION_EXCLUSIVE) 语句结束后才释放

set global read only=on; 会加（GLOBAL，MDL_EXPLICIT，MDL_SHARED）

由于session1执行时间比较长，一直持有MDL_INTENTION_EXCLUSIVE。从兼容性矩阵可以看出MDL_SHARED和MDL_INTENTION_EXCLUSIVE是不相容的，因此发生“Waiting for global read lock ”等待。直到session 1 alter操作完成释放MDL_INTENTION_EXCLUSIVE。set global read only=on;才可以继续执行。

3.3 Waiting for commit lock

构造一个Waiting for commit lock场景：

1 2 3

session1 : begin ; insert into t1 vlaues ( null , 'ab' ) ;          session2 : flush table with read lock ;    / /成功 session1 : commit                        / /发生等待

查看进行信息：

1 2 3 4 5 6 7 8

mysql > show processlist ; + -- -- + -- -- -- + -- -- -- -- -- -- -- -- - + -- -- -- + -- -- -- -- -- -- + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + | Id | User | Host | db | Command | Time | State | Info | + -- -- + -- -- -- + -- -- -- -- -- -- -- -- - + -- -- -- + -- -- -- -- -- -- + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + | 1 | root | 127.0.0.1 : 5202 | test | Query | 7 | Waiting for commit lock | commit | | 2 | root | 127.0.0.1 : 14699 | test | Sleep | 13 | | NULL | | 3 | root | 127.0.0.1 : 17085 | NULL | Query | 0 | init | show processlist | + -- -- + -- -- -- + -- -- -- -- -- -- -- -- - + -- -- -- + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- +

分析：

flush table with read lock; 持有（COMMIT，MDL_EXPLICIT，MDL_SHARED）

commit; 时上（COMMIT，MDL_EXPLICIT，MDL_INTENTION_EXCLUSIVE）锁，MDL_SHARED和MDL_INTENTION_EXCLUSIVE是不相容的，因此发生等待.。

四、MDL锁的性能与并发改进

讲到这会发现MDL锁的开销并不比InnoDB层的行锁要小，而且这可能是一个更为密集的并发瓶颈。MySQL 5.6和5.5版本通常通过调整如下两个参数来进行并发调优:

metadata_locks_cache_size：MDL锁的缓存大小。

metadata_locks_hash_instances：通过分片来提高并发度，与InnoDB AHI类似。

MySQL 5.7 MDL锁的最大改进之处在于将MDL锁的机制通过lock free算法来实现，从而提高了在多核并发下数据库的整体性能提升。

五、MDL锁的诊断

MySQL 5.7版本之前并没有提供一个方便的途径来查看MDL锁，github上有一名为mysql-plugin-mdl-info的项目，通过插件的方式来查看，非常有想法的实现。好在官方也意识到了这个问题，于是在MySQL 5.7中的performance_schea库下新增了一张表metadata_locks，用其来查看MDL锁那是相当的方便：

1	mysql > SELECT * FROM performance_schema .metadata_locks ;

不过默认并没有打开此功能，可通过setup_instruments表查看。

1	mysql > SELECT * FROM performance_schema .setup_instruments ;

需要手工将wait/lock/metadata/sql/mdl监控给打开:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql > UPDATE performance_schema .setup_consumers SET ENABLED = 'YES' WHERE NAME = 'global_instrumentation' ; mysql > UPDATE performance_schema .setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl' ; mysql > select * from performance_schema .metadata_locks \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * * OBJECT_TYPE : TABLE OBJECT_SCHEMA : performance_schema OBJECT_NAME : metadata_locks OBJECT_INSTANCE_BEGIN : 140388723649872 LOCK_TYPE : SHARED_READ LOCK_DURATION : TRANSACTION LOCK_STATUS : GRANTED SOURCE : sql_parse .cc : 5927 OWNER_THREAD_ID : 7738281 OWNER_EVENT_ID : 14

会话1

1 2	mysql > lock table xx read ; Query OK , 0 rows affected ( 0.21 sec )

会话2：

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql > SELECT * FROM performance_schema .metadata_locks \ G ; * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * * OBJECT_TYPE : TABLE OBJECT_SCHEMA : performance_schema OBJECT_NAME : metadata_locks OBJECT_INSTANCE_BEGIN : 240554768 LOCK_TYPE : SHARED_READ LOCK_DURATION : TRANSACTION LOCK_STATUS : GRANTED SOURCE : sql_parse .cc : 5927 OWNER_THREAD_ID : 38 OWNER_EVENT_ID : 10 1 row in set ( 0.00 sec )