mysql锁——元数据锁和innodb锁

清疚 2022-05-22 04:20 408阅读 0赞

### 一、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 。例如下面的这种情形：

[![理解MySQL的MDL元数据锁][MySQL_MDL]][MySQL_MDL_MySQL_MDL]

若没有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存储引擎》][MySQL_InnoDB]）。但是MDL锁对象的层次更多，简单来看有如下的层次：

[![理解MySQL的MDL元数据锁][MySQL_MDL 1]][MySQL_MDL 1]

上图中显示了最常见的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 按照对象/范围划分

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

2.2 按照锁的持有时间

2.3 按照操作的对象

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

关于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操作。

<table style="font-size:12px;margin-left:-28px;border:none;padding:0px;margin-right:0px;margin-bottom:0px;margin-top:0px;width:auto;background:none;border-spacing:0px;table-layout:auto;"> 
 <tbody> 
 <tr style="background:0px center;padding:0px;border-style:none;vertical-align:top;margin:0px;"> 
 <td style="border-bottom:0px;border-top:0px;border-left:0px;font-family:'Courier New', monospace;vertical-align:top;padding:0px;margin:0px;background:rgb(0,0,0);color:rgb(137,137,137);"> 
 <div style="background:0px center;border:0px;padding:0px;margin:0px;white-space:nowrap;line-height:20px;"> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 1 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 2 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 3 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 4 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 5 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 6 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 7 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 8 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 9 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 10 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 11 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 12 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 13 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 14 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 15 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 16 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 17 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 18 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 19 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 20 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 21 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 22 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 23 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 24 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 25 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 26 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 27 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 28 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 29 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 30 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 31 
 </div> 
 </div></td> 
 <td style="border-width:0px;background:0px center;width:1103.2px;font-family:'Courier New', monospace;vertical-align:top;padding:0px;margin:0px;"> 
 <div style="padding:0px;margin:0px;white-space:pre;background:none;border:none;color:rgb(255,255,255);line-height:20px;"> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 # SESSION A 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 mysql 
 &gt; 
 
 insert 
 into 
 sbtest2 
 select 
 
 * 
 
 from 
 sbtest1 
 ; 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;">
 &nbsp; 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 # SESSION B 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 mysql 
 &gt; 
 
 alter 
 table 
 sbtest2 
 add 
 test1 
 int 
 ; 
 &nbsp;&nbsp; 
 / 
 /等待 
 SESSION 
 
 A执行完 
 ; 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;">
 &nbsp; 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 # SESSION C 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 mysql 
 &gt; 
 
 show 
 processlist 
 ; 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 + 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 | 
 
 Id 
 &nbsp;&nbsp; 
 | 
 
 User 
 
 | 
 
 Host 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 | 
 
 db 
 &nbsp;&nbsp;&nbsp;&nbsp; 
 | 
 
 Command 
 
 | 
 
 Time 
 
 | 
 
 State 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 | 
 
 Info 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 | 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 + 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 | 
 
 267 
 
 | 
 
 root 
 
 | 
 
 localhost 
 
 | 
 
 sbtest 
 
 | 
 
 Query 
 &nbsp;&nbsp; 
 | 
 &nbsp;&nbsp;&nbsp;&nbsp; 
 7 
 
 | 
 
 Sending 
 data 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 | 
 
 insert 
 into 
 sbtest2 
 select 
 
 * 
 
 from 
 sbtest1 
 
 | 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 | 
 
 271 
 
 | 
 
 root 
 
 | 
 
 localhost 
 
 | 
 
 sbtest 
 
 | 
 
 Query 
 &nbsp;&nbsp; 
 | 
 &nbsp;&nbsp;&nbsp;&nbsp; 
 3 
 
 | 
 
 Waiting 
 for 
 
 table 
 metadata 
 lock 
 
 | 
 
 alter 
 table 
 sbtest2 
 add 
 test1 
 int 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 | 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 | 
 
 272 
 
 | 
 
 root 
 
 | 
 
 localhost 
 
 | 
 
 NULL 
 &nbsp;&nbsp; 
 | 
 
 Query 
 &nbsp;&nbsp; 
 | 
 &nbsp;&nbsp;&nbsp;&nbsp; 
 0 
 
 | 
 
 starting 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 | 
 
 show 
 processlist 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 | 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 + 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 3 
 
 rows 
 in 
 
 set 
 
 ( 
 0.00 
 
 sec 
 ) 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;">
 &nbsp; 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 # SESSION D 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 mysql 
 &gt; 
 
 select 
 
 * 
 
 from 
 sbtest2 
 limit 
 
 10 
 ; 
 &nbsp;&nbsp;&nbsp;&nbsp; 
 / 
 /等待元数据锁 
 ; 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;">
 &nbsp; 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 # SESSION E 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 mysql 
 &gt; 
 
 show 
 processlist 
 ; 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 + 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 | 
 
 Id 
 &nbsp;&nbsp; 
 | 
 
 User 
 
 | 
 
 Host 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 | 
 
 db 
 &nbsp;&nbsp;&nbsp;&nbsp; 
 | 
 
 Command 
 
 | 
 
 Time 
 
 | 
 
 State 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 | 
 
 Info 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 | 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 + 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 | 
 
 267 
 
 | 
 
 root 
 
 | 
 
 localhost 
 
 | 
 
 sbtest 
 
 | 
 
 Query 
 &nbsp;&nbsp; 
 | 
 &nbsp;&nbsp; 
 20 
 
 | 
 
 Sending 
 data 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 | 
 
 insert 
 into 
 sbtest2 
 select 
 
 * 
 
 from 
 sbtest1 
 
 | 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 | 
 
 271 
 
 | 
 
 root 
 
 | 
 
 localhost 
 
 | 
 
 sbtest 
 
 | 
 
 Query 
 &nbsp;&nbsp; 
 | 
 &nbsp;&nbsp; 
 13 
 
 | 
 
 Waiting 
 for 
 
 table 
 metadata 
 lock 
 
 | 
 
 alter 
 table 
 sbtest2 
 add 
 test1 
 int 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 | 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 | 
 
 272 
 
 | 
 
 root 
 
 | 
 
 localhost 
 
 | 
 
 NULL 
 &nbsp;&nbsp; 
 | 
 
 Query 
 &nbsp;&nbsp; 
 | 
 &nbsp;&nbsp;&nbsp;&nbsp; 
 0 
 
 | 
 
 starting 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 | 
 
 show 
 processlist 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 | 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 | 
 
 308 
 
 | 
 
 root 
 
 | 
 
 localhost 
 
 | 
 
 sbtest 
 
 | 
 
 Query 
 &nbsp;&nbsp; 
 | 
 &nbsp;&nbsp;&nbsp;&nbsp; 
 3 
 
 | 
 
 Waiting 
 for 
 
 table 
 metadata 
 lock 
 
 | 
 
 select 
 
 * 
 
 from 
 sbtest2 
 limit 
 
 10 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 | 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 + 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 4 
 
 rows 
 in 
 
 set 
 
 ( 
 0.00 
 
 sec 
 ) 
 </div> 
 </div></td> 
 </tr> 
 </tbody> 
