【网易云商】记一次实遇的 MySQL--index merge 死锁历程

网易数智

于 2023-03-28 11:54:11 发布

阅读量263

点赞数

文章标签：数据库开发语言 mysql

本文链接：https://blog.csdn.net/netease_im/article/details/129809837

版权

在实际业务开发过程中，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+ 树，主键位于非叶子节点，数据存放于叶子节点。示意图如下：

图1：图源网络

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

图2：图源网络

以 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 的记录加上互斥锁即可（加锁后本事务可以读和写，其他事务读和写会被阻塞）。如下：

根据唯一索引进行更新

update table set name='cjy' where name='ccc'，InnoDB 现在唯一索引 name 上找到 name='ccc' 的索引项（ccc,27）并加上加上互斥锁，然后根据 id=27 再到主键索引上找到对应的叶子节点并加上互斥锁。

一共两把锁，一把加在唯一索引上，一把加在主键索引上。这里需要说明的是加锁是一步步加的，这种分步加锁的机制就是后文死锁的导火索。示意图如下：

根据非唯一索引进行更新

update table set name='cjy' where name='ccc'。如果 name 不唯一，和上面唯一索引加锁相似，不同的是会给所有符合条件的索引加锁。如下：

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

在非唯一索引（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 技术后，会同时执行两个索引，分别查到结果后再进行合并。再结合上文对加锁机制的理解，两个索引的同时加锁就可能导致死锁。图析如下：