如何理解mysql Meta Lock 机制

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

  原因   

  最近在上线的 过程中出现主从数据不一致的现象 。发现问题是 slave 进行ddl 操作时候等待   Waiting for table metadata lock

 发现查询用户有几个进程在连接上，字节把这些线程kill掉。slave 基本上瞬间执行完同步

因为是停应用的上线操作对对等待时间较长。但是对业务未有有影响。

   发现时间，未启动应用。也造成从库的雪崩问题。

为了解决该问题。并且在未来对线上停机时间要求比较严格。停机时间较短 or 记性读写分离 or 进行ddl时候 进行下列分析

为什么要有MDL 锁

在MySQL5.1及之前的版本中，如果有未提交的事务trx，当执行DROP/RENAME/ALTER TABLE RENAME操作时，不会被其他事务阻塞住。这会导致如下问题(MySQL bug#989)

master：

未提交的事务，但SQL已经完成(binlog也准备好了)，表schema发生更改，在commit的时候不会被察觉到。

slave：

在binlog里是以事务提交顺序记录的，DDL隐式提交，因此在备库先执行DDL，后执行事务trx，由于trx作用的表已经发生了改变，因此trx会执行失败。

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

一个简单的例子：

session1，set autocommit=0，对表b执行一条DML

root@xxx 11:48:28>set autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

root@xxx 11:48:35>insert into b values (NULL,4);

Query OK, 1 row affected (0.00 sec)

session2，执行rename table a to tmp_b

root@xxx 11:48:23>rename table b to tmp_b;

Query OK, 0 rows affected (0.01 sec)

session1：commit;

root@xxx 11:49:00>show binlog events;

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

| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |

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

| mysql-bin.000001 |   4 | Format_desc |        12 |         106 | Server ver: 5.1.48-log, Binlog ver: 4 |

| mysql-bin.000001 | 106 | Query       |        12 |         191 | use `xxx`; rename table b to tmp_b    |

| mysql-bin.000001 | 191 | Query       |        12 |         258 | BEGIN                                 |

| 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 按照对象/范围划分

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

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 按照锁的持有时间

属性	含义
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 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 机制问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。