MySQL 实战笔记 第02期：MySQL 元数据锁
作者简介
无为，多年 MySQL DBA 工作经验，现就职于某知名互联网公司，对 MySQL、 Redis、PostgrepSQL 等主流数据库有一定了解，拥有丰富的一线运维经验。
当我们在 MySQL 中执行 DDL 语句时，经常会发现语句没有在你预期的时间完成，这时候我们通常会使用 show full processlist ，来看看发生了什么状况。当你看到 waiting for table metadata lock 时，那就碰到元数据锁了。那元数据锁是怎样产生的又应该怎样避免呢？让我们从这篇文章开始了解它。
1、什么是元数据锁
MDL 全称为 metadata lock，即元数据锁，一般也可称为字典锁。MDL 的主要作用是为了管理数据库对象的并发访问和确保元数据一致性。元数据锁适用对象包含：table、schema、procedures, functions, triggers, scheduled events、tablespaces 。
2、加锁规则
获取规则：
语句逐个（ one by one ）获取元数据锁，不是同时获取，并在获取过程中执行死锁检测。
DML 语句获取锁按照语句中 table 出现的顺序来获取锁。
DDL 语句、LOCK TABLES 和其他类似语句按名称顺序获取锁，对于隐式使用的表（例如外键关系中也必须锁定的表）可能会以不同的顺序获取锁。
DDL 的写锁请求优先级高于 DML
3、模拟加锁规则
两个相同表结构的表 t 和 t_new 开始。三个线程来操作这些表：
场景一
线程 1:
LOCK TABLE t WRITE, t_new WRITE;
该语句按表名顺序在 t 和 t_new 上获取写锁
线程 2:
INSERT INTO t VALUES(1);
该语句处于也需要获取表 t 上的 MDL 所以处于等待状态
线程 3:
RENAME TABLE t TO t_old, t_new TO t;
该语句需要按表名顺序在 t 、t_new、t_old 上获取互斥锁，所以也处于等待状态
线程 1:
UNLOCK TABLES;
该语句释放对 t 和 t_new 的写锁定。线程 3 对 t 加写锁的优先级高于 线程 2 ，因此线程 3 在 t 上优先获得互斥锁，然后依次在 t_new、t_old 上获取互斥锁，执行重命名后释放其锁定。线程 2 获得 t 上的写锁，执行插入操作，然后释放其锁定。rename 操作在 insert 之前执行。
场景二
两个具有相同表结构的表 t 和 new_t ，同样是三个线程来操作这些表
线程 1:
LOCK TABLE t WRITE, new_t WRITE;
该语句按表名顺序在 new_t 和 t 上获取写锁
线程 2:
INSERT INTO t VALUES(1);
该语句处于也需要获取表 t 上的 MDL 所以处于等待状态
线程 3:
RENAME TABLE t TO old_t, new_t TO t;
该语句需要按表名顺序在 new_t 、old_t、t 上获取互斥锁，所以也处于等待状态
该语句释放对 t 和 new_t 的写锁定。对于 t 首先发起锁请求的是线程 2 ，因此线程 2 优先获得了 t 上的元数据写锁，执行完插入操作，然后释放该锁。线程 3 首先获取的是 new_t 、old_t 的互斥锁，最后才会请求 t 上的互斥锁，所以线程 3 在线程 2 执行完毕之前都是处于等待状态的。rename 操作在 insert 操作之后。
4、 如何监控元数据锁
performance_schema.metadata_locks 表中记录了元数据锁相关的信息，开启方式如下：
在线开启 metadata_locks，操作如下：
--UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME ='global_instrumentation';
--此值默认已开启了，可检查确认。
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl';
若可停库维护，则在 my.cnf 中添加如下：
[mysqld]
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
5、如何优化元数据锁
MDL 锁一旦发生会对业务造成极大影响，因为后续所有对该表的访问都会被阻塞，造成连接积压。我们日常要尽量避免 MDL 锁的发生，下面给出几点优化建议可供参考：
开启 metadata_locks 表记录 MDL 锁。
设置参数 lock_wait_timeout 为较小值，使被阻塞端主动停止。
规范使用事务，及时提交事务，避免使用大事务。
增强监控告警，及时发现 MDL 锁。
DDL 操作及备份操作放在业务低峰期执行。
另外，悦专栏欢迎各位大牛投稿，内容可以是数据库、开发、算法、大数据、运维、产品、运营等。只要在公众号回复“投稿”即可。
LIKECOLUMN
悦专栏
在这里，学好编程
做更优秀的 IT人！
各大平台都可以找到我
微信公众号：杨建荣的学习笔记
Github：@jeanron100
CSDN：@jeanron100
知乎：@jeanron100
头条号：@杨建荣的学习笔记
网易号：@杨建荣的数据库笔记
大鱼号：@杨建荣的数据库笔记
百家号：@杨建荣的数据库笔记
腾讯云+社区：@杨建荣的学习笔记
QQ群号：763628645
QQ群二维码如下， 添加请注明：姓名+地区+职位，否则不予通过