共计 4532 个字符，预计需要花费 12 分钟才能阅读完成。
给表新增字段时，发现锁表了，查看进程，提示 Waiting for table metadata lock，等待锁释放；然而蛋疼的是几分钟过去了，依然没有任何的进展，特此记录下这个问题的定位过程以及 MDL 的相关背景知识
看到上面的表现，基本问题就来了
Metadata Lock 是什么鬼
是什么原因导致一直等待
<!– more –>
I. 问题定位
首先需要确认什么地方加锁，从 mysql 出发，应该怎么定位？
1. 定位过程
对于 mysql 而言，一般来讲上锁和事物时伴生关系，所以我们的直观出发点就是查找 db 当前正在执行的事物
— 查询当前正在执行的事物的 sql
SELECT * FROM information_schema.INNODB_TRX;
输出结果如下，首先拿到事物对应的进程 id
拿到 id 之后，则可以分析对应的进程信息
— 查询进程信息
show processlist
— 查询所有的进程信息
show full processlist
然后定位到具体的进程
然后登陆到目标机器，查看端口号对应的进程，通过 lsof 命令查看
lsof -i tcp:52951
从图中可以看出，是一个 python 进程的 mysql 连接开启的事物，进程 id 为 5436
接着查看进程对应的信息
ps aux | grep 5436
这个脚本正是测试 aiomysql 的 python 脚本，内容比较简单
import asyncio
import aiomysql
loop = asyncio.get_event_loop()
@asyncio.coroutine
def test_example():
conn = yield from aiomysql.connect(host=’127.0.0.1′, port=3306,
user=’root’, password=”, db=’test’,
loop=loop, autocommit=False)
cur = yield from conn.cursor()
yield from cur.execute(“SELECT * from test_table”)
print(cur.description)
r = yield from cur.fetchall()
print(r)
yield from cur.close()
conn.close()
loop.run_until_complete(test_example())
2. 原因分析
对 python 不太熟，直接借助 google 查一下，发现有同样的问题
[Why aiomysql locks the table even when using context manager?
](https://stackoverflow.com/que…
这个问题抛出，在通过 with 打开连接获取游标后，执行 mysql，但是没有 commit 之前，会锁表，这个期间修改表都会出现等待
下面近给出了解答，并没有看到更多的深层次的说明，先记录下，解决办法就是在创建连接池的时候，选择自动提交方式，然后就不会有这个问题了
pool = await aiomysql.create_pool(
host=”localhost”,
user=”test”,
password=”test”,
db=”test”,
autocommit=True,
cursorclass=DictCursor,
loop=loop)
II. Metadata Lock 说明
找到一篇文章说 MDL 的，推荐详细阅读 MySQL 表结构变更你不可不知的 Metadata Lock 详解
1. MDL 说明
抓一下核心的要点，简单说一下看完这篇文章之后的朴素理解
MetaData Lock 简称为 MDL，简单来说就是表的元数据锁；当修改表结构的时候，就需要持有这个锁
a. 作用
MDL 的主要作用只有一点，保护一个正在执行的事物表结构不被修改
有一个原则，MDL 是事物级别的，只有事物结束之后才会释放，而这里面说的事物分为两类
显示事物：
关闭 autocommit
以 begin 或 start transaction 开始的操作
AC-NL-RO(auto-commit non-locking read-only):
auto commit 开启之下的 select 操作
b. 实例说明
直接看上面的说明，不太直观，一个经典的 case 如下
session1 开启了一个事物，执行查询操作；但是现在 session2 要删除表，如果执行成功，那么 session1 的第二次查询就跪了，这样就违背了事物的原则，所有在 5.5 版本引入了 MDL，来保证在事物执行期间，表结构不被修改
2. 出现 MDL 等待原因及解决方法
当我们出现修改表结构，就需要获取 MDL 的排他锁，因此只有这个表没有事物在执行时，才能获取成功；当持有独占锁之后，这个表的其他操作将被阻塞（即不能插入数据，修改数据，也不能开启事物操作）
因此在执行 DDL 时，一直出现等待 MDL 的时候，常见的原因有下面三个
a. 长事物，阻塞 DDL，从而阻塞所有同表的后续操作
通过 show processlist 看到表上有正在进行的操作（包括读），此时修改表时也会等待获取 MDL，这种时候解决办法要么就是等待执行完毕，要么就是直接 kill 掉进程
b. 未提交事物，阻塞 DDL
通过 show processlist 没有找到表上的操作，但是通过 information_schema.innodb_trx 发现有未提交的事物，
c. 异常的状况
通过 show processlist 和事物查询都没有的情况下，可能的场景是一个显示的事物中，对表的操作出现了异常，虽然事物失败，但是持有的锁还没有释放，也会导致这个原因
可以在 performance_schema.events_statements_current 表中查询失败的语句
3. MDL 分类与 sql 实例
前面两小节，分别说明什么是 MDL（朴素理解为表的元数据锁），以及当修改表时出现长时间的等待 MDL 的原因分析；正常看完之后，应该会有下面的疑惑
MDL 有哪些类型
哪些 sql 会持有 MDL
对于 MDL 的类型，从网上截一张图
接下来需要分析下不同锁模式对应的 sql
属性
含义
事例
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, 可以说是为了 online ddl 才引入的。特点是允许 DML，防止 DDL；
alter table/create index/drop index 会加该锁; 加入下锁（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）
4，小结
上面的内容，可能信息量比较大，特别是 MDL 的锁分类情况，很难抓住重点，针对我们日常接触中，简单给出小结
MDL 是为了保证事物执行过程中，表结构不被修改引入的；因此修改表结构的前提是这个表上没有事物（没有正在执行，失败，或者未提交的事物）
DDL 执行，一般来讲是需要获取排他的 MDL
DML 都会开启事物，因此会获取 MDL_SW 锁
DQL 语句会获取 MDL_SR 锁
几个简称的说明
MDL: metadata lock，可以简单理解为表的元数据锁
DDL: 数据定义语言，可以简单理解为表的操作，如创建，修改，删除表、视图等，新增索引、字段等操作
DML: 数据操作语言，也就是我们常规理解的 insert, update, delete 语句
DQL: 数据查询语言，常见的 select 语句
几个常见疑问解答
a. 为什么同一张表的多个 DDL 不能并行执行
MDL 读锁是互相兼容的, 可以有多个增删查改
MDL 写锁是互斥的, 只能有一个表的 DDL
b. 为什么有时候 DDL 会卡住
MDL 读写锁之间是互斥的, 所以如果 DDL 卡住, 就证明有事务在执行, 不能申请 MDL 写锁
c. 常见卡住的场景
非常频繁的业务高峰期
有慢查询把持着 MDL 读锁
有事物一直未提交
d. 为什么需要 MDL 锁
当事务本身执行的时候理论上是不能容忍表结构在中途发生改变的
5. 更多参考
相关博文或者问答
[Why aiomysql locks the table even when using context manager?
](https://stackoverflow.com/que…
MySQL 表结构变更你不可不知的 Metadata Lock 详解
理解 MySQL 的 MDL 元数据锁
II. 其他
1. 一灰灰 Blog：https://liuyueyi.github.io/he…
一灰灰的个人博客，记录所有学习和工作中的博文，欢迎大家前去逛逛
2. 声明
尽信书则不如，已上内容，纯属一家之言，因个人能力有限，难免有疏漏和错误之处，如发现 bug 或者有更好的建议，欢迎批评指正，不吝感激
微博地址: 小灰灰 Blog
QQ：一灰灰 /3302797840
3. 扫描关注
一灰灰 blog
知识星球
