0%

MySQL - MetaData Lock

“Waiting for table metadata lock”,你有没有遇到过等待这个锁?MetaData Lock是个啥锁?

本文中描述的相关知识点,都是基于InnoDB引擎下。

本文中涉及到的MySQL例子中,左侧MySQL连接为事务A,右侧MySQL连接为事务B。

名词解释:

  • DML:Data Manipulation Language,数据操控语言。用于操作数据库对象中包含的数据,比如Insert、Delete、Update。

  • DDL:Data Definition Language,数据定义语言。用于操作对象和对象的属性,比如create、drop、alter。DDL不会对具体的数据进行操作。

  • MDL:MetaData Lock。

什么是MDL

MDL,全称MetaData Lock,是在MySQL 5.5.3引入的,出现的初衷是为了保护一个处于事务中的表结构不被修改,这里的事务包括显式事务和AC-NL-RO(auto-commit non-locking read-only)事务,其中:

  • 显式事务
    • 关闭AutoCommit下的操作
    • 以begain或start transaction开始的操作
  • AC-NL-RO
    • AutoCommit开启下的select操作

例如:

  • RR隔离模式下,会话A开启事务,然后执行SQL查询,查询得到2条记录;
  • 会话B使用alter修改表结构,增加一列;
  • 会话A使用同样的SQL查询,此时会发现得到0条数据,与可重复读相冲突。

有了MDL的出现,会导致步骤二的时候被阻塞,直到会话A完成事务。

MDL类型

锁一般划分为读锁(S,共享锁)和写锁(X,排它锁),为了进一步提高并发性,还会加入意向共享锁和意向排它锁。MySQL在MDL上,为了进一步提高并发,设计得比较复杂。

  • 按照锁住的对象划分
属性 含义 范围/对象 对应的等待信息
GLOBAL 全局锁 范围 Waiting for global read lock
COMMIT 提交保护锁 范围 Waiting for commit lock
SCHEMA 库锁 对象 Waiting for schema metadata lock
TABLE 表锁 对象 Waiting for table metadata lock
FUNCTION 函数锁 对象 Waiting for stored function metadata lock
PROCEDURE 存储过程锁 对象 Waiting for stored procedure metadata lock
TRIGGER 触发器锁 对象 Waiting for trigger metadata lock
EVENT 事件锁 对象 Waiting for event metadata lock
  • 按照操作的对象划分
锁名称 说明 适用语句
MDL_INTENTION_EXCLUSIVE(IX) 意向锁 任何语句都会获取MDL意向锁,然后再获取更强级别的MDL锁。
MDL_SHARED(S) 共享锁,表示只访问表结构,不访问数据
MDL_SHARED_HIGH_PRIO(SH) 共享锁,用于访问information_scheam show create table等只访问INFORMATION_SCHEMA的语句
MDL_SHARED_READ(SR) 访问表结构并且读表数据 select语句
LOCK TABLE ... READ
MDL_SHARED_WRITE(SW) 访问表结构并且写表数据 SELECT ... FOR UPDATE
DML语句
MDL_SHARED_UPGRADABLE(SU) 是mysql5.6引入的新的metadata lock,为了online ddl才引入的。特点是允许DML,防止DDL; alter语句中间过程会使用
MDL_SHARED_NO_WRITE(SNW) 可升级锁,访问表结构并且读写表数据,并且禁止其它事务写。 alter语句中间过程会使用
MDL_SHARED_NO_READ_WRITE(SNRW) 可升级锁,访问表结构并且读写表数据,并且禁止其它事务读写。 LOCK TABLES ... WRITE
MDL_EXCLUSIVE(X) 独占锁,禁止其它事务读写。 CREATE/DROP/RENAME TABLE等DDL语句。

其中,锁之间的互斥关系如下(Y为不互斥,N为互斥):

S SH SR SW SU SNW SNRW X
S Y Y Y Y Y Y Y N
SH Y Y Y Y Y Y Y N
SR Y Y Y Y Y Y N N
SW Y Y Y Y Y N N N
SU Y Y Y Y N N N N
SNW Y Y Y N N N N N
SNRW Y Y N N N N N N
X N N N N N N N N

上面的锁类型比较复杂,针对日常使用,我们分析几种典型语句的加/释放锁流程:

  • select语句操作MDL锁流程
    • opening tables阶段,加共享锁
      • 加对象级别的MDL_SHARED_READ锁
    • 事务提交阶段,释放MDL锁
      • 释放MDL_SHARED_READ锁
  • DML语句操作MDL锁流程

    • opening tables阶段,加共享锁
      • 加global类型的MDL_INTENTION_EXCLUSIVE锁
      • 加对象级别MDL_SHARED_WRITE锁
    • 事务提交阶段,释放MDL锁
      • 释放MDL_INTENTION_EXCLUSIVE锁
      • 释放MDL_SHARED_WRITE锁
  • alter操作MDL锁流程

    • opening tables阶段,加共享锁
      • 加global类型的MDL_INTENTION_EXCLUSIVE锁
      • 加对象级别的MDL_SHARED_UPGRADABLE锁,升级到MDL_SHARED_NO_WRITE锁
    • 操作数据,copy data,流程如下:
      • 创建临时表tmp,重定义tmp为修改后的表结构
      • 从原表读取数据插入到tmp表
    • rename阶段,将MDL_SHARED_NO_WRITE读锁升级到MDL_EXCLUSIVE锁
      • 删除原表,将tmp重命名为原表名
    • 事务提交阶段,释放MDL锁
      • 释放MDL_INTENTION_EXCLUSIVE锁
      • 释放MDL_EXCLUSIVE锁

如上几种典型的语句加锁流程,引出以下几种常见的场景:

  • select与alter是否会冲突

    首先,select语句,全程加的都是S锁,alter语句,在第三步的rename阶段,才会加X锁,因此只要select的执行过程中,不与alter的rename阶段冲突,则不会产生事务执行的冲突,否则会产生锁等待。

    开篇中的问题:有了MDL的出现,会导致步骤二的时候被阻塞,直到会话A完成事务。,是因为开启事务,执行select,事务提交之前执行alter的话,会阻塞在rename阶段,当事务提交之后,则alter语句才能继续执行。

  • DML与alter是否会冲突

    DML会在opening tables阶段加MDL_SHARED_WRITE锁,而alter在opening tables阶段,会将锁升级到MDL_SHARED_NO_WRITE锁,而这个锁跟前者的MDL_SHARED_WRITE锁是冲突的,因此DML跟alter会在opening tables阶段冲突。

  • select与DML是否会冲突

    select上的MDL_SHARED_READ锁与MDL上的MDL_SHARED_WRITE锁并不冲突,因此二者不会冲突。

出现MDL的常见原因

  • 长事物运行,阻塞DDL,继而阻塞所有同表的后续操作

    长事务的执行,会导致一直持有MDL_SHARED_READ,会阻塞DDL的执行。这种情况应该优化业务,避免长事务的出现。

  • 未提交事物,阻塞DDL,继而阻塞所有同表的后续操作

    常见的场景为,在autocommit=0的配置下,某个事务没有显示的执行commit/rollback,导致事务一直未提交,从而导致DDL获取不到独占锁。可以通过select * from information_schema.innodb_trx\G, 找到未提交事物的sid, 然后 kill 掉,让其回滚。

MDL会等待lock_wait_timeout设置好超时时间,此参数默认值是31536000,即是365天,建议在生产环境修改成较短的时间。不过即使这样,在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。

参考



-=全文完=-