MySQL的index merge(索引合并)导致数据库死锁分析与解决方案 | 京东云技术团队
背景
在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='黑白名单申请单明细'
表数据:
| id | apply_id | status |
|---|---|---|
| 959651 | 1695369220522068998 | 1 |
| 960738 | 1695369227576173690 | 1 |
| 961319 | 1695373047673903326 | 1 |
| 961365 | 1695373122447865228 | 1 |
通过 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语句:
#事务1
update jdi_roster_apply_detail set `status` = 10 where `status` = 1 and apply_id = '1695369220522068998'
#事务2
update 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,然后将查到的结果进行合并,因此在模拟的过程中,可以将其拆成两个查询语句。
死锁模拟
| 事务1 | 事务2 | 锁的范围 |
|---|---|---|
| begin | begin | |
| select * from jdi_roster_apply_detail where apply_id = ‘1695369220522068998’ for update | idx_apply_id所以锁住了(-∞,1695369220522068998],(1695369220522068998,1695369227576173690) 主键id索引锁住了 id=959651 | |
| select * from jdi_roster_apply_detail where apply_id = ‘1695369227576173690’ for update | idx_apply_id所以锁住了(1695369220522068998,1695369227576173690],(1695369227576173690,1695373047673903326) 主键id索引锁住了 id=960738 | |
| select * from jdi_roster_apply_detail where status = 1 for update | 会对idx_status上加next-key锁和间隙锁,但是在对主键959651,960738,961319,961365进行加记录锁时,其中事务2 对960738已经加了记录锁,所以该事务1进行了阻塞。 | |
| select * from jdi_roster_apply_detail where status = 1 for update | 会对idx_status上加next-key锁和间隙锁,但是在对主键959651,960738,961319,961365进行加记录锁时,其中事务1对959651已经加了记录锁,所以该事务2进行了阻塞。 | |
| deadlock |
两个事务分别想要两个主键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
作者:京东工业 李小辉
来源:京东云开发者社区 转载请注明来源
相关文章:
MySQL的index merge(索引合并)导致数据库死锁分析与解决方案 | 京东云技术团队
背景 在DBS-集群列表-更多-连接查询-死锁中,看到9月22日有数据库死锁日志,后排查发现是因为mysql的优化-index merge(索引合并)导致数据库死锁。 定义 index merge(索引合并):该数据库查询优化的一种技术࿰…...
第四章 网络层 | 计算机网络(谢希仁 第八版)
文章目录 第四章 网络层4.1 网络层提供的两种服务4.2 网际协议IP4.2.1 虚拟互连网络4.2.2 分类的IP地址4.2.3 IP地址与硬件地址4.2.4 地址解析协议ARP4.2.5 IP数据报的格式4.2.6 IP层转发分组的流程 4.3 划分子网和构造超网4.3.1 划分子网4.3.2 使用子网时分组的转发4.3.3 无分…...
课题学习(八)----卡尔曼滤波动态求解倾角、方位角
一、 卡尔曼滤波 卡尔曼滤波的应用要求系统和底层过程的测量模型都是线性的。离散时间线性状态空间系统的描述为: x k Φ k , k − 1 x k − 1 G k − 1 w k − 1 x_k\Phi_{k,k-1}x_{k-1}G_{k-1}w_{k-1} xkΦk,k−1xk−1Gk−1wk−1 式中 Φ k , k − 1 \Phi_{…...
仿真软件Proteus8.9 SP2 Pro 下载、安装、汉化详细图文教程
Proteus8.9 安装教程 视频教程一、安装软件解压二、软件安装常见问题及解决方法:三、汉化 Proteus8.9 SP2 Pro 安装教程 本破解教程仅供个人及 proteus 8.9粉丝们交流学习之用,请勿用于商业用途, 谢谢支持。此版本为Proteus8.9 SP2 Pro。其他…...
振弦传感器和无线振弦采集仪在隧道安全监测的解决方案
振弦传感器和无线振弦采集仪在隧道安全监测的解决方案 隧道作为交通工程的重要组成部分,具有极高的安全风险,因此隧道安全监测是必不可少的。振弦传感器和无线振弦采集仪作为隧道安全监测的两种重要设备,能够有效地监测隧道的振动情况&#…...
c# xml 参数读取的复杂使用
完整使用2 生产厂家里面包含很多规格型号,一个规格型号里面包含很多出厂序列号,点击下一步如果检测到填充的和保存的不一样 就新增一条(如检测到生产厂家相同,但是规格型号不同,就新增一组规格型号)。 界面一:新增界面 界面2 删除界面 界面一:新增界面 load 其中…...
在Mac中使用 brew services start redis 命令启动、停止Redis服务报错
一、问题现象 启动Redis服务命令: brew services start redis异常信息如下: Error: uninitialized constant Homebrew::Service::System /opt/homebrew/Library/Homebrew/macos_version.rb:150:in const_missing /opt/homebrew/Library/Taps/homebrew…...
iapp源码-----比较经典
2.0底部菜单导航栏.rar: https://url18.ctfile.com/f/7715018-958700751-6096bd?p6511 (访问密码: 6511) 2.0涟漪_拖动条控制音乐播放.rar: https://url18.ctfile.com/f/7715018-958700754-4cec13?p6511 (访问密码: 6511) 2.0手电筒.rar: https://url18.ctfile.com/f/7715018…...
为什么手机会莫名多出许多软件?
许多手机用户都曾遭遇过这样的问题,他们在使用手机的过程中,突然发现手机屏幕上出现了一些未知的软件。这些软件并非他们主动下载的,但它们却显现在屏幕上。这些软件从何而来? 其实,这些软件往往是在浏览网页、阅读小…...
测试自动化的边缘:DevTestOps 和 DevSecOps
什么是 DevOps? DevOps 允许企业通过自动化基础设施、工作流程和持续测量应用程序的性能来提高开发人员和运营团队之间的协作和生产力。通过 DevOps,开发人员可以以小块的形式编写代码,以便在几个小时内集成、测试、监控和部署代码ÿ…...
fatal:Could not read from remote repository解决方法
Linux服务器如何连接GitHub? 生成SSH密钥 ssh-keygen -C “邮箱” -t rsa 存放位置一般是/root/.ssh/id_rsa 登录个人github,添加客户端生成的公钥 打开Settings,点击SSH and GPG keys,点击New SSH Key。Key中粘贴id_rsa.pub…...
数学基础
线性代数 关键词:线性方程组、矩阵、增广矩阵(系数矩阵、常数项矩阵)、阶梯型矩阵、行最简矩阵、最简形矩阵、向量系统、向量加法、向量空间、基本单位向量、线性相关、线性无关、Span张成空间、 向量乘法(点积、内积、外积、叉积…...
【Python】Python语言基础(上)
第一章 前言 1. Python简介 Python语言并不是新的语言,它早于HTTP 1.0协议5年,早于Java语言 4年。 Python是由荷兰人Guido van Rossum(吉多范罗苏姆)于1989年圣诞节期间在阿姆斯特丹休假时为了打发无聊的假期而编写的一个脚本…...
C#设计模式六大原则之依赖倒置原则
C#设计模式六大原则是单一职责原则、里氏替换原则、依赖倒置原则、接口隔离原则、迪米特法则、开闭原则。它们不是要我们刻板的遵守,而是根据实际需要灵活运用。只要对它们的遵守程度在一个合理的范围内,努为做到一个良好的设计。以下介绍C#依赖倒置原则…...
IDEA的使用(二)快捷键 (IntelliJ IDEA 2022.1.3版本)
1. IDEA中的常用快捷键 1.1 通用型快捷键 1.2 提高编写速度 ctrl shift ↑或↓ 只能在方法里面移动代码。 alt shift ↑或↓ 可以向方法外移动代码。 设置过自动导包,所以不用批量导包啦。 1.3 类结构、查找和查看源码 1.4 查找、替换和关闭 1.5 调整格式 1.6 De…...
微信小程序个人账号申请和配置详细教程
一、注册小程序管理账号 1、注册方法 在微信公众平台官网首页(mp.weixin.qq.com),点击右上角的“立即注册”按钮。 2、选择注册的账号类型 选择“小程序”,点击“查看类型区别”可查看不同类型账号的区别和优势。 3、填写邮箱和…...
opencv定位图片中的图案?
import cv2 as cv2def find_positions(image_path, small_image_path):# 读取大图和小图large_image cv2.imread(image_path)small_image cv2.imread(small_image_path)# 小图规格small_image_h, small_image_w small_image.shape[:2]# 对比大图与小图# 匹配模板res cv2.ma…...
高防CDN:网络安全的不可或缺之选
在当今数字化时代,网络攻击已经成为互联网上的一种不可避免的风险。为了应对不断升级的网络威胁,许多企业和组织正在采用高防御CDN(Content Delivery Network)技术,以确保他们的在线资产得到保护,用户体验得…...
flutter 常用组件:文本、图片和按钮
文章目录 文本控件富文本控件图片本地图片网络图片按钮文本控件 ##一’码’当先 Text(这是一段文本这是一段文本这是一段文本这是一段文本这是一段文本这是一段文本这是一段文本这是一段文本,textAlign:TextAlign.center,style: TextStyle(fontWeight: FontWeight.bold, font…...
BI工具:让数据分析井然有序一望而知
BI(Business Intelligence)工具是一类专门用于数据分析和决策支持的软件工具。 它们能够将企业内部和外部的数据进行整合、处理和可视化,帮助用户从海量数据中获取有价值的见解和洞察,并以直观、易懂的方式展示给决策者和相关人员…...
基于算法竞赛的c++编程(28)结构体的进阶应用
结构体的嵌套与复杂数据组织 在C中,结构体可以嵌套使用,形成更复杂的数据结构。例如,可以通过嵌套结构体描述多层级数据关系: struct Address {string city;string street;int zipCode; };struct Employee {string name;int id;…...
调用支付宝接口响应40004 SYSTEM_ERROR问题排查
在对接支付宝API的时候,遇到了一些问题,记录一下排查过程。 Body:{"datadigital_fincloud_generalsaas_face_certify_initialize_response":{"msg":"Business Failed","code":"40004","sub_msg…...
Prompt Tuning、P-Tuning、Prefix Tuning的区别
一、Prompt Tuning、P-Tuning、Prefix Tuning的区别 1. Prompt Tuning(提示调优) 核心思想:固定预训练模型参数,仅学习额外的连续提示向量(通常是嵌入层的一部分)。实现方式:在输入文本前添加可训练的连续向量(软提示),模型只更新这些提示参数。优势:参数量少(仅提…...
椭圆曲线密码学(ECC)
一、ECC算法概述 椭圆曲线密码学(Elliptic Curve Cryptography)是基于椭圆曲线数学理论的公钥密码系统,由Neal Koblitz和Victor Miller在1985年独立提出。相比RSA,ECC在相同安全强度下密钥更短(256位ECC ≈ 3072位RSA…...
DeepSeek 赋能智慧能源:微电网优化调度的智能革新路径
目录 一、智慧能源微电网优化调度概述1.1 智慧能源微电网概念1.2 优化调度的重要性1.3 目前面临的挑战 二、DeepSeek 技术探秘2.1 DeepSeek 技术原理2.2 DeepSeek 独特优势2.3 DeepSeek 在 AI 领域地位 三、DeepSeek 在微电网优化调度中的应用剖析3.1 数据处理与分析3.2 预测与…...
Vue3 + Element Plus + TypeScript中el-transfer穿梭框组件使用详解及示例
使用详解 Element Plus 的 el-transfer 组件是一个强大的穿梭框组件,常用于在两个集合之间进行数据转移,如权限分配、数据选择等场景。下面我将详细介绍其用法并提供一个完整示例。 核心特性与用法 基本属性 v-model:绑定右侧列表的值&…...
学校招生小程序源码介绍
基于ThinkPHPFastAdminUniApp开发的学校招生小程序源码,专为学校招生场景量身打造,功能实用且操作便捷。 从技术架构来看,ThinkPHP提供稳定可靠的后台服务,FastAdmin加速开发流程,UniApp则保障小程序在多端有良好的兼…...
屋顶变身“发电站” ,中天合创屋面分布式光伏发电项目顺利并网!
5月28日,中天合创屋面分布式光伏发电项目顺利并网发电,该项目位于内蒙古自治区鄂尔多斯市乌审旗,项目利用中天合创聚乙烯、聚丙烯仓库屋面作为场地建设光伏电站,总装机容量为9.96MWp。 项目投运后,每年可节约标煤3670…...
spring:实例工厂方法获取bean
spring处理使用静态工厂方法获取bean实例,也可以通过实例工厂方法获取bean实例。 实例工厂方法步骤如下: 定义实例工厂类(Java代码),定义实例工厂(xml),定义调用实例工厂ÿ…...
【分享】推荐一些办公小工具
1、PDF 在线转换 https://smallpdf.com/cn/pdf-tools 推荐理由:大部分的转换软件需要收费,要么功能不齐全,而开会员又用不了几次浪费钱,借用别人的又不安全。 这个网站它不需要登录或下载安装。而且提供的免费功能就能满足日常…...
