给表新增字段时，发现锁表了，查看进程，提示Waiting for table metadata lock，等待锁释放；然而蛋疼的是几分钟过去了，依然没有任何的进展，特此记录下这个问题的定位过程以及MDL的相关背景知识看到上面的表现，基本问题就来了Metadata Lock 是什么鬼是什么原因导致一直等待<!– more –>I. 问题定位首先需要确认什么地方加锁，从mysql出发，应该怎么定位？1. 定位过程对于mysql而言，一般来讲上锁和事物时伴生关系，所以我们的直观出发点就是查找db当前正在执行的事物– 查询当前正在执行的事物的sqlSELECT * 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 asyncioimport aiomysqlloop = asyncio.get_event_loop()@asyncio.coroutinedef 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_WRITEMDL_EXCLUSIVE(X)防止其他线程读写元数据CREATE/DROP/RENAME TABLE，其他online DDL在rename阶段也持有X锁(TABLE，MDL_TRANSACTION，MDL_EXCLUSIVE）4， 小结上面的内容，可能信息量比较大，特别是MDL的锁分类情况，很难抓住重点，针对我们日常接触中，简单给出小结MDL是为了保证事物执行过程中，表结构不被修改引入的；因此修改表结构的前提是这个表上没有事物（没有正在执行，失败，或者未提交的事物）DDL执行，一般来讲是需要获取排他的MDLDML都会开启事物，因此会获取 MDL_SW 锁DQL语句会获取 MDL_SR 锁几个简称的说明MDL: metadata lock，可以简单理解为表的元数据锁DDL: 数据定义语言，可以简单理解为表的操作，如创建，修改，删除表、视图等，新增索引、字段等操作DML: 数据操作语言，也就是我们常规理解的 insert, update, delete 语句DQL: 数据查询语言，常见的select语句几个常见疑问解答a. 为什么同一张表的多个DDL不能并行执行MDL读锁是互相兼容的,可以有多个增删查改MDL写锁是互斥的,只能有一个表的DDLb. 为什么有时候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或者有更好的建议，欢迎批评指正，不吝感激微博地址: 小灰灰BlogQQ： 一灰灰/33027978403. 扫描关注一灰灰blog知识星球