MySQL 元数据锁及问题排查的解决

"元数据"是用来描述数据对象定义的，而元数据锁（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;

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

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

alter table t1 add name varchar(16);

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

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

show processlist;

二、查看元数据锁

除了表，元数据锁也会加在表空间，存储过程，函数，触发器等对象上。但最常遇到的问题是我们想修改表结构，但是却被元数据锁阻塞了，导致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';

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

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

我们依然用第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';

• 锁状态为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;

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

show procelist;

解决方案：

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

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

到此这篇关于MySQL 元数据锁及问题排查的解决的文章就介绍到这了,更多相关MySQL 元数据锁内容请搜索编程网(www.lsjlt.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.lsjlt.com)！