navicat下对mysql创建索引导致死锁，数据库崩溃，完美解决方案

写在前面

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

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

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

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

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

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

非navicat环境下，结果或许不同！

一、短事务场景下，执行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 `lock_test`(`id`, `name`, `age`, `column_name`) VALUES (1, '1', 1, '1');

有数据的情况和没有数据的情况，是不一样的！

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

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

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

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

我们发现，当事务A执行提交之后，事务B和事务C进入死锁，此时该表的任何SQL语句都无法执行！

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

5、总结

用事实说话。

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

二、完美解决方案

1、执行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;

2、执行DDL之前设置超时时间

在 alter table 时，设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

-- 单位 秒,只在当前会话中生效
set lock_wait_timeout=10
-- 添加索引
CREATE INDEX index_tb ON lock_test(column_name);

3、（重要）使用命令行

使用mysql命令行的方式，不用navicat，不会导致死锁！！

三、原因分析

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

当我们把主键删掉之后：

这种情况，和当表中有数据，执行表字段删除操作的场景一模一样。

原因是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语句只有才开始和结束的时候，才会禁止读和写，在语句执行的时候是可以进行读的。

1、验证OnLine-DDL

我们发现，事务C，换成Update语句，并不会导致死锁。

因为OnLine-DDL 是 写锁-读锁-写锁，有个锁降级升级的过程。
OnLine-DDL对select语句来说，不是原子操作，分了两步（写锁时相对于select加锁，读锁时相对于select解锁，写锁时又对select加锁）。
对update语句来说，是原子操作，相当于全程加了锁。

写在后面

如果本文对你有帮助，请点赞收藏关注一下吧 ~