</table>

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

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

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

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

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

然后找到其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会被堵住

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

查看SESSION A

查看SESSION C

从上述测试可以看出，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场景：

查看进行信息：

<table style="font-size:12px;margin-left:-20px;border:none;padding:0px;margin-right:0px;margin-bottom:0px;margin-top:0px;width:auto;background:none;border-spacing:0px;table-layout:auto;"> 
 <tbody> 
 <tr style="background:0px center;padding:0px;border-style:none;vertical-align:top;margin:0px;"> 
 <td style="border-bottom:0px;border-top:0px;border-left:0px;font-family:'Courier New', monospace;vertical-align:top;padding:0px;margin:0px;background:rgb(0,0,0);color:rgb(137,137,137);"> 
 <div style="background:0px center;border:0px;padding:0px;margin:0px;white-space:nowrap;line-height:20px;"> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 1 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 2 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 3 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 4 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 5 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 6 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 7 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 8 
 </div> 
 </div></td> 
 <td style="border-width:0px;background:0px center;width:901.6px;font-family:'Courier New', monospace;vertical-align:top;padding:0px;margin:0px;"> 
 <div style="padding:0px;margin:0px;white-space:pre;background:none;border:none;color:rgb(255,255,255);line-height:20px;"> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 mysql 
 &gt; 
 
 show 
 processlist 
 ; 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 + 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 + 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 | 
 
 Id 
 
 | 
 
 User 
 
 | 
 
 Host 
 
 | 
 
 db 
 
 | 
 
 Command 
 
 | 
 
 Time 
 
 | 
 
 State 
 
 | 
 
 Info 
 
 | 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 + 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 + 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 | 
 
 1 
 
 | 
 
 root 
 
 | 
 
 localhost 
 : 
 5202 
 &nbsp;&nbsp; 
 | 
 
 test 
 
 | 
 
 Query 
 
 | 
 
 12 
 
 | 
 
 altering 
 table 
 
 | 
 
 alter 
 table 
 t1 
 add 
 c3 
 bigint 
 
 | 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 | 
 
 2 
 
 | 
 
 root 
 
 | 
 
 localhost 
 : 
 14699 
 
 | 
 
 test 
 
 | 
 
 Query 
 
 | 
 
 3 
 
 | 
 
 Waiting 
 for 
 
 global 
 read 
 
 lock 
 
 | 
 
 set 
 global 
 read_only 
 = 
 on 
 
 | 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 | 
 
 3 
 
 | 
 
 root 
 
 | 
 
 localhost 
 : 
 17085 
 
 | 
 
 NULL 
 
 | 
 
 Query 
 
 | 
 
 0 
 
 | 
 
 init 
 
 | 
 
 show 
 processlist 
 
 | 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 + 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 + 
 </div> 
 </div></td> 
 </tr> 
 </tbody> 
