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

给表新增字段时,发现锁表了,查看进程,提示Waiting for table metadata lock,等待锁释放;然而蛋疼的是几分钟过去了,依然没有任何的进展,特此记录下这个问题的定位过程以及MDL的相关背景知识

看到上面的表现,基本问题就来了

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

<!-- more -->

I. 问题定位

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

1. 定位过程

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

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

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

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

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

-- 查询进程信息
show processlist

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

然后定位到具体的进程

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

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

lsof -i tcp:52951

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

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

接着查看进程对应的信息

ps aux | grep 5436

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

这个脚本正是测试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如下

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

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的类型,从网上截一张图

Mysql DDL出现长时间等待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...

II. 其他

1. 一灰灰Bloghttps://liuyueyi.github.io/he...

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

2. 声明

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

3. 扫描关注

一灰灰blog

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

知识星球

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

相关推荐