背景

在 DBS-集群列表-更多-连接查询-死锁中，看到 9 月 22 日有数据库死锁日志，后排查发现是因为 mysql 的优化-index merge（索引合并）导致数据库死锁。

定义

index merge(索引合并)：该数据库查询优化的一种技术，在 mysql 5.1 之后进行引入，它可以在多个索引上进行查询，并将结果合并返回。

mysql 数据库的锁机制

在排查问题之前，首先讲一下 mysql 数据库的锁机制：

1 加锁的基本单位是 next-key lock（记录锁+间隙锁），当记录锁或者间隙锁能够解决幻读的问题，就会退化为记录锁（行锁），间隙锁。

2 加锁是将锁加在了索引之上，而不是数据之上。

3 对于当前读，索引进行加锁，当前读语句包括了（select ... from. ... for update,select...from ..... lock in share mode,update...,delete....）。

4 加锁根据唯一性索引、非唯一性索引进行了区分，根据查询条件分为了等值查询、范围查询，根据是否能够查到数据又分为了记录存在和不存在的情况。

本次死锁问题使用的索引是非唯一性索引的等值查询中记录存在的情况，因此本文仅仅详细介绍这种情况，其它情况可以查看最下面的参考文档 1：

加锁情况是：会依次扫描，首先扫描到条件匹配的数据，加一个 next-key lock，然后接下来扫描到第一个记录不匹配的数据，增加一个间隙锁，最后对查到记录的主键增加一个记录锁，

针对以上情况加了三种锁，加锁的目的是为了防止幻读的发生。

针对二级索引的锁进行分析：

表结构：

CREATE TABLE `jdi_roster_apply_detail` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',  `apply_id` varchar(100) NOT NULL COMMENT '申请单号',  `status` tinyint(10) NOT NULL COMMENT '状态',  PRIMARY KEY (`id`),  KEY `idx_status` (`status`),  KEY `idx_apply_id` (`apply_id`)) ENGINE=InnoDB AUTO_INCREMENT=984483 DEFAULT CHARSET=utf8 COMMENT='黑白名单申请单明细'

表数据：

通过 idx_apply_id 建立的 b+树：

因为索引是二级索引，所以叶子节点存储的数据是主键值。

执行 sql:

select * from jdi_roster_apply_detail where apply_id='1695369227576173690' for update

执行数据扫描过程

1 查到符合条件的记录，增加 next-key 锁，因此锁是(1695369220522068998,1695369227576173690]

2 找到第一个不符合记录的数据增加间隙锁，因此锁是 （1695369227576173690，1695373047673903326）

3 对符合条件的主键索引增加记录锁，因此对 id=960738，增加记录锁。

针对三种锁解决的幻读：

1 如果没有第一条的 next-key 锁， 另一个事务增加一个 apply_id=1695369227576173690， id<960738 时，该事务在进行查询时，会多一条记录，因此会造成幻读。

2 如果没有第二条的 间隙锁，另一个事务增加一个 apply_id=1695369227576173690， id>960738 是，该事务在进行查询时，会多一条记录，因此会造成幻读。

3 如果没有第三条的记录锁，另一条事务删除一条 id=960738 的记录，该事务进行查询时，会少一条数据，因此会造成幻读。

实际问题分析

数据库死锁日志

以上日志两个事务分别执行了 update 语句：

#事务1update jdi_roster_apply_detail set `status` = 10 where `status` = 1 and apply_id = '1695369220522068998'#事务2update jdi_roster_apply_detail set `status` = 10 where `status` = 1 and apply_id = '1695369227576173690' 

这个 sql 是用于将某个申请单 id 待审批的数据改为已审批。

因为在泰山里不能执行 update 语句 ，因此执行了 select 语句查看用的索引情况：

explain select * from  jdi_roster_apply_detail  where `status` = 1 and apply_id = '1695369220522068998'

执行的结果：

通过结果可以看出两个 update 语句都使用了两个索引，分别是 idx_status，idx_apply_id，然后将查到的结果进行合并，因此在模拟的过程中，可以将其拆成两个查询语句。

死锁模拟

两个事务分别想要两个主键 id 的记录锁，造成相互等待，形成了死锁。

以上是先执行 idx_apply_id 的索引查询再执行 idx_status 索引查询，如果先执行 idx_status 索引查询，再执行 idx_apply_id 的索引查询，也会因为主键的记录锁造成死锁。

解决方案

1 利用 force index(idx_apply_id)强制走某个索引，这样 InnoDB 就会忽略 index merge，避免多个索引同时加锁的情况。

2 禁用 Index Merge，用命令禁用 Index Merge：SET GLOBAL optimizer_switch='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off';

3 Index Merge 同时使用了 2 个独立索引，因此新建一个包含这两个索引所有字段的联合索引，这样 InnoDB 就只会走这个单独的联合索引。

第三种方案相较于第一种查询性能更好，相对于第二种仅仅作用于该表，影响范围小，因此本次也是采用了该方案。

总结

该死锁问题是因为优化器使用了合并索引问题导致的，最终通过新建一个联合索引来解决这个问题。

参考文档：

1 https://www.xiaolincoding.com/mysql/lock/how_to_lock.html

作者：京东工业 李小辉

来源：京东云开发者社区 转载请注明来源