如何理解mysql Meta Lock 机制

如何理解mysql Meta Lock机制，针对这个问题，本文详细介绍了相应的分析和解答，希望能帮助更多想要解决这个问题的小伙伴找到更简单更容易的方法。

原因

最近上线过程中，主从数据不一致。发现问题是从机在做ddl操作时等待Waitingfortable metadatalock。

发现查询用户连接了几个进程，字节杀死了这些线程。从机几乎瞬间完成同步。

因为停止应用的在线操作等待时间较长。但对业务没有影响。

发现时间，应用程序未启动。这也导致了图书馆的雪崩问题。

来解决这个问题。而且未来，对在线停机的要求会更严格。短时间停机或内存分离、读写或ddl分析如下

为什么要有MDL 锁

在MySQL5.1版本5.1及更低版本中，如果存在未提交的事务trx，则在执行DROP/RENAME/ALTER TABLE RENAME操作时，它不会被其他事务阻止。这将导致以下问题(MySQL错误#989)

主：

未提交的事务，但是SQL已经完成(binlog也准备好了)，并且表模式发生了变化，这在提交时不会被注意到。

奴隶：

在binlog中是按照事务提交的顺序记录的，DDL是隐式提交的，所以先在备用中执行DDL，然后执行事务trx。因为trx作用的表已经改变，trx将无法执行。

DDL中主库的DML压力越大，触发这个问题的可能性就越高。

一个简单的例子：

1、设置自动提交=0，对表b执行一个DML。

root @ xxx 11:48:28set设置自动提交=0；

查询正常，0行受影响(0.00秒)

root @ xxx 11:48:35insert插入b值(NULL，4)；

查询正常，1行受影响(0.00秒)

会话2，执行重命名表a为tmp _ B。

root@xxx 11:48:23rename表b到tmp _ b；

查询正常，0行受影响(0.01秒)

session1:提交；

root @ XXX 11:49:00 show binlog事件；

—————— — —-——— ——— ——— —————————————

|日志_名称|位置|事件_类型|服务器_id |结束_日志_位置|信息|

—————— — —-——— ——— ——— —————————————

| MySQL-bin . 000001 | 4 | Format _ desc | 12 | 106 | Server ver : 5 . 1 . 48-log，Binlog ver: 4 |

| MySQL-bin . 000001 | 106 | Query | 12 | 191 |使用` xxx将表b重命名为tmp_b |

| MySQL-bin . 000001 | 191 | Query | 12 | 258 | BEGIN nb

sp;     |

| mysql-bin.000001 | 258 | Table_map   |        12 |         298 | table_id: 195 (xxx.b)                 |

| mysql-bin.000001 | 298 | Write_rows  |        12 |         336 | table_id: 195 flags: STMT_END_F       |

| mysql-bin.000001 | 336 | Xid         |        12 |         363 | COMMIT /* xid=737 */                  |

+——————+—–+—-———+———–+——–—–+—————————————+

显然当这样的Binlog同步到备库的话，必然会导致复制中断。

在5.1里可以通过如下步骤绕过bug：

>set autocommit = 0;

>lock tables t1 write;

> drop table t1 / alter table t1 rename to t2

rename table t1 to t2这样的DDL不适用于上述方法。

在5.5引入了MDL（meta data lock）锁来解决在这个问题，至于5.1，官方已经明确回复不会FIX，太伤感了。。。

MDL 的类型
  metadata lock也是一种锁。每个metadata lock都会定义锁住的对象，锁的持有时间和锁的类型。
2.1 按照对象/范围划分

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

1. Waiting for global read lock 

2. Waiting for commit lock

3. Waiting for schema metadata lock

4. Waiting for table metadata lock

5. Waiting for stored function metadata lock

6. Waiting for stored procedure metadata lock

7. Waiting for trigger metadata lock

8. Waiting for event metadata lock

2.2 按照锁的持有时间

2.3 按照操作的对象

关于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而导致等待的情况。

关于5.7 对MDL 锁的改进

在MySQL 5.7里对MDL子系统做了更为彻底的优化。主要从以下几点出发：

第一，尽管对MDL HASH进行了分区，但由于是以表名+库名的方式作为key值进行分区，如果查询或者DML都集中在同一张表上，就会hash到相同的分区，引起明显的MDL HASH上的锁竞争

针对这一点，引入了LOCK-FREE的HASH来存储MDL_lock，LF_HASH无锁算法基于论文"Split-Ordered Lists: Lock-Free Extensible Hash Tables"，实现还比较复杂。 注：实际上LF_HASH很早就被应用于Performance Schema，算是比较成熟的代码模块。

由于引入了LF_HASH，MDL HASH分区特性自然直接被废除了 。

对应WL#7305， PATCH(Rev:7249)

第二，从广泛使用的实际场景来看，DML/SELECT相比DDL等高级别MDL锁类型，是更为普遍的，因此可以针对性的降低DML和SELECT操作的MDL开销。

为了实现对DML/SELECT的快速加锁，使用了类似LOCK-WORD的加锁方式，称之为FAST-PATH，如果FAST-PATH加锁失败，则走SLOW-PATH来进行加锁。

每个MDL锁对象（MDL_lock）都维持了一个long long类型的状态值来标示当前的加锁状态，变量名为MDL_lock::m_fast_path_state 举个简单的例子：（初始在sbtest1表上对应MDL_lock::m_fast_path_state值为0）

Session 1: BEGIN;

Session 1: SELECT * FROM sbtest1 WHERE id =1; //m_fast_path_state = 1048576, MDL ticket 不加MDL_lock::m_granted队列

Session 2: BEGIN;

Session 2: SELECT * FROM sbtest1 WHERE id =2; //m_fast_path_state=1048576+1048576=2097152，同上，走FAST PATH

Session 3: ALTER TABLE sbtest1 ENGINE = INNODB; //DDL请求加的MDL_SHARED_UPGRADABLE类型锁被视为unobtrusive lock，可以认为这个是比上述SQL的MDL锁级别更高的锁，并且不相容，因此被强制走slow path。而slow path是需要加MDL_lock::m_rwlock的写锁。m_fast_path_state = m_fast_path_state | MDL_lock::HAS_SLOW_PATH | MDL_lock::HAS_OBTRUSIVE

注:DDL还会获得库级别的意向排他MDL锁或者表级别的共享可升级锁，但为了表述方便，这里直接忽略了，只考虑涉及的同一个MDL_lock锁对象。

Session 4: SELECT * FROM sbtest1 WHERE id =3; // 检查m_fast_path_state &HAS_OBTRUSIVE，如果DDL还没跑完，就会走slow path。

从上面的描述可以看出，MDL子系统显式的对锁类型进行了区分（OBTRUSIVE or UNOBTRUSIVE），存储在数组矩阵m_unobtrusive_lock_increment。 因此对于相容类型的MDL锁类型，例如DML/SELECT，加锁操作几乎没有任何读写锁或MUTEX开销。

对应WL#7304, WL#7306 ， PATCH（Rev:7067,Rev:7129）(Rev:7586)

第三，由于引入了MDL锁，实际上早期版本用于控制Server和引擎层表级并发的THR_LOCK 对于Innodb而言已经有些冗余了，因此Innodb表完全可以忽略这部分的开销。

不过在已有的逻辑中，Innodb依然依赖THR_LOCK来实现LOCK TABLE tbname READ，因此增加了新的MDL锁类型来代替这种实现。

实际上代码的大部分修改都是为了处理新的MDL类型，Innodb的改动只有几行代码。

对应WL#6671，PATCH(Rev:8232)

第四，Server层的用户锁（通过GET_LOCK函数获取）使用MDL来重新实现。

用户可以通过GET_LOCK()来同时获取多个用户锁，同时由于使用MDL来实现，可以借助MDL子系统实现死锁的检测。

注意由于该变化，导致用户锁的命名必须小于64字节，这是受MDL子系统的限制导致。

对应WL#1159, PATCH(Rev:8356)

关于如何理解mysql Meta Lock 机制问题的解答就分享到这里了，希望