【MySQL】MetaData Lock 之二

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

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_TRANSACTION 在一个事务中涉及所有表获取MDL，一直到事务commit或者rollback(线程中终清理)才释放。

truncate table t1;
insert into t1 values(3,abcde);会加如下锁(GLOBAL,MDL_STATEMENT,MDL_INTENTION_EXCLUSIVE）                            (SCHEMA,MDL_TRANSACTION,MDL_INTENTION_EXCLUSIVE)


用于访问information_scheam表，不涉及数据。 select * from information_schema.tables;
show create table xx; desc xxx;会加如下锁：
(TABLE,MDL_TRANSACTION,MDL_SHARED_HIGH_PRIO)
是mysql5.6引入的新的metadata lock,
在alter table/create index/drop index会加该锁;可以说是为了online ddl才引入的。特点是允许DML，防止DDL；
（TABLE，MDL_TRANSACTION，MDL_SHARED_UPGRADABLE）
可升级锁，访问表结构并且读写表数据，并且禁止其它事务写。 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
防止其他线程读写元数据 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 MDL 锁的兼容性矩阵



三、几种典型语句的加(释放)锁流程
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锁，大部分情况都是线上DB出现异常了。那么出现异常后，我们如何去判断是MDL锁导致的呢。监视MDL锁主要有两种方法，一种是通过show  processlist命令，判断是否有事务处于“Waiting for table metadata lock”状态，另外就是通过mysql的profile，分析特定语句在每个阶段的耗时时间。
抛出几个问题：
select 与alter是否会相互阻塞
dml与alter是否会相互阻塞
select与DML是否会相互阻塞
结合第三节几种语句的上锁流程，我们很容易得到这三个问题的答案。语句会在阻塞在具体某个环节，可以通过profile来验证我们的答案是否正确。
第一个问题，当执行select语句时，只要select语句在获取MDL_SHARED_READ锁之前，alter没有执行到rename阶段，那么select获取MDL_SHARED_READ锁成功，后续有alter执行到rename阶段，请求MDL_EXCLUSIVE锁时，就会被阻塞。rename阶段会持有MDL_EXCLUSIVE锁，但由于这个过程时间非常短(大头都在copy数据阶段)，并且是alter的最后一个阶段，所以基本感觉不到alter会阻塞select语句。由于MDL锁在事务提交后才释放，若线上存在大查询，或者存在未提交的事务，则会出现ddl卡住的现象。这里要注意的是，ddl卡住后，若再有select查询或DML进来，都会被堵住，就会出现threadrunning飙高的情况。
第二个问题，alter在opening阶段会将锁升级到MDL_SHARED_NO_WRITE，rename阶段再将升级为MDL_EXCLUSIVE，由于MDL_SHARED_NO_WRITE与MDL_SHARED_WRITE互斥，所以先执行alter或先执行DML语句，都会导致语句阻塞在opening tables阶段。
第三个问题，显然，由于MDL_SHARED_WRITE与MDL_SHARED_READ兼容，所以它们不会因为MDL而导致等待的情况。

MYSQL METADATA LOCK(MDL LOCK)学习(1) 理论知识和加锁类型测试 原创，水平有限如有错误请指出共同探讨本文中某些结论性的东西我用黄色标记出来请大家多多留意一下另外我家二娃刚刚出生，大家祝福我一下吧。谢谢！^_^ http://blog.itpub.net/7728585/viewspace-2143093/源码版本:5.7.14注意MDL和DML术语的不同。