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)工具是一类专门用于数据分析和决策支持的软件工具。 它们能够将企业内部和外部的数据进行整合、处理和可视化,帮助用户从海量数据中获取有价值的见解和洞察,并以直观、易懂的方式展示给决策者和相关人员…...
Cursor实现用excel数据填充word模版的方法
cursor主页:https://www.cursor.com/ 任务目标:把excel格式的数据里的单元格,按照某一个固定模版填充到word中 文章目录 注意事项逐步生成程序1. 确定格式2. 调试程序 注意事项 直接给一个excel文件和最终呈现的word文件的示例,…...
云启出海,智联未来|阿里云网络「企业出海」系列客户沙龙上海站圆满落地
借阿里云中企出海大会的东风,以**「云启出海,智联未来|打造安全可靠的出海云网络引擎」为主题的阿里云企业出海客户沙龙云网络&安全专场于5.28日下午在上海顺利举办,现场吸引了来自携程、小红书、米哈游、哔哩哔哩、波克城市、…...
现代密码学 | 椭圆曲线密码学—附py代码
Elliptic Curve Cryptography 椭圆曲线密码学(ECC)是一种基于有限域上椭圆曲线数学特性的公钥加密技术。其核心原理涉及椭圆曲线的代数性质、离散对数问题以及有限域上的运算。 椭圆曲线密码学是多种数字签名算法的基础,例如椭圆曲线数字签…...
【配置 YOLOX 用于按目录分类的图片数据集】
现在的图标点选越来越多,如何一步解决,采用 YOLOX 目标检测模式则可以轻松解决 要在 YOLOX 中使用按目录分类的图片数据集(每个目录代表一个类别,目录下是该类别的所有图片),你需要进行以下配置步骤&#x…...
土地利用/土地覆盖遥感解译与基于CLUE模型未来变化情景预测;从基础到高级,涵盖ArcGIS数据处理、ENVI遥感解译与CLUE模型情景模拟等
🔍 土地利用/土地覆盖数据是生态、环境和气象等诸多领域模型的关键输入参数。通过遥感影像解译技术,可以精准获取历史或当前任何一个区域的土地利用/土地覆盖情况。这些数据不仅能够用于评估区域生态环境的变化趋势,还能有效评价重大生态工程…...
Java面试专项一-准备篇
一、企业简历筛选规则 一般企业的简历筛选流程:首先由HR先筛选一部分简历后,在将简历给到对应的项目负责人后再进行下一步的操作。 HR如何筛选简历 例如:Boss直聘(招聘方平台) 直接按照条件进行筛选 例如:…...
Linux --进程控制
本文从以下五个方面来初步认识进程控制: 目录 进程创建 进程终止 进程等待 进程替换 模拟实现一个微型shell 进程创建 在Linux系统中我们可以在一个进程使用系统调用fork()来创建子进程,创建出来的进程就是子进程,原来的进程为父进程。…...
佰力博科技与您探讨热释电测量的几种方法
热释电的测量主要涉及热释电系数的测定,这是表征热释电材料性能的重要参数。热释电系数的测量方法主要包括静态法、动态法和积分电荷法。其中,积分电荷法最为常用,其原理是通过测量在电容器上积累的热释电电荷,从而确定热释电系数…...
云原生安全实战:API网关Kong的鉴权与限流详解
🔥「炎码工坊」技术弹药已装填! 点击关注 → 解锁工业级干货【工具实测|项目避坑|源码燃烧指南】 一、基础概念 1. API网关(API Gateway) API网关是微服务架构中的核心组件,负责统一管理所有API的流量入口。它像一座…...
MySQL 部分重点知识篇
一、数据库对象 1. 主键 定义 :主键是用于唯一标识表中每一行记录的字段或字段组合。它具有唯一性和非空性特点。 作用 :确保数据的完整性,便于数据的查询和管理。 示例 :在学生信息表中,学号可以作为主键ÿ…...
