mysql创建索引导致死锁,数据库崩溃,完美解决方案
创始人
2024-06-01 00:49:31
0

文章目录

  • 写在前面
  • 一、短事务场景下,执行DDL语句场景分析
    • 1、短事务场景下,执行表字段添加操作
    • 2、短事务场景下,执行表字段修改操作
    • 3、短事务场景下,执行表字段删除操作
      • (1)往里添加一条数据试试
    • 4、短事务场景下,添加索引操作
    • 5、总结
  • 二、完美解决方案
  • 三、原因分析
  • 写在后面

写在前面

DDL语句,就是对数据库对象(数据库、表、列、索引等)进行创建、删除、修改等。

之前分享过一篇mysql创建索引导致死锁,数据库崩溃,mysql的表级锁之【元数据锁(meta data lock,MDL)】全解

通过上一篇文章我们了解到,MySQL有一种表锁叫做元数据锁(meta data lock,MDL)元数据锁,执行DDL时会检查元数据锁并尝试获取。

之前一直以为,只要保证MySQL数据库当前没有长事务,就可以安枕无忧地执行DDL语句,我们天真的认为短事务场景中,DDL语句总是会在上一个事务结束后,获取到元数据锁,并不会有死锁的危险。

但是最近发现!事情并没有想象中的那么简单!就算是没有长事务,MySQL8.0在创建索引的时候,仍然有可能会导致死锁的发生!
我们一起来分析一下。

注!本文操作都是基于mysql 8.0.21InnoDB引擎,可重复读事务隔离级别下来完成的。

一、短事务场景下,执行DDL语句场景分析

先创建一个表:

CREATE TABLE `lock_test` (`id` int NOT NULL,`name` varchar(20) DEFAULT NULL,`age` int DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB;

1、短事务场景下,执行表字段添加操作

在这里插入图片描述
我们发现,当事务A尚未提交时,事务B、事务C都会处于等待状态。

当事务A一经发起commit命令,事务B紧跟着会进行执行,并执行成功,事务C在事务B之后也会执行成功。

整个过程只有正常的事务等待,并不会发生死锁。

2、短事务场景下,执行表字段修改操作

在这里插入图片描述
我们发现,修改字段的场景下和添加字段场景下的结果是一样的。

3、短事务场景下,执行表字段删除操作

在这里插入图片描述
当该表无数据时,我们会发现,当事务A执行提交之后,事务B和事务C进入死锁,此时该表的任何SQL语句都无法执行!

此时导致数据库表死锁,数据库SQL堆积越来越多,导致数据库崩溃!

(1)往里添加一条数据试试

INSERT INTO `ourea`.`lock_test`(`id`, `name`, `age`, `column_name`) VALUES (1, '1', 1, '1');

在这里插入图片描述
有数据的情况和没有数据的情况,是不一样的!

有数据时,当事务A提交之后,事务C会执行成功,当事务C提交之后,事务B才是最终执行成功。

4、短事务场景下,添加索引操作

还是保持数据库中有数据。

INSERT INTO `ourea`.`lock_test`(`id`, `name`, `age`, `column_name`) VALUES (1, '1', 1, '1');

在这里插入图片描述
我们发现,当事务A执行提交之后,事务B和事务C进入死锁,此时该表的任何SQL语句都无法执行!

此时导致数据库表死锁,数据库SQL堆积越来越多,导致数据库崩溃!

5、总结

用事实说话。

通过实例,我们可以看出,即使没有长事务,执行DDL语句仍会导致表死锁。
尤其是对索引的操作,非常危险!

二、完美解决方案

在执行DDL语句之前,通过以下SQL,首先要确保没有长事务:

SELECT * FROM information_schema.INNODB_TRX;

然后,再执行DDL语句之前,先将整表锁住,然后执行DDL语句:
在这里插入图片描述
使用如下操作,完美解决死锁问题!

-- 锁整表,加上写锁
lock table lock_test write;
-- 添加索引
CREATE INDEX index_tb ON lock_test(column_name);
-- 解锁
unlock table;

三、原因分析

当对非主键字段更改索引时,其实并不是一个原子操作,会先更新非主键字段索引,然后再更新主键索引。

当我们把主键删掉之后:

在这里插入图片描述
这种情况,和当表中有数据,执行表字段删除操作的场景一模一样。

原因是MySQL5.6引入的OnLine-DDL,一个DDL语句其实包含着两个等待操作:

  • prepare阶段:尝试获取MDL排他锁,禁止其他线程读写;
  • ddl执行阶段:降级成MDL共享锁,允许其他线程读取;
  • commit阶段:升级成MDL排他锁,禁止其他线程读写;
  • finish阶段:释放MDL锁;
    1、3、4如果没有锁冲突,执行时间非常短。第2步占用了DDL绝大部分时间,这期间这个表可以正常读写数据,是因此称为“online ”。
    如果第3步升级为MDL写锁的时候,这个表的MDL锁有其他事务占着,那么这个事务会阻塞,等到可以拿到MDL写锁,而且如果不幸一直拿不到,最后锁超时了,就只好回滚这个DDL操作。
    所以,DDL语句只有才开始和结束的时候,才会禁止读和写,在语句执行的时候是可以进行读的。

写在后面

如果本文对你有帮助,请点赞收藏关注一下吧 ~
在这里插入图片描述

相关内容

热门资讯

中国铁建全资子公司新增一项62... (转自:快查一企业中标了)快查APP显示,中国铁建相关公司中国铁建大桥工程局集团有限公司于2025年...
巴西总统抨击特朗普关税威胁 转自:财联社【巴西总统抨击特朗普关税威胁】财联社7月8日电,据CCTV国际时讯报道,巴西总统卢拉当地...
北京大北农科技集团股份有限公司...   证券代码:002385证券简称:大北农               公告编号:2025-060 ...
孕36周遭遇心血管“旋风炸弹”... 转自:天津日报  怀孕36周的准妈妈王女士遭遇有心血管“旋风炸弹”之称的主动脉夹层,天津医科大学第二...
12月4日小胖闯非洲在尼泊尔出... 12月4日小胖闯非洲在尼泊尔出什么事了小胖在尼泊尔直播的时候被杀身亡了12月4日,小胖闯非洲在尼泊尔...
牛为什么要吃草?要求科学解释? 牛为什么要吃草?要求科学解释?提示:为什么牛不搞光合作用,为什么牛不吃肉(自然为什么这么选择)?为什...
现在有什么好看的玄幻小说,要好... 现在有什么好看的玄幻小说,要好看的?类似斗破苍穹。神墓的!盘龙 我看过 挺好的
重庆燃气相关公司新增一项331... (转自:快查一企业中标了)快查APP显示,重庆燃气相关公司重庆渝西天然气管道有限公司于2025年7月...
小亭子连接职场“大舞台” 转自:光明日报  临近暑假,东北师范大学学生就业指导服务中心大厅里,两座AI面试亭依然“座无虚席”,...
从“被动养老”到“主动健康” 转自:光明日报  日前,在位于福建省厦门市嘉莲街道友邻中心的智慧健身房里,70岁的居民张凯明正对着镜...