</table>

分析：

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场景：

查看进行信息：

<table style="font-size:12px;margin-left:-20px;border:none;padding:0px;margin-right:0px;margin-bottom:0px;margin-top:0px;width:auto;background:none;border-spacing:0px;table-layout:auto;"> 
 <tbody> 
 <tr style="background:0px center;padding:0px;border-style:none;vertical-align:top;margin:0px;"> 
 <td style="border-bottom:0px;border-top:0px;border-left:0px;font-family:'Courier New', monospace;vertical-align:top;padding:0px;margin:0px;background:rgb(0,0,0);color:rgb(137,137,137);"> 
 <div style="background:0px center;border:0px;padding:0px;margin:0px;white-space:nowrap;line-height:20px;"> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 1 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 2 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 3 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 4 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 5 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 6 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 7 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;text-align:center;height:inherit;">
 8 
 </div> 
 </div></td> 
 <td style="border-width:0px;background:0px center;width:876px;font-family:'Courier New', monospace;vertical-align:top;padding:0px;margin:0px;"> 
 <div style="padding:0px;margin:0px;white-space:pre;background:none;border:none;color:rgb(255,255,255);line-height:20px;"> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 mysql 
 &gt; 
 
 show 
 processlist 
 ; 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 + 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 + 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 | 
 
 Id 
 
 | 
 
 User 
 
 | 
 
 Host 
 
 | 
 
 db 
 
 | 
 
 Command 
 
 | 
 
 Time 
 
 | 
 
 State 
 
 | 
 
 Info 
 
 | 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 + 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 + 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 | 
 
 1 
 
 | 
 
 root 
 
 | 
 
 127.0.0.1 
 : 
 5202 
 
 | 
 
 test 
 
 | 
 
 Query 
 
 | 
 
 7 
 
 | 
 
 Waiting 
 for 
 
 commit 
 lock 
 
 | 
 
 commit 
 
 | 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 | 
 
 2 
 
 | 
 
 root 
 
 | 
 
 127.0.0.1 
 : 
 14699 
 
 | 
 
 test 
 
 | 
 
 Sleep 
 
 | 
 
 13 
 
 | 
 
 | 
 
 NULL 
 
 | 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 | 
 
 3 
 
 | 
 
 root 
 
 | 
 
 127.0.0.1 
 : 
 17085 
 
 | 
 
 NULL 
 
 | 
 
 Query 
 
 | 
 
 0 
 
 | 
 
 init 
 
 | 
 
 show 
 processlist 
 
 | 
 </div> 
 <div style="font-family:inherit;background:0px center;border:0px;padding:0px 5px;margin:0px;height:inherit;"> 
 + 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 - 
 + 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 -- 
 + 
 </div> 
 </div></td> 
 </tr> 
 </tbody> 
</table>

分析：

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锁那是相当的方便：

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

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

会话1

会话2：

[MySQL_MDL]: http://www.ywnds.com/wp-content/uploads/2016/11/2016112810215353-1000x145.png
[MySQL_MDL_MySQL_MDL]: /images/20220522/3383337212654e96b392d0acc7ab4686.png
[MySQL_InnoDB]: http://www.innomysql.com/article/97.html
[MySQL_MDL 1]: /images/20220522/078553a6c7914b43b62cfeefdaf0cd2e.png
[Waiting for table metadata lock]: http://www.ywnds.com/?p=10091%20