MDL锁相关

背景

最近遇到一个问题，需要在user用户表（数据量为千万级别）中添加两个字段，发现老是加不上去，一直卡死。这个表不仅数据量巨大，而且是一个热点表，访问频率特别高。此外，该表的访问是在一个大事务中进行的。在添加字段时，一直在等待获取MDL（元数据锁）写锁。这个等待也影响了后续表访问对MDL读锁的获取，导致后面的查询也都被堵塞了。更糟糕的是，客户端有重试机制，当查询堵塞超过超时时间后会再起一个session进行请求，导致数据库的线程池很快就爆满了，直接导致服务宕机。

MDL锁

MDL锁介绍

MDL锁（metadata lock，元数据锁）属于表级别的锁，分为数据锁和元数据锁。通常我们说的加锁一般指的是加的数据锁。跟数据锁一样，元数据锁也分读锁和写锁。MDL锁不需要显示使用，会在进行表操作时自动加上。

• 当对表数据进行增删改查（如insert、delete、select、update等）时，会自动加上MDL读锁。
• 当要对表进行加减字段的结构修改时，会自动加上MDL写锁。

读锁之间不互斥，意味着可以多个线程同时对一张表进行增删改查（CRUD）的操作。写锁是独占的，获取到写锁后，在写锁释放前，其他线程无法获取到MDL读锁和写锁。也就是说，修改一个表的结构过程中，会阻塞其他线程对表的操作。MDL锁是MySQL自动隐式加锁，无需手动操作。

DML与DDL

• DML（Data Manipulation Language）数据操纵语言：适用于对表数据进行操作，如insert、delete、select、update等。
• DDL（Data Definition Language）数据定义语言：适用于对表结构进行操作，如create、drop、alter、rename、truncate等。

MDL锁的必要性

MDL锁的存在是为了保证数据的一致性。如果没有MDL锁，在查询过程中可能会因为其他线程修改表结构而导致查询结果混乱。因此，为了保证并发操作下数据的一致性，MDL锁是必要的。如果一个事务正在执行中，另一个事务这时修改了表结构，不但可能导致当前事务出现不可重复读的问题，还有可能使事务都无法提交。

事故复现

通过以下操作序列模拟线上情况：

• Session 1: begin; select * from user limit 10;
• Session 2: alter table user add ‘age’ int not null default ‘0’ comment ‘年龄’;(阻塞)
• Session 3: select * from user limit 10;

时刻1，事务1对表user进行查询，此时事务1并未提交，所以获取的MDL读锁也不会释放。时刻2，另外一个线程想要添加字段。由于事务1正持着MDL读锁，所以事务2会陷入阻塞，等待事务1释放读锁后获取MDL写锁。申请MDL锁的操作会形成一个队列，队列中写锁的获取优先级高于读锁。因此，事务2不仅阻塞了加字段的操作，也会阻塞后续对该表的所有操作。客户端的重试机制会导致数据库连接池被挤爆。

MySQL服务宕机的原因

原因是在执行查询语句时，MySQL自动加了MDL锁。可以通过执行show processlist命令查看正在执行的进程，发现Session2和Session3的语句正在等待MDL锁。

解决方案

方案一

了解到原因后，可以在尝试加字段语句卡住30秒后就手动取消，避免对后续请求的影响。选择在请求频率较低的时间点进行加字段操作。

方案二

从MySQL5.6版本开始引入了Online DDL功能，允许在执行DDL的时候并发执行DML。从MySQL8.0版本开始又优化了Online DDL，支持快速添加列，可以实现给大表秒级加字段。在使用DDL语句时，可以指定ALGORITHM和LOCK参数来调整执行方式和锁定策略。ALGORITHM可以指定执行DDL的算法，LOCK可以指定执行过程中的锁定状态。

其他

关注INNODB_TRX, INNODB_LOCKS, 以及INNODB_LOCK_WAITS三张表，记录正在运行的事务以及锁定信息。

总结

以上内容是基于个人经验进行的总结，希望能给大伙提供参考