MySQL插入更新死锁问题解析

于 2024-09-04 14:09:35 发布
阅读量417 收藏 11
点赞数
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhuanzhuantech/article/details/141892325
版权

文章目录

1 问题背景

前段时间，领导说我们业务量大涨，部门新增HC，让我们赶紧招人。

领导：经过大家的共同努力和不懈奋斗，我们的业务量实现了显著的大涨，这是对我们团队能力和工作成果的最好证明。为了更好地应对业务量的增长，满足客户的需求，我们决定在部门内新增HC，大家行动起来吧。

。。。

面试官：你好，今天想和你聊聊MySQL数据库中的死锁问题。首先，你能解释一下什么是死锁吗？

应聘者：死锁是指两个或多个事务在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，这些事务都将无法向前推进。在MySQL中，这通常发生在多个事务尝试以不同顺序访问相同的资源（如表或行）时。

面试官：很好，那么MySQL中死锁发生的常见原因有哪些？

应聘者：MySQL中死锁的常见原因包括：

不同事务交叉锁定资源：当两个或多个事务相互等待对方释放锁定的资源时，就可能发生死锁。
索引使用不当：不恰当的索引使用可能导致查询锁定更多行，增加死锁的风险。
大量数据的修改：在处理大量数据时，尤其是同时修改多个表或行时，更容易发生死锁。
锁升级：在某些情况下，低级锁（如行锁）可能会升级为更高级别的锁（如表锁），这也会增加死锁的可能性。

面试官：如何分析一个SQL都加了哪些锁呢？你需要哪些前置信息呢？

应聘者：好的，我先说一下我的理解。

加锁规则：两个原则、两个优化、一个 bug

原则 1：加锁的基本单位是 next-key lock，前开后闭区间

原则 2：查找过程中访问到的对象才会加锁

优化 1：索引上的等值查询，给唯一索引加锁的时候，匹配上数据，next-key lock 退化为行锁

优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁

一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止

面试官：那我有个案例，你分析分析都加了哪些锁？是否会产生死锁。
我有一个回收单表，回收单id+回收类型 是唯一索引，
我先根据回收单id A更新回收单A状态，（如果数据不存在）再插入回收单A数据。
我再根据回收单id B更新回收单B状态，（如果数据不存在）再插入回收单B数据。

应聘者：。。。

上面是国内开发者在找工作过程中常被问到的问题，大家吐槽是八股文，过度依赖背诵，加剧应试教育的倾向，使得应聘者更加注重面试通过率而非实际技能的提升。

其实有些八股文是实际遇到问题的经验总结。

这个问题是我们在线上每日错误日志清零时发现排查的死锁问题。在这里介绍一下，给大家遇到类似问题时提供一个排查思路。

2 线上问题

2.1 线上异常日志

线上错误日志，从日志第2行可以发现是发生了死锁，
从第6行可以发现是插入了数据时发生了异常，
从20行可以看到异常的方法。
image

根据日志找到业务代码，发现业务代码逻辑是：先把回收单id 对应 历史的回收单都更新为失效，然后再插入回收单id对应的新的回收单数据。

2.2 数据准备

首先在测试库里建表，并准备相关的原数据。

1、使用的mysql版本：线上5.7.21，测试8.0.32

2、配置的隔离级别：REPEATABLE-READ

创建个checkout_detail表，分别插入三条数据。

