信用网站一体化建设方案/网络培训机构
提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
文章目录
- 案例分析
- 生产背景
- 死锁日志
- 表结构
- 执行计划 EXPLAN
- 为什么会用 index_merge(索引合并)
- 为什么用了 index_merge就死锁了
- 解决方案
- 注:MySQL官方已经确认了此bug:==77209==
案例分析
生产背景
生产环境出现死锁流水,通过查看死锁日志,看到造成死锁的是两条一样的update语句(只有where条件中的值不同),如下:
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx1' AND `status` = 0;
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx2' AND `status` = 0;
一开始比较费解,通过大量查询跟学习后,分析出了死锁形成的具体原理,特分享给大家,希望能帮助到遇到同样问题的朋友。
死锁日志
*** (1) TRANSACTION:
TRANSACTION 791913819, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 462005230, OS thread handle 0x7f55d5da3700, query id 2621313306 x.x.x.x test_user Searching rows for update
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx1' AND `status` = 0;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 110 page no 39167 n bits 1056 index `idx_status` of table `test`.`test_table` trx id 791913819 lock_mode X waiting
Record lock, heap no 495 PHYSICAL RECORD: n_fields 2; compact format; info bits 0*** (2) TRANSACTION:
TRANSACTION 791913818, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 3, locked 3
5 lock struct(s), heap size 1184, 4 row lock(s)
MySQL thread id 462005231, OS thread handle 0x7f55cee63700, query id 2621313305 x.x.x.x test_user Searching rows for update
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx2' AND `status` = 0;
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 110 page no 39167 n bits 1056 index `idx_status` of table `test`.`test_table` trx id 791913818 lock_mode X
Record lock, heap no 495 PHYSICAL RECORD: n_fields 2; compact format; info bits 0*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 110 page no 41569 n bits 88 index `PRIMARY` of table `test`.`test_table` trx id 791913818 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 30; compact format; info bits 0*** WE ROLL BACK TRANSACTION (1)
简要分析下上边的死锁日志:
1、第一块内容(第1行到第9行)中,第6行为事务(1)执行的SQL语句,第7和第8行意思为事务(1)在等待 idx_status 索引上的X锁;
2、第二块内容(第11行到第19行)中,第16行为事务(2)执行的SQL语句,第17和第18行意思为事务(2)持有 idx_status 索引上的X锁;
3、第三块内容(第21行到第23行)的意思为,事务(2)在等待 PRIMARY 索引上的X锁。(but not gap指不是间隙锁)
4、最后一句的意思即为,MySQL将事务(1)进行了回滚操作。
表结构
CREATE TABLE `test_table` (`id` int(11) NOT NULL AUTO_INCREMENT,`trans_id` varchar(21) NOT NULL,`status` int(11) NOT NULL,PRIMARY KEY (`id`),UNIQUE KEY `uniq_trans_id` (`trans_id`) USING BTREE,KEY `idx_status` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
- 主键索引: id
- 唯一索引: trans_id
- 普通索引 :status
InnoDB引擎中有两种索引:
聚簇索引: 将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据。
辅助索引: 辅助索引叶子节点存储的是主键值,也就是聚簇索引的键值。
主键索引 PRIMARY 就是聚簇索引,叶子节点中会保存行数据。 uniq_trans_id 索引和 idx_status 索引为辅助索引,叶子节点中保存的是主键值,也就是id列值。
当我们通过辅助索引查找行数据时,先通过辅助索引找到主键id,再通过主键索引进行二次查找(也叫回表),最终找到行数据。
执行计划 EXPLAN
通过看执行计划,可以发现,update语句用到了index merge(索引合并),也就是这条语句既用到了 uniq_trans_id 索引,又用到了 idx_status 索引,
Using intersect(uniq_trans_id,idx_status) 的意思是通过两个索引获取交集。
为什么会用 index_merge(索引合并)
MySQL5.0之前,一个表一次只能使用一个索引,无法同时使用多个索引分别进行条件扫描。但是从5.1开始,引入了 index merge 优化技术,对同一个表可以使用多个索引分别进行条件扫描。
如执行计划中的语句:
UPDATE test_table SET `status` = 1 WHERE `trans_id` = '38' AND `status` = 0 ;
MySQL会根据 trans_id = ‘38’ 这个条件,利用 uniq_trans_id 索引找到叶子节点中保存的id值;同时会根据 status = 0 这个条件,利用 idx_status 索引找到叶子节点中保存的id值;然后将找到的两组id值取交集,最终通过交集后的id回表,也就是通过 PRIMARY 索引找到叶子节点中保存的行数据。
这里可能很多人会有疑问了,uniq_trans_id 已经是一个唯一索引了,通过这个索引最终只能找到最多一条数据,那MySQL优化器为啥还要用两个索引取交集,再回表进行查询呢,这样不是多了一次 idx_status 索引查找的过程么。我们来分析一下这两种情况执行过程。
上边两种情况,主要区别在于,第一种是先通过一个索引把数据找到后,再用其它查询条件进行过滤;第二种是先通过两个索引查出的id值取交集,如果取交集后还存在id值,则再去回表将数据取出来。
当优化器认为第二种情况执行成本比第一种要小时,就会出现索引合并。(生产环境流水表中 status = 0 的数据非常少,这也是优化器考虑用第二种情况的原因之一)。
为什么用了 index_merge就死锁了
上面简要画了一下两个update事务加锁的过程,从图中可以看到,在 idx_status 索引和 PRIMARY (聚簇索引) 上都存在重合交叉的部分,这样就为死锁造成了条件。
如,当遇到以下时序时,就会出现死锁:
事务1等待事务2释放锁,事务2等待事务1释放锁,这样就造成了死锁。
MySQL检测到死锁后,会自动回滚代价更低的那个事务,如上边的时序图中,事务1持有的锁比事务2少,则MySQL就将事务1进行了回滚。
解决方案
一、从代码层面
- where 查询条件中,只传 trans_id ,将数据查询出来后,在代码层面判断 status 状态是否为0;
- 使用 force index(uniq_trans_id) 强制查询语句使用 uniq_trans_id 索引;
- where 查询条件后边直接用 id 字段,通过主键去更新。
二、从MySQL层面
- 删除 idx_status 索引或者建一个包含这俩列的联合索引;
- 将MySQL优化器的index merge优化关闭。
注:MySQL官方已经确认了此bug:77209
https://bugs.mysql.com/bug.php?id=77209
相关文章:

锁--07_2---- index merge(索引合并)引起的死锁
提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档 文章目录 案例分析生产背景死锁日志表结构执行计划 EXPLAN为什么会用 index_merge(索引合并)为什么用了 index_merge就死锁了解决方案注:M…...

后端打印不了trace等级的日志?-SpringBoot日志打印-Slf4j
在调用log变量的方法来输出日志时,有以上5个级别对应的方法,从不太重要,到非常重要 调用不同的方法,就会输出不同级别的日志。 trace:跟踪信息debug:调试信息info:一般信息warn:警告…...

声明式编程Declarative Programming
接下来要介绍第五种编程范式 -- 声明式编程。分别从它的优缺点、案例分析和适用的编程语言这三个方面来介绍这个歌编程范式。 声明式编程是一种编程范式,其核心思想是通过描述问题的性质和约束,而不是通过描述解决问题的步骤来进行编程。这与命令式编程…...

人工智能与天文:技术前沿与未来展望
人工智能与天文:技术前沿与未来展望 一、引言 随着科技的飞速发展,人工智能(AI)在各个领域的应用越来越广泛。在天文领域,AI也发挥着越来越重要的作用。本文将探讨人工智能与天文学的结合,以及这种结合带…...

JeecgBoot 框架升级至 Spring Boot3 的实战步骤
JeecgBoot 框架升级 Spring Boot 3.1.5 步骤 JEECG官方推出SpringBoot3分支:https://github.com/jeecgboot/jeecg-boot/tree/springboot3 本次更新由于属于破坏式更新,有几个生态内的组件,无法进行找到平替或无法升级,目前尚不完…...

论文阅读——Semantic-SAM
Semantic-SAM可以做什么: 整合了七个数据集: 一般的分割数据集,目标级别分割数据集:MSCOCO, Objects365, ADE20k 部分分割数据集:PASCAL Part, PACO, PartImagenet, and SA-1B The datasets are SA-1B, COCO panopt…...

gitlab下载,离线安装
目录 1.下载 2.安装 3.配置 4.启动 5.登录 参考: 1.下载 根据服务器操作系统版本,下载对应的RPM包。 gitlab官网: The DevSecOps Platform | GitLab rpm包官网下载地址: gitlab/gitlab-ce - Results in gitlab/gitlab-ce 国内镜像地…...

【SpringBoot篇】Interceptor拦截器 | 拦截器和过滤器的区别
文章目录 🌹概念⭐作用 🎄快速入门⭐入门案例代码实现 🛸拦截路径🍔拦截器interceptor和过滤器filter的区别🎆登录校验 🌹概念 拦截器(Interceptor)是一种软件设计模式,…...

conan入门(三十六):在set_version方法中从pom.xml中读取版本号实现动态版本定义
一般情况下,我们通过self.version字段定义conan 包的版本号如下: class PkgConan(ConanFile):name "pkg"version "1.7.3"因为版本号是写死的,所以这种方式有局限性: 比如我的java项目中版本号是在pom.xml中…...

为什么 GAN 不好训练
为什么 GAN 不好训练?先看 GAN 的损失: 当生成器固定时,堆D(x)求导,推理得到(加号右边先对log求导,再对负项求导) 然后在面对最优Discriminator时,Generator的优化目标就变成了&…...

select、poll、epoll 区别有哪些
文章目录 select、poll、epoll 区别有哪些?select:poll:epoll: select、poll、epoll 区别有哪些? select: 它仅仅知道了,有 I/O 事件发生了,却并不知道是哪那几个流(可…...

大模型下开源文档解析工具总结及技术思考
1 基于文档解析工具的方法 pdf解析工具 导图一览: PyPDF2提取txt: import PyPDF2 def extract_text_from_pdf(pdf_path):with open(pdf_path, rb) as file:pdf_reader PyPDF2.PdfFileReader(file)num_pages pdf_reader.numPagestext ""f…...

【华为数据之道学习笔记】5-4 数据入湖方式
数据入湖遵循华为信息架构,以逻辑数据实体为粒度入湖,逻辑数据实体在首次入湖时应该考虑信息的完整性。原则上,一个逻辑数据实体的所有属性应该一次性进湖,避免一个逻辑实体多次入湖,增加入湖工作量。 数据入湖的方式…...

Vue3-03-reactive() 响应式基本使用
reactive() 的简介 reactive() 是vue3 中进行响应式状态声明的另一种方式; 但是,它只能声明 【对象类型】的响应式变量,【不支持声明基本数据类型】。reactive() 与 ref() 一样,都是深度响应式的,即对象嵌套属性发生了…...

OpenAI开源超级对齐方法:用GPT-2,监督、微调GPT-4
12月15日,OpenAI在官网公布了最新研究论文和开源项目——如何用小模型监督大模型,实现更好的新型对齐方法。 目前,大模型的主流对齐方法是RLHF(人类反馈强化学习)。但随着大模型朝着多模态、AGI发展,神经元…...

TeeChart.NET 2023.11.17 Crack
.NET 的 TeeChart 图表控件提供了一个出色的通用组件套件,可满足无数的图表需求,也针对重要的垂直领域,例如金融、科学和统计领域。 数据可视化 数十种完全可定制的交互式图表类型、地图和仪表指示器,以及完整的功能集,…...

计算机网络常见的缩写
计算机网络常见缩写 通讯控制处理机(Communication Control Processor)CCP 前端处理机(Front End Processor)FEP 开放系统互连参考模型 OSI/RM 开放数据库连接(Open Database Connectivity)ODBC 网络操作系…...

vue cli 脚手架之配置代理
方法二...

STM32启动流程详解(超全,startup_stm32xx.s分析)
单片机上电后执行的第一段代码 1.初始化堆栈指针 SP_initial_sp 2.初始化 PC 指针Reset_Handler 3.初始化中断向量表 4.配置系统时钟 5.调用 C 库函数_main 初始化用户堆栈,然后进入 main 函数。 在正式讲解之前,我们需要了解STM32的启动模式。 STM32的…...

小程序接口OK,桌面调试接口不行
手机小程序OK,桌面版出现问题; 环境:iis反向url的tomcat服务,提供接口。 该接口post了一个很大的数组,处理时间比较久。 1)桌面调试出现错误,提示 用apipost调用接口同样出错, 502 - Web 服务器在作为网关或代理服…...

【贪心】LeetCode-406. 根据身高重建队列
406. 根据身高重建队列。 假设有打乱顺序的一群人站成一个队列,数组 people 表示队列中一些人的属性(不一定按顺序)。每个 people[i] [hi, ki] 表示第 i 个人的身高为 hi ,前面 正好 有 ki 个身高大于或等于 hi 的人。 请你重新…...

【C++11特性篇】C++11中新增的initializer_list——初始化的小利器
前言 大家好吖,欢迎来到 YY 滴C11系列 ,热烈欢迎! 本章主要内容面向接触过C的老铁 主要内容含: 欢迎订阅 YY滴C专栏!更多干货持续更新!以下是传送门! 目录 一.探究std::initializer_list是什么…...

springboot(ssm宠物美容机构CRM系统 宠物服务商城系统Java系统
springboot(ssm宠物美容机构CRM系统 客户关系管理系统Java系统 开发语言:Java 框架:ssm/springboot vue JDK版本:JDK1.8(或11) 服务器:tomcat 数据库:mysql 5.7(或8.0ÿ…...

LSTM 双向 Bi-LSTM
目录 一.Bi-LSTM介绍 二.Bi-LSTM结构 Bi-LSTM 代码实例 一.Bi-LSTM介绍 由于LSTM只能从序列里由前往后预测,为了既能够从前往后预测,也能从后往前预测,Bi-LSTM便被发明了出来。简单来说,BiLSTM就是由前向LSTM与后向LSTM组合而成。 二.Bi-LSTM结构 转自:...

2024测试开发面试题完整版本(附答案)
目录 1. 什么是软件测试, 谈谈你对软件测试的了解 2. 我看你简历上有写了解常见的开发模型和测试模型, 那你跟我讲一下敏捷模型 3. 我看你简历上还写了挺多开发技能的, 那你给我讲讲哈希表的实现流程 4. 谈一谈什么是线程安全问题, 如何解决 5. 既然你选择走测…...

MySQL作为服务端的配置过程与实际案例
MySQL是一款流行的关系型数据库管理系统,广泛应用于各种业务场景中。作为服务端,MySQL的配置过程对于数据库的性能、安全性和稳定性至关重要。本文将详细介绍MySQL作为服务端的配置过程,并通过一个实际案例进行举例说明。 一、MySQL服务端配…...

Appium 自动化自学篇 —— 初识Appium自动化!
Appium 简介 随着移动终端的普及,手机应用越来越多,也越来越重要。而作为测试 的我们也要与时俱进,努力学习手机 App 的相关测试,文章将介绍手机自动化测试框架 Appium 。 那究竟什么是 Appium 呢? 接下来我们一起来学习PythonS…...

Linux基本操作指令
哈喽小伙伴们,从这篇文章开始,在学习数据结构的同时,我们开启一个新的篇章——Linux操作系统的学习,这将会是又一个新的开始,希望小伙伴们能够认真细心,不要掉队哦。 目录 一.什么是Linux 二.为什么要学习…...

探索SD-WAN技术对传统制造业实现智能制造的作用
在智能制造背景下,传统制造业面临着日益增长的信息化建设需求。随着企业趋向数字化转型,构建稳定、高效的网络基础设施成为提升企业核心竞争力的重要一环。 制造业企业信息化建设中的组网需求: 第一,连接多地分支机构,…...

C++基础-this指针详解
本文详细讲解C++this指针 定义 this 是 C++ 中的一个关键字,一个特殊的指针,它指向当前对象地址(换句话说,其值为 &object),通过它可以访问当前对象的所有成员。 类定义好后我们就可以通过类来创建多个实例对象,每个对象都有各自的实例属性(实例变量),但是非内…...