MySQL 元数据锁及问题排查（Metadata Locks MDL）

"元数据"是用来描述数据对象定义的，而元数据锁（Metadata Lock MDL）即是加在这些定义上。通常我们认为非锁定一致性读（简单select）是不加锁的，这个是基于表内数据层面，其依然会对表的元数据加锁，保证读取数据期间表结构不会变更。

一、元数据锁简介

在事务执行过程中，MySQL会对所有涉及对象的定义加上元数据锁（语句执行的时候加锁），目的是保证事务执行过程中对象定义不被修改（你不能在别人查询的时候修改表结构或者把表删了）。

对表进行DML操作时（select, update等），MySQL会对表的定义施加一个共享元数据锁（S MDL），而进行DDL操作时，会施加排他元数据锁（X MDL）。DML之间的元数据锁时不会互相阻塞的，而普通用户通常只会执行DML，他们是感知不到元数据锁的。

如果DBA在业务运行期间执行了DDL，那么DDL也会尝试获取元数据锁，在事务都很短小的时候，可能很快就获取到了。但如果有长事务阻塞了DDL，那么就有可能导致严重的问题。

示例：在会话1中执行下面SQL:

create table t1 (id int primary key auto_increment);
begin;
select * from t1;
1
2
3

• MySQL对DML默认是自动提交的，因此每条DML语句都是独立事务，当语句执行完，元数据锁就释放了，这里通过begin显式开启事务，让select语句执行完后，事务依然存在。

另启动一个会话2，执行下面DDL语句，可以发现其被阻塞（会话迟迟不返回）：

alter table t1 add name varchar(16);
1

• DDL在执行前会隐式提交事务并释放元数据锁，这就是为什么要另一个会话发起DDL。

启动会话3，执行show processlist;命令，即可看到会话2在等待元数据锁（Waiting for table metadata lock）：

show processlist;
1

二、查看元数据锁

除了表，元数据锁也会加在表空间，存储过程，函数，触发器等对象上。但最常遇到的问题是我们想修改表结构，但是却被元数据锁阻塞了，导致DDL无法执行，进一步导致后续DML无法执行（业务停滞），此时需要进行人工干预。

2.1 查询元数据锁

MySQL提供了performance_schema.metadata_locks用来查询具体元数据锁信息，且默认就打开了元数据锁的信息收集，直接查询即可。表中包含了持有，等待及其他中间状态的MDL数据，当锁释放时，会从表中删除。

如果没有打开元数据锁信息收集，可以执行下面的SQL：

update performance_schema.setup_instruments
set enabled = 'YES', timed = 'YES'
where name = 'wait/lock/metadata/sql/mdl';
1
2
3

也可以持久化写入配置文件（需要重启）：

[mysqld]
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
1
2

我们依然用第1章的示例，在执行完会话1的SQL后，另开一个会话执行下面SQL：

select 
l.object_schema 数据库名,
l.object_type 对象类型, 
l.object_name 对象名称,
l.lock_type 锁类型,
l.lock_duration 持续类型,
l.lock_status 锁状态,
l.owner_thread_id 线程ID,
t.processlist_id 会话ID,
s.sql_text
from performance_schema.metadata_locks l
join performance_schema.threads t on t.thread_id=l.owner_thread_id
join performance_schema.events_statements_current s on s.thread_id=l.owner_thread_id
where l.object_schema='test'and l.object_name='t1';
1
2
3
4
5
6
7
8
9
10
11
12
13
14

• 锁状态为granted，代表成功获取了元数据锁

随后执行会话2的DDL，再次执行查询SQL，可以看到锁状态pending（等待中）：

• 通过会话ID，锁状态和SQL_Text三个字段，可以判断会话ID为4107的select语句阻塞了alter table

2.2 常见问题

元数据锁的获取是有优先级的，X锁的优先级要高于S锁。在实际生产环境中，如果一个长事务阻塞了DDL，由于其尝试获取的是X锁（优先级高），那么它还会阻止后续DML获取S锁。即：DML => DDL阻塞 =>DML阻塞，从现象上看就是表无法执行任何操作。

在上面示例的基础上，再重新开几个会话执行下面的SQL，你会发现所有类型DML都无法返回（甚至无法读）：

insert into t1 values(1,'Vincent');
update t1 set name='Victor' where id=1;
delete from t1 where id=1;
select * from t1;
1
2
3
4

如果生产环境出现了DDL阻塞，你的processlist可能就是下面的样子，堆积的DML会越来越多，最后挤爆线程：

show procelist;
1

解决方案：

• 尽量避免在业务活跃期间执行DDL，特别是有长事务的时候
• 如果已经产生了阻塞，立刻取消DDL或将其会话kill掉，先让业务运行下去

注：Online DDL在运行过程中也会短暂地获取X锁，所以并不能解决DDL阻塞问题。