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

database

一天下午,在给线上一个小表加个字段,发现老是加不上去,一直卡死。运维同学突然跑过来跟我说,线上数据库这半个小时一直在重启,问我是否有做什么操作。我当时虎躯一震,总共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】获取持续更新海量学习资料!!

以上是 吓尿,给小表加个字段,把数据库搞挂了 的全部内容, 来源链接: utcz.com/z/535644.html

回到顶部