“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锁
- opening tables阶段,加共享锁
DML语句操作MDL锁流程
- opening tables阶段,加共享锁
- 加global类型的MDL_INTENTION_EXCLUSIVE锁
- 加对象级别MDL_SHARED_WRITE锁
- 事务提交阶段,释放MDL锁
- 释放MDL_INTENTION_EXCLUSIVE锁
- 释放MDL_SHARED_WRITE锁
- opening tables阶段,加共享锁
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锁
- opening tables阶段,加共享锁
如上几种典型的语句加锁流程,引出以下几种常见的场景:
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天,建议在生产环境修改成较短的时间。不过即使这样,在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。