吓尿，给小表加个字段，把数据库搞挂了

一天下午，在给线上一个小表加个字段，发现老是加不上去，一直卡死。运维同学突然跑过来跟我说，线上数据库这半个小时一直在重启，问我是否有做什么操作。我当时虎躯一震，总共100多行的小表加个字段都加出问题了？我立马停止尝试加字段，果然数据库恢复正常了。后面查到原因，也顺利加上字段，现在来复盘总结一下。

先讲下原因，表数据量虽然小，却是一个热点表，访问频率特别高，而且该表的访问是在一个大事务中。加字段的时候一直在等待获取MDL写锁。这个等待也影响了后续表访问对MDL读锁的获取，导致后面的查询也都被堵塞了。更惨的是，客户端有重试机制，查询堵塞超过超时时间会再起一个session进行请求，导致数据库的线程池很快就爆满了，直接挂掉。

什么是MDL锁

MDL锁属于表级别的元数据锁。表级别锁分为数据锁和元数据锁，通常我们说的加锁一般指的是加的数据锁。跟数据锁一样，元数据锁也分读锁和读写锁。

MDL不需要显示使用，在进行表操作时会自动加上。当对表进行增删改查时，会自动加上MDL读锁；当要对表进行加减字段的结构修改时，会自动加上MDL写锁。

  • 读锁不互斥，意味着可以多个线程同时对一张表进行增删改查的操作。

  • 写锁独占，进行结构修改前，要先等待其他所有的MDL锁释放了才能获取到MDL写锁。获取到写锁后，在写锁释放前，其他线程无法获取到MDL读锁和写锁。也就是说，修改一个表的结构过程中，会阻塞其他线程对表的操作。

MDL锁的必要性

MDL锁的存在，其实是为了保证数据的一致性。想象一下，假如没有MDL锁，一个查询在遍历表数据的过程中，另外一个线程执行了ALTER TABLE t DELETE COLUMN 'col_1'把col_1这一列删掉了，那查询结果就乱了，结果中是否应该有这一列数据？

事故复现

介绍完MDL锁，我们再来复现下事故。我们通过下面的操作序列来模拟线上情况。

时刻1，事务1对表t_mdl_test进行查询，注意此时事务1并未提交，所以获取的MDL读锁也不会释放。时刻2另外一个线程想要添加字段c, 由于事务1正持着MDL读锁，所以事务2会陷入阻塞，等待事务1释放读锁后获取MDL写锁。

申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。
所以事务2不仅阻塞了加字段的操作，也会阻塞后续对该表的所有操作。比如后面的事务3和事务4查询由于获取不到MDL读锁都被阻塞了。

这时，如果客户端有重试机制，查询超时后会重新进行请求，容易把数据库的连接池给挤爆了。

表t_mdl_test建表：

CREATE TABLE `t_mdl_test` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
 `a` varchar(64) NOT NULL,
 `b` varchar(64) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

读者可关注公众号【会玩code】在获取的写库huiwan_write_x中自行实验。

解决办法

了解了原因，事情就比较好处理了，数据库奔溃原因是由于加字段等待时间太长导致影响后续请求，但mysql又无法在 alter table 语句里面设定等待时间.

所以当时做法是继续尝试加字段语句，语句卡住30秒就手动cancel掉。避免对后续请求的影响。重试了几次发现一直没能加上。。。，最后是通过查看接口调用监控，在请求频率较低的时间点给加上了。

反思

  1. 避免写大事务，如果不是查询所在的事务太大，也不会导致后面语句获取不到MDL写锁。
  2. 事务中，尽量减少加锁时间。还是这次这个例子，从t_mdl_test中获取的数据在事务最后一步更新其他表的时候才会用到，所以可以把t_mdl_test的查询放在事务的尾部。减少t_mdl_test加锁时间。
  3. 对表结构修改的语句注意执行时间，长时间卡住需要注意先取消掉，避免影响其他线程对表的增删改查操作。

留个小问题

在查阅资料的时候，发现另外一个情况。

这种情况事务2会阻塞吗？大家可以在自己的huiwan_write_x库中自行实验。原因我会在公众号文章下留言公布，欢迎大家参与讨论～。

写在最后

喜欢本文的朋友，欢迎关注公众号「会玩code」，专注大白话分享实用技术

公众号福利

回复【mysql】获取免费测试数据库！！

回复【pdf】获取持续更新海量学习资料！！

大白话mysql

相关

标签