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

栏目: 数据库 · Mysql · 发布时间: 5年前

内容简介:给表新增字段时,发现锁表了,查看进程,提示看到上面的表现,基本问题就来了<!-- more -->

给表新增字段时,发现锁表了,查看进程,提示 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_SW
MDL_SR

几个简称的说明

insert, update, delete
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或者有更好的建议,欢迎批评指正,不吝感激

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

3. 扫描关注

一灰灰blog

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

知识星球

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


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Programming Amazon Web Services

Programming Amazon Web Services

James Murty / O'Reilly Media / 2008-3-25 / USD 49.99

Building on the success of its storefront and fulfillment services, Amazon now allows businesses to "rent" computing power, data storage and bandwidth on its vast network platform. This book demonstra......一起来看看 《Programming Amazon Web Services》 这本书的介绍吧!

HTML 压缩/解压工具
HTML 压缩/解压工具

在线压缩/解压 HTML 代码

RGB转16进制工具
RGB转16进制工具

RGB HEX 互转工具

随机密码生成器
随机密码生成器

多种字符组合密码