当前位置: 首页 > news >正文

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='黑白名单申请单明细'

表数据:

idapply_idstatus
95965116953692205220689981
96073816953692275761736901
96131916953730476739033261
96136516953731224478652281

通过 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锁的范围
beginbegin
select * from jdi_roster_apply_detail where apply_id = ‘1695369220522068998’ for updateidx_apply_id所以锁住了(-∞,1695369220522068998],(1695369220522068998,1695369227576173690) 主键id索引锁住了 id=959651
select * from jdi_roster_apply_detail where apply_id = ‘1695369227576173690’ for updateidx_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-集群列表-更多-连接查询-死锁中&#xff0c;看到9月22日有数据库死锁日志&#xff0c;后排查发现是因为mysql的优化-index merge&#xff08;索引合并&#xff09;导致数据库死锁。 定义 index merge(索引合并)&#xff1a;该数据库查询优化的一种技术&#xff0…...

第四章 网络层 | 计算机网络(谢希仁 第八版)

文章目录 第四章 网络层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−1​xk−1​Gk−1​wk−1​    式中 Φ k , k − 1 \Phi_{…...

仿真软件Proteus8.9 SP2 Pro 下载、安装、汉化详细图文教程

Proteus8.9 安装教程 视频教程一、安装软件解压二、软件安装常见问题及解决方法&#xff1a;三、汉化 Proteus8.9 SP2 Pro 安装教程 本破解教程仅供个人及 proteus 8.9粉丝们交流学习之用&#xff0c;请勿用于商业用途&#xff0c; 谢谢支持。此版本为Proteus8.9 SP2 Pro。其他…...

振弦传感器和无线振弦采集仪在隧道安全监测的解决方案

振弦传感器和无线振弦采集仪在隧道安全监测的解决方案 隧道作为交通工程的重要组成部分&#xff0c;具有极高的安全风险&#xff0c;因此隧道安全监测是必不可少的。振弦传感器和无线振弦采集仪作为隧道安全监测的两种重要设备&#xff0c;能够有效地监测隧道的振动情况&#…...

c# xml 参数读取的复杂使用

完整使用2 生产厂家里面包含很多规格型号,一个规格型号里面包含很多出厂序列号,点击下一步如果检测到填充的和保存的不一样 就新增一条(如检测到生产厂家相同,但是规格型号不同,就新增一组规格型号)。 界面一:新增界面 界面2 删除界面 界面一:新增界面 load 其中…...

在Mac中使用 brew services start redis 命令启动、停止Redis服务报错

一、问题现象 启动Redis服务命令&#xff1a; brew services start redis异常信息如下&#xff1a; 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…...

为什么手机会莫名多出许多软件?

许多手机用户都曾遭遇过这样的问题&#xff0c;他们在使用手机的过程中&#xff0c;突然发现手机屏幕上出现了一些未知的软件。这些软件并非他们主动下载的&#xff0c;但它们却显现在屏幕上。这些软件从何而来&#xff1f; 其实&#xff0c;这些软件往往是在浏览网页、阅读小…...

测试自动化的边缘:DevTestOps 和 DevSecOps

什么是 DevOps&#xff1f; DevOps 允许企业通过自动化基础设施、工作流程和持续测量应用程序的性能来提高开发人员和运营团队之间的协作和生产力。通过 DevOps&#xff0c;开发人员可以以小块的形式编写代码&#xff0c;以便在几个小时内集成、测试、监控和部署代码&#xff…...

fatal:Could not read from remote repository解决方法

Linux服务器如何连接GitHub&#xff1f; 生成SSH密钥 ssh-keygen -C “邮箱” -t rsa 存放位置一般是/root/.ssh/id_rsa 登录个人github&#xff0c;添加客户端生成的公钥 打开Settings&#xff0c;点击SSH and GPG keys&#xff0c;点击New SSH Key。Key中粘贴id_rsa.pub…...

数学基础

线性代数 关键词&#xff1a;线性方程组、矩阵、增广矩阵&#xff08;系数矩阵、常数项矩阵&#xff09;、阶梯型矩阵、行最简矩阵、最简形矩阵、向量系统、向量加法、向量空间、基本单位向量、线性相关、线性无关、Span张成空间、 向量乘法&#xff08;点积、内积、外积、叉积…...

【Python】Python语言基础(上)

第一章 前言 1. Python简介 Python语言并不是新的语言&#xff0c;它早于HTTP 1.0协议5年&#xff0c;早于Java语言 4年。 ​ Python是由荷兰人Guido van Rossum&#xff08;吉多范罗苏姆&#xff09;于1989年圣诞节期间在阿姆斯特丹休假时为了打发无聊的假期而编写的一个脚本…...

C#设计模式六大原则之依赖倒置原则

C#设计模式六大原则是单一职责原则、里氏替换原则、依赖倒置原则、接口隔离原则、迪米特法则、开闭原则。它们不是要我们刻板的遵守&#xff0c;而是根据实际需要灵活运用。只要对它们的遵守程度在一个合理的范围内&#xff0c;努为做到一个良好的设计。以下介绍C#依赖倒置原则…...

IDEA的使用(二)快捷键 (IntelliJ IDEA 2022.1.3版本)

1. IDEA中的常用快捷键 1.1 通用型快捷键 1.2 提高编写速度 ctrl shift ↑或↓ 只能在方法里面移动代码。 alt shift ↑或↓ 可以向方法外移动代码。 设置过自动导包&#xff0c;所以不用批量导包啦。 1.3 类结构、查找和查看源码 1.4 查找、替换和关闭 1.5 调整格式 1.6 De…...

微信小程序个人账号申请和配置详细教程

一、注册小程序管理账号 1、注册方法 在微信公众平台官网首页&#xff08;mp.weixin.qq.com&#xff09;&#xff0c;点击右上角的“立即注册”按钮。 2、选择注册的账号类型 选择“小程序”&#xff0c;点击“查看类型区别”可查看不同类型账号的区别和优势。 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:网络安全的不可或缺之选

在当今数字化时代&#xff0c;网络攻击已经成为互联网上的一种不可避免的风险。为了应对不断升级的网络威胁&#xff0c;许多企业和组织正在采用高防御CDN&#xff08;Content Delivery Network&#xff09;技术&#xff0c;以确保他们的在线资产得到保护&#xff0c;用户体验得…...

flutter 常用组件:文本、图片和按钮

文章目录 文本控件富文本控件图片本地图片网络图片按钮文本控件 ##一’码’当先 Text(这是一段文本这是一段文本这是一段文本这是一段文本这是一段文本这是一段文本这是一段文本这是一段文本,textAlign:TextAlign.center,style: TextStyle(fontWeight: FontWeight.bold, font…...

BI工具:让数据分析井然有序一望而知

BI&#xff08;Business Intelligence&#xff09;工具是一类专门用于数据分析和决策支持的软件工具。 它们能够将企业内部和外部的数据进行整合、处理和可视化&#xff0c;帮助用户从海量数据中获取有价值的见解和洞察&#xff0c;并以直观、易懂的方式展示给决策者和相关人员…...

[特殊字符] 智能合约中的数据是如何在区块链中保持一致的?

&#x1f9e0; 智能合约中的数据是如何在区块链中保持一致的&#xff1f; 为什么所有区块链节点都能得出相同结果&#xff1f;合约调用这么复杂&#xff0c;状态真能保持一致吗&#xff1f;本篇带你从底层视角理解“状态一致性”的真相。 一、智能合约的数据存储在哪里&#xf…...

label-studio的使用教程(导入本地路径)

文章目录 1. 准备环境2. 脚本启动2.1 Windows2.2 Linux 3. 安装label-studio机器学习后端3.1 pip安装(推荐)3.2 GitHub仓库安装 4. 后端配置4.1 yolo环境4.2 引入后端模型4.3 修改脚本4.4 启动后端 5. 标注工程5.1 创建工程5.2 配置图片路径5.3 配置工程类型标签5.4 配置模型5.…...

python打卡day49

知识点回顾&#xff1a; 通道注意力模块复习空间注意力模块CBAM的定义 作业&#xff1a;尝试对今天的模型检查参数数目&#xff0c;并用tensorboard查看训练过程 import torch import torch.nn as nn# 定义通道注意力 class ChannelAttention(nn.Module):def __init__(self,…...

FFmpeg 低延迟同屏方案

引言 在实时互动需求激增的当下&#xff0c;无论是在线教育中的师生同屏演示、远程办公的屏幕共享协作&#xff0c;还是游戏直播的画面实时传输&#xff0c;低延迟同屏已成为保障用户体验的核心指标。FFmpeg 作为一款功能强大的多媒体框架&#xff0c;凭借其灵活的编解码、数据…...

ESP32读取DHT11温湿度数据

芯片&#xff1a;ESP32 环境&#xff1a;Arduino 一、安装DHT11传感器库 红框的库&#xff0c;别安装错了 二、代码 注意&#xff0c;DATA口要连接在D15上 #include "DHT.h" // 包含DHT库#define DHTPIN 15 // 定义DHT11数据引脚连接到ESP32的GPIO15 #define D…...

Neo4j 集群管理:原理、技术与最佳实践深度解析

Neo4j 的集群技术是其企业级高可用性、可扩展性和容错能力的核心。通过深入分析官方文档,本文将系统阐述其集群管理的核心原理、关键技术、实用技巧和行业最佳实践。 Neo4j 的 Causal Clustering 架构提供了一个强大而灵活的基石,用于构建高可用、可扩展且一致的图数据库服务…...

微服务商城-商品微服务

数据表 CREATE TABLE product (id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 商品id,cateid smallint(6) UNSIGNED NOT NULL DEFAULT 0 COMMENT 类别Id,name varchar(100) NOT NULL DEFAULT COMMENT 商品名称,subtitle varchar(200) NOT NULL DEFAULT COMMENT 商…...

IoT/HCIP实验-3/LiteOS操作系统内核实验(任务、内存、信号量、CMSIS..)

文章目录 概述HelloWorld 工程C/C配置编译器主配置Makefile脚本烧录器主配置运行结果程序调用栈 任务管理实验实验结果osal 系统适配层osal_task_create 其他实验实验源码内存管理实验互斥锁实验信号量实验 CMISIS接口实验还是得JlINKCMSIS 简介LiteOS->CMSIS任务间消息交互…...

聊一聊接口测试的意义有哪些?

目录 一、隔离性 & 早期测试 二、保障系统集成质量 三、验证业务逻辑的核心层 四、提升测试效率与覆盖度 五、系统稳定性的守护者 六、驱动团队协作与契约管理 七、性能与扩展性的前置评估 八、持续交付的核心支撑 接口测试的意义可以从四个维度展开&#xff0c;首…...

html-<abbr> 缩写或首字母缩略词

定义与作用 <abbr> 标签用于表示缩写或首字母缩略词&#xff0c;它可以帮助用户更好地理解缩写的含义&#xff0c;尤其是对于那些不熟悉该缩写的用户。 title 属性的内容提供了缩写的详细说明。当用户将鼠标悬停在缩写上时&#xff0c;会显示一个提示框。 示例&#x…...