CREATE TABLE `checkout_detail` (
  `id` bigint(20) NOT NULL COMMENT '主键id',
  `recycle_order_id` bigint(20) NOT NULL COMMENT '回收单ID',
  `confirm_recycle_time` datetime NOT NULL COMMENT '确认回收时间',
  `contrast_type` int(4) NOT NULL COMMENT '对比类型：1:售前、2:后验、3:售后',
  `remark` varchar(255) DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_idx_recycle_order_id_contrast_type` (`recycle_order_id`,`contrast_type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='后验详情表';
 
INSERT INTO checkout_detail ( id, recycle_order_id, confirm_recycle_time, contrast_type, remark )
VALUES
	( 1, 1, '2024-07-15 19:56:01', 1, "回收单1" );#模拟线上数据
	INSERT INTO checkout_detail ( id, recycle_order_id, confirm_recycle_time, contrast_type, remark );
VALUES
	( 2, 10, '2024-07-15 19:56:01', 2, "回收单10" );#模拟线上数据
	INSERT INTO checkout_detail ( id, recycle_order_id, confirm_recycle_time, contrast_type, remark );
VALUES
	( 3, 20, '2024-07-15 19:56:01', 3, "回收单20" ); #模拟线上数据

2.3 问题复现

2.3.1 执行流程
执行时间顺序事务A事务B
START TRANSACTION; START TRANSACTION;
1update checkout_detail SET remark = '更新状态' WHERE recycle_order_id = 30;
2update checkout_detail SET remark = '更新状态' WHERE recycle_order_id = 40;
3INSERT INTO checkout_detail ( id, recycle_order_id, confirm_recycle_time, contrast_type, remark )VALUES( 30, 30, '2024-07-15 19:56:01', 1, "插入回收单30" );
4INSERT INTO checkout_detail ( id, recycle_order_id, confirm_recycle_time, contrast_type, remark )VALUES( 40, 40, '2024-07-15 19:56:01', 1, "插入回收单40" );

大家可以思考一下，这个执行过程中都会加哪些锁？会发生锁等待吗？会发生死锁吗？

2.3.2 死锁排查

上面执行第3步会锁等待，执行第4步会死锁。

执行如下SQL

SHOW ENGINE INNODB STATUS;

它是MySQL 中一个非常有用的命令，它用于显示 InnoDB 存储引擎的当前状态信息。这个命令对于诊断 InnoDB 存储引擎的问题、监控性能以及理解内部操作非常有帮助。

输出的内容非常多，我们只关注锁信息就行，找到LATEST DETECTED DEADLOCK 最近一次死锁信息如下：
死锁日志

2.3.3 死锁日志分析

现在让我们来分析这个死锁日志，我只会分析我们需要的信息。

2.3.3.1 事务A23087信息
*** (1) "TRANSACTION":<br/>
TRANSACTION 23087, ACTIVE 22 sec inserting<br/>
mysql tables in use 1, locked 1<br/>
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1<br/>
MySQL thread id 9, OS thread handle 123145459134464, query id 1039 localhost 127.0.0.1 root update<br/>
INSERT INTO checkout_detail ( id, recycle_order_id, confirm_recycle_time, contrast_type, remark )VALUES( 30, 30, '2024-07-15 "19":56:01', 1, "插入回收单30" )<br/>
  1. 事务状态
    事务ID：23087
    操作：正在进行插入（INSERT）操作。
  2. 锁等待情况
    锁结构数量：3个锁结构，这表明MySQL为该事务准备了多个锁来管理对数据的访问。
    行锁数量：2个行锁，说明事务试图在checkout_detail表中的两行数据上设置锁。
  3. 事务阻塞
    LOCK WAIT：这表明事务正在等待其他事务释放锁。在当前情况下，它正在等待能够插入或更新它试图操作的两行数据。
2.3.3.2 事务23087持有锁
*** (1) HOLDS THE "LOCK(S)":<br/>
RECORD LOCKS space id 4 page no 5 n bits 72 index uniq_idx_recycle_order_id_contrast_type of table `my_database`.`checkout_detail` trx id 23087 lock_mode X <br/>
Record lock, heap no 1 PHYSICAL "RECORD": n_fields 1; compact format; info bits 0<br/>
 "0": len 8; hex 73757072656d756d; asc supremum;;<br/>

事务23087持有的锁是一个针对uniq_idx_recycle_order_id_contrast_type索引的X（排他）锁，但它实际上锁定的是索引中的“supremum”伪记录。这通常意味着事务并没有锁定任何具体的、存在的数据行，而是锁定了一个表示索引末尾的虚拟记录。

2.3.3.3 事务23087等待锁
*** (1) WAITING FOR THIS LOCK TO BE "GRANTED":<br/>
RECORD LOCKS space id 4 page no 5 n bits 72 index uniq_idx_recycle_order_id_contrast_type of table `my_database`.`checkout_detail` trx id 23087 lock_mode X insert intention waiting<br/>
Record lock, heap no 1 PHYSICAL "RECORD": n_fields 1; compact format; info bits 0<br/>
 "0": len 8; hex 73757072656d756d; asc supremum;;<br/>

事务23087正在等待一个插入意向锁（lock_mode X insert intention waiting）被授予

2.3.3.4 事务23088信息
** (2) "TRANSACTION":
TRANSACTION 23088, ACTIVE 14 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 123145460199424, query id 1043 localhost 127.0.0.1 root update
INSERT INTO checkout_detail ( id, recycle_order_id, confirm_recycle_time, contrast_type, remark )VALUES( 40, 40, '2024-07-15 "19":56:01', 1, "插入回收单40" )
  1. 事务状态
    事务ID：23088
    操作：正在进行插入（INSERT）操作。
  2. 锁等待情况
    锁结构数量：3个锁结构，这表明MySQL为该事务准备了多个锁来管理对数据的访问。
    行锁数量：2个行锁，说明事务试图在checkout_detail表中的两行数据上设置锁。
  3. 事务阻塞
    LOCK WAIT：这表明事务正在等待其他事务释放锁。在当前情况下，它正在等待能够插入或更新它试图操作的两行数据。
2.3.3.5 事务23088持有锁
*** (2) HOLDS THE "LOCK(S)":
RECORD LOCKS space id 4 page no 5 n bits 72 index uniq_idx_recycle_order_id_contrast_type of table `my_database`.`checkout_detail` trx id 23088 lock_mode X
Record lock, heap no 1 PHYSICAL "RECORD": n_fields 1; compact format; info bits 0
 "0": len 8; hex 73757072656d756d; asc supremum;;

事务23088持有的锁是一个针对uniq_idx_recycle_order_id_contrast_type索引的X（排他）锁，但它实际上锁定的是索引中的“supremum”伪记录。这通常意味着事务并没有锁定任何具体的、存在的数据行，而是锁定了一个表示索引末尾的虚拟记录。

2.3.3.6 事务23088等待锁
*** (2) WAITING FOR THIS LOCK TO BE "GRANTED":
RECORD LOCKS space id 4 page no 5 n bits 72 index uniq_idx_recycle_order_id_contrast_type of table `my_database`.`checkout_detail` trx id 23088 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL "RECORD": n_fields 1; compact format; info bits 0
 "0": len 8; hex 73757072656d756d; asc supremum;;

事务23088正在等待一个插入意向锁（lock_mode X insert intention waiting）被授予

3 分析原因

参考MySQL的官方文档。

间隙锁（Gap Locks）是一种特殊的锁机制，用于锁定索引记录之间的间隙，或者第一个索引记录之前的间隙以及最后一个索引记录之后的间隙。这种锁的主要目的是防止其他事务在这些间隙中插入新的记录，从而维护数据的一致性和隔离性。

插入意向锁（Insert Intention Locks）
是InnoDB存储引擎在插入操作前设置的一种间隙锁（Gap Locks）。这种锁的目的是在多个事务尝试向同一个索引间隙中插入不同位置的数据时，能够并行执行而不需要相互等待。

可以得到索引如下加锁示意图

索引上添加锁

锁总是锁定索引记录。如果要锁定的是最后一条记录之后的区间，防止有人在这个区间插入数据，那么mysql就会锁定隐藏的最大记录

索引记录关联的锁

4 解决方法

1、查看死锁日志时，先看一下发生死锁的事务等待获取锁的语句，
都有哪些语句发生死锁。

2、根据死锁语句，找到相关到业务代码（如果有日志，直接根据日志找到业务代码也行）。

3、根据业务代码执行流程，来分析死锁发生过程。（注意分析数据存在，数据不存在时的加锁区别）

发现了问题原因，那么解决方案就很简单了。在这个场景下是：先查询数据是否存在，如果数据存在则更新，如果数据不存在再插入。

5 总结

  • 两个事务即使生成的间隙锁的范围是一样的，也不会发生冲突，因为间隙锁目的是为了防止其他事务插入数据，因此间隙锁与间隙锁之间是相互兼容的。

  • 在执行插入语句时，如果插入的记录在其他事务持有间隙锁范围内，插入语句就会被阻塞，因为插入语句在碰到间隙锁时，会生成一个插入意向锁，然后插入意向锁和间隙锁之间是互斥的关系。

关于作者
黄培祖 采货侠JAVA开发工程师

转转研发中心及业界小伙伴们的技术学习交流平台，定期分享一线的实战经验及业界前沿的技术话题。
关注公众号「转转技术」（综合性）、「大转转FE」（专注于FE）、「转转QA」（专注于QA），更多干货实践，欢迎交流分享~

转转技术团队
关注
  • 0
    点赞
  • 踩
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql 播入和更新死锁_大招落地：MySQL 插入更新死锁的根因分析
weixin_28762271的博客
01-19 175
读者反馈了一个死锁案例，比较有意思，我分析总结了一篇文章。需要一些基础，下面是我在掘金上写的另外五篇调试源码分析锁的文章，可以顺便看看：下面开始真正的内容：建表语句：CREATE TABLE `tenant_config` (`id` bigint(21) NOT NULL AUTO_INCREMENT,`tenant_id` int(11) NOT NULL,`open_card_point` ...
mysql并发插入死锁
qq_35425070的博客
11-18 3420
mysql并发插入死锁 场景 多个线程同时执行条件插入语句 SQL： INSERT INTO system_lock (resource, owner, token, version, lock_time, release_time) SELECT ?, ?, ?, 0, ?, ? FROM DUAL WHERE NOT EXISTS(SELECT resource FROM system_lock WHERE resource = ?) 状态跟踪 执行命令查看 innodb 状态 show engine
Mysql并发插入死锁问题
https://gitee.com/hello-piper/PiperChat
05-16 2196
比如有1和5两条记录，两个事务分别插入2和3，插入之前都会使用插入意向锁锁定1-5的间隙，但是因为是不同的行，所以不需要互相等待。间隙锁：间隙锁是在索引记录之间的间隙上的锁，或者是第一条记录之前或最后一条记录后的间隙上的锁。for update的用处，第一是对记录加X锁，第二是可以当前读，也就是在可重复读隔离级别下，可以读到其他事务已提交的数据。for update 不存在数据的时候，加的锁是间隙锁吗？结果：同一个事务的间隙锁和插入意向锁不会互斥，两个事务之间的间隙锁和插入意向锁会互斥。
【MySQL系列】记插入产生的死锁问题
热门推荐
檀越的博客
07-12 3万+
💝💝💝欢迎来到我的博客，很高兴能够在这里和您见面！希望您在这里可以感受到一份轻松愉快的氛围，不仅可以获得有趣的内容和知识，也可以畅所欲言、分享您的想法和见解。非常期待和您一起在这个小小的网络世界里共同探索、学习和成长。💝💝💝 ✨✨ 欢迎订阅本专栏 ✨✨。
Mysql插入删除死锁问题排查
qq_42254413的博客
02-11 5033
搞懂Mysql插入删除是如何加锁的
mysql 播入和更新死锁_大招落地：MySQL 插入更新死锁源码分析
weixin_39703468的博客
01-27 54
今天再来分析一个死锁场景。下面开始真正的内容。建表语句：CREATE TABLE `tenant_config` (`id` bigint(21) NOT NULL AUTO_INCREMENT,`tenant_id` int(11) NOT NULL,`open_card_point` int(11) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `uid...
mysql批量更新 死锁_[转]MySQL批量更新死锁案例分析
weixin_28881787的博客
01-25 166
文章出处：http://blog.csdn.net/aesop_wubo/article/details/8286215问题描述在做项目的过程中，由于写SQL太过随意，一不小心就抛了一个死锁异常，如下：com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to ge...
[笔记]MySQL 插入导致死锁
永远相信美好的事情即将发生
09-13 1972
线上遇到的 MySQL 插入导致死锁,问题排查.
mysql 批量更新 死锁_MySQL批量更新死锁案例分析
weixin_35733586的博客
01-19 450
问题描述在做项目的过程中，由于写SQL太过随意，一不小心就抛了一个死锁异常，如下：com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:Deadlockfoundwhentryingtogetlock;tryrestartingtransactionatsun.reflect.GeneratedCo...
MySQL数据库之Purge死锁问题解析
08-28
MySQL数据库之Purge死锁问题解析 MySQL数据库中Purge死锁问题是指在数据库中出现的死锁情况，导致数据库无法继续进行操作。Purge死锁问题是MySQL数据库中的一种常见问题，出现这种问题的原因有多种，例如记录锁、...
关于MySQL死锁问题的深入分析
09-09
在MySQL数据库中，尤其是使用InnoDB存储引擎时，死锁问题不容忽视。理解死锁的原理以及如何预防和解决死锁问题对于保持数据库系统的稳定运行至关重要。 ### 死锁的概念和产生原因 在MySQL中，死锁通常是由于多个...
MySQL——事务与存储过程（一）事务管理（3）事务的回滚
PAP
08-30 796
MySQL——事务与存储过程（一）事务管理（3）事务的回滚
mysql-day02
m0_64370841的博客
08-29 2265
【代码】mysql-day02。
MySQL 函数、约束、多表查询与事务详解
A_cot的博客
08-30 1725
在 MySQL 数据库中，函数、约束、多表查询和事务是非常重要的概念，它们可以帮助我们更好地管理和操作数据。本文将详细介绍这些概念，并通过代码演示来帮助你更好地理解。
如何在 MySQL 中使用 Socket 身份验证 ？
xiaochong0302的博客
08-31 1444
在 MySQL 和 MariaDB 中，可以使用 socket 身份验证，而不是传统的用户名和密码组合。这种方法既安全又方便，因为它使用的是操作系统的用户凭证。
【MySQL】索引使用规则——（覆盖索引，单列索引，联合索引，前缀索引，SQL提示，数据分布影响，查询失效情况）
最新发布
YYDsis的博客
08-31 1301
一.索引使用规则 ※.验证索引效率提升 1.覆盖索引——查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到2.单列索引＆联合索引3.前缀索引——解决冗长字符串与索引问题【1】前缀索引＆索引选择性的介绍【2】前缀索引创建演示：5.SQL提示——指定某个索引/忽略索引/强制索引 6.数据分布影响——MySQL自我评估7.查询失效的几种情况【1】违背——最左前缀法则(联合索引)【2】范围查询右侧失效【3】用or分割开的条件，or后面没索引，所有索引失效【4】索引列上进行运算操作，索引
MySQL空间函数ST_Distance_Sphere()的使用
程序人生
08-29 1338
MySQL提供了一系列的空间函数，其中ST_Distance_Sphere()函数可以计算两个经纬度坐标之间的距离，返回单位为米的浮点数。这个函数使用的是球形大地模型，因此对于较短的距离，计算结果的精度比较高。注意，在使用ST_Distance_Sphere()函数时，参数必须是经度和纬度的点对象。函数来创建表示经纬度坐标的点对象，然后将这两个点作为参数传递给。这个函数会计算这两个点之间的距离，并将结果作为。在上面的SQL中，我们将经度和纬度的值作为参数传递给。函数，然后将返回的点对象作为参数传递给。
MySQL——基础操作（1）
hu_143的博客
08-31 1520
MySQL简单操作，包括增删查改等~~~
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值