190308-Mysql DDL出现长时间等待MDL问题分析
by 一灰灰

2019-03-08

DB / Mysql

17 分钟读完 (大约 2505 个字) 0次访问

190308-Mysql DDL出现长时间等待MDL问题分析

文章目录

给表新增字段时，发现锁表了，查看进程，提示Waiting for table metadata lock，等待锁释放；然而蛋疼的是几分钟过去了，依然没有任何的进展

现在就有几个问题了

Metadata Lock 是什么鬼
是什么原因导致一直等待

I. 问题定位

首先需要确认什么地方加锁，从mysql出发，应该怎么定位？

1. 定位过程

对于mysql而言，一般来讲上锁和事物时伴生关系，所以我们的直观出发点就是查找db当前正在执行的事物

1 2	-- 查询当前正在执行的事物的sql SELECT * FROM information_schema.INNODB_TRX;

输出结果如下，首先拿到事物对应的进程id

拿到id之后，则可以分析对应的进程信息

-- 查询进程信息
show processlist

-- 查询所有的进程信息
show full processlist

然后定位到具体的进程

然后登陆到目标机器，查看端口号对应的进程，通过lsof命令查看

1	lsof -i tcp:52951

从图中可以看出，是一个python进程的mysql连接开启的事物，进程id为5436

接着查看进程对应的信息

1	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?

这个问题抛出，在通过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. 更多参考

II. 其他

1. 一灰灰Blog： https://liuyueyi.github.io/hexblog

一灰灰的个人博客，记录所有学习和工作中的博文，欢迎大家前去逛逛

2. 声明

尽信书则不如，已上内容，纯属一家之言，因个人能力有限，难免有疏漏和错误之处，如发现bug或者有更好的建议，欢迎批评指正，不吝感激

微博地址: 小灰灰Blog
QQ：一灰灰/3302797840

3. 扫描关注

一灰灰blog

QrCode

知识星球

goals

# Mysql

支付宝

微信

一灰灰Blog

Java,Python,DBA, 主旨服务器，兼职全栈

Wuhan, China

文章

523

分类

101

标签

122

关注我

190308-Mysql DDL出现长时间等待MDL问题分析

I. 问题定位

1. 定位过程

2. 原因分析

II. Metadata Lock说明

1. MDL 说明

a. 作用

b. 实例说明

2. 出现MDL等待原因及解决方法

a. 长事物，阻塞DDL，从而阻塞所有同表的后续操作

b. 未提交事物，阻塞DDL

c. 异常的状况

3. MDL分类与sql实例

4，小结

a. 为什么同一张表的多个DDL不能并行执行

b. 为什么有时候DDL会卡住

c. 常见卡住的场景

d. 为什么需要MDL锁

5. 更多参考

II. 其他

1. 一灰灰Blog： https://liuyueyi.github.io/hexblog

2. 声明

3. 扫描关注

喜欢这篇文章？打赏一下作者吧

评论

链接

最新文章

标签云

标签

分类

归档

分类

归档

Your browser is out-of-date!

190308-Mysql DDL出现长时间等待MDL问题分析

I. 问题定位

1. 定位过程

2. 原因分析

II. Metadata Lock说明

1. MDL 说明

a. 作用

b. 实例说明

2. 出现MDL等待原因及解决方法

a. 长事物，阻塞DDL，从而阻塞所有同表的后续操作

b. 未提交事物，阻塞DDL

c. 异常的状况

3. MDL分类与sql实例

4， 小结

a. 为什么同一张表的多个DDL不能并行执行

b. 为什么有时候DDL会卡住

c. 常见卡住的场景

d. 为什么需要MDL锁

5. 更多参考

II. 其他

1. 一灰灰Blog： https://liuyueyi.github.io/hexblog

2. 声明

3. 扫描关注

喜欢这篇文章？打赏一下作者吧

评论

链接

最新文章

标签云

标签

分类

归档

分类

归档

Your browser is out-of-date!

4，小结