技术干货 | 记一次实遇的 MySQL--index merge 死锁历程

文 | 陈佳瑶

网易云商

背景

某一天中午，我们收到了报警信息，出现关键词 Deadlock。

nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

显然出现了数据库死锁。一开始我们以为是数据库操作顺序不一致引发的，但是经分析相关业务和 SQL 后并未发现不一致的场景，场面一度陷入僵局，没办法只能寻根究底从 MySQL 的索引和加锁机制入手了。

先来看下 MySQL 的加锁机制。

MySQL 的加锁机制

MySQL 索引分为主键索引（或聚簇索引）和二级索引（或非主键索引、非聚簇索引，包括各种主键索引外的其他索引）。不同存储引擎对于数据的组织方式略有不同，本文以 InnoDB 为例展开，MySQL 版本 5.7。

主键索引和数据是存放在一起的，构成一棵 B+ 树，主键位于非叶子节点，数据存放于叶子节点。示意图如下：

二级索引列位于非叶子节点，主键值位于叶子节点，示意图如下：

以 select * from table where name='ccc' 为例，MySQL 对 SQL 进行解析后发现 name 字段有索引可用，于是先在二级索引（图2）上根据 name='ccc' 找到主键 id=27，然后根据主键 27 到主键索引上（图1）上找到需要的记录。这个过程也被称作回表。

接下来言归正传看下 MySQL 到底是如何给索引加锁的（本例中隔离级别为 RC）。具体分以下 3 个场景分析：

• 根据主键进行更新

update table set name='cjy' where id=27；只需要将主键上 id=27 的记录加上互斥锁即可（加锁后本事务可以读和写，其他事务读和写会被阻塞）。如下：

这里有四把锁，加锁步骤如下：

• 在非唯一索引（name）上找到（ccc,27）的索引项，加上互斥锁；

• 根据（ccc,27）找到主键索引的（27,ccc）记录，加互斥锁；

• 在非唯一索引（name）上找到（ccc,29）的索引项，加上互斥锁；

• 根据（ccc,29）找到主键索引的（29,ccc）记录，加互斥锁；

从上面步骤可以看出，InnoDB 对于每个符合条件的记录是分步逐条加锁的。

再来看下 MySQL 的事务机制。

MySQL 事务机制

 事务的四个特性 ACID

原子性（Atomicity）：指事务是一个不可分割的最小工作单位，事务中的操作只有都发生和都不发生两种情况。

一致性（Consistency）：事务必须使数据库从一个一致状态变换到另外一个一致状态，举个例子，李二给王五转账 50 元，其事务就是让李二账户上减去 50 元，王五账户上加上 50 元；一致性是指其他事务看到的情况是要么李二还没有给王五转账的状态，要么王五已经成功接收到李二的 50 元转账。而对于李二少了 50 元，王五还没加上 50 元这个中间状态是不可见的。

隔离性（Isolation）：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性（Durability）：一个事务一旦提交成功，它对数据库中数据的改变将是永久性的，接下来的其他操作或故障不应对其有任何影响。

 事务的隔离等级 

MySQL 中的四种事务隔离级别分别如下：

• read uncommitted（读未提交数据）：允许事务读取未被其他事务提交的变更。（脏读、不可重复读和幻读的问题都会出现）。

• read committed（读已提交数据）：只允许事务读取已经被其他事务提交的变更。（可以避免脏读，但不可重复读和幻读的问题仍然可能出现）。

• repeatable read（可重复读）：确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新（update）。（可以避免脏读和不可重复读，但幻读仍然存在）。

• serializable（串行化）：确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作，所有并发问题都可避免，但性能十分低下。

其中 RR 实现可重复读的原理是：MVC 多版本并发控制。其实就是版本号概念。读取数据时大于当前事务版本号的记录不管。

同时 RR 中会使用到间隙锁。间隙锁是采用在指定记录的前面和后面以及中间的间隙上加间隙锁的方式避免数据被插入，从而解决幻读的问题。

死锁原因分析

基于上面讲过的知识，下面就来看看本次死锁的具体原因是什么。

本次死锁发生的场景是给一批客户手机号发短信后更新记录时触发的。废话不多说，先上表结构：

（基于数据敏感和方便理解考虑，去除了和本次死锁无关的字段）

CREATE TABLE `phone_send_record` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',  `task_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '发送批次id',  `phone` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '手机号',  `status` smallint(4) NOT NULL DEFAULT '0' COMMENT '发送状态 0未发送 1发送成功 2 发送失败',  PRIMARY KEY (`id`),  KEY `idx_taskId` (`task_id`) USING BTREE,  KEY `idx_phone` (`phone`) USING BTREE) ENGINE = InnoDB  DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '短信发送记录表'

业务场景就是：创建一个短信发送任务（task_id），一个任务里包含了一批手机号，因此数据库短信发送记录表中会有 n 行记录：phone 不同，task_id 相同。更新 SQL 如下：

update phone_send_record set status = #{status} where phone = #{phone} and task_id = #{taskId}

心路历程

我们业务上做了手机号去重，因此理论上同一个发送任务里是不会存在相同手机号的，同时发生死锁的2行记录显示的也是不同的手机号（见下文 sql）。

按道理讲，innoDB 触发行锁，没有锁表，不同行不应该影响才对，就算锁多行那也是资源竞争等待不应该触发死锁。我们带着疑问继续往下看。

接着我们考虑了间隙锁的可能，但是间隙锁是在 RR 隔离级别下才存在，查了下我们数据库的隔离级别是 RC，所以间隙锁的可能也被排除了。

接着查看了 SQL 执行计划，发现使用了 index_merge。index_merge 是 MySQL 5.1 后引入的一项索引合并优化技术，它允许对同一个表同时使用多个索引进行查询，并对多个索引的查询结果进行合并后返回。

回到我们的 SQL，经过分析日志找出发生死锁的是如下 2 条:

update phone_send_record set  status = 0 where phone = '13555111111' and task_id = 123;update phone_send_record set  status = 0 where phone = '13555222222' and task_id = 123;

如果没有 index_merge，要么走 idx_taskId 索引，要么走 idx_phone 索引，不会出现两个索引一起使用的情况。而在使用 index_merge 技术后，会同时执行两个索引，分别查到结果后再进行合并。再结合上文对加锁机制的理解，两个索引的同时加锁就可能导致死锁。图析如下：

上图只是其中一种造成死锁的可能路径，事实上还有其他可能（大家可以自行发挥想象）。

下表是对上图做的简要解析：

事务 1 等待事务 2 释放锁，事务 2 等待事务 1 释放锁，这样就造成了死锁。

解决方法

1. 既然是 index_merge 导致的，那理所当然的想就是关闭 index_merge。的确这能解决上述的死锁问题，但是一般情况下，数据量越大 index_merge 开启的优化效果会越明显，所以没有特别要求不建议关闭（默认是开启的）。因此此方法不推荐。

2. 使用主键索引进行更新。先根据二级索引查询出主键 id，再拿主键 id 进行 update。这样更新的范围是唯一的，自然不会触发死锁。

3. 删除多余的独立索引，创建联合索引。本栗就是 KEY idx_t_p （task_id，phone）。这个就提醒我们在加索引字段时（尤其是往旧表加）需要仔细分析下业务场景，来决定是创建独立索引还是联合索引。

小结：方法 2 是从代码层面入手从根本上规避死锁，但是会增加一定的代码量；方法 3 是巧用联合索引实现 2 个独立索引的功能又不会触发 index_merge。推荐方法 2，但方法 3 更方便些，原则上方法 2 和 3 都有效。

总结

本文仅描述了由于 index_merge 优化导致的死锁，讲述了死锁产生的原因以及解决办法，并顺便介绍了 MySQL 加锁机制。事实上死锁的原因还有很多，比如操作数据顺序不一致、长事务等，就不一一展开了。

 推荐好友活动