RR 隔离级别下删除不存在数据导致 【死锁】

间隙锁与死锁：防止幻读的原理与实例分析
原创 已于 2023-09-16 22:33:44 修改 · 436 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据库 #java #开发语言 #死锁 #RR

于 2023-05-14 16:22:54 首次发布
文章探讨了间隙锁在数据库中的作用，主要是为了防止幻读，阻止间隙内数据的插入和已有数据更新到间隙内。通过一个死锁场景的复盘，解释了当两个事务分别持有无限范围的间隙锁时，尝试插入数据可能导致死锁的情况。内容涉及数据库事务处理和InnoDB存储引擎的锁机制。

        如果删除索引的数据不存在，会出现一个间隙锁，区间（左边最近第一个值, 右边最近第一个值）, 间隙锁之间是不会冲突的，只有往间隙中插入数据的操作，才会被阻塞。

重温间隙锁目的：

间隙锁的目的是为了防止幻读，其主要通过两个方面实现：

（1）防止间隙内有新数据被插入

（2）防止已存在的数据，更新成间隙内的数据（例如防止numer=3的记录通过update变成number=5）

死锁场景流程复盘：开启事务，任务数据先执行批量删除操作，然后执行插入操作， 出现死锁。

数据表


CREATE TABLE `task` (
  `uid` int(11) NOT NULL,
  `task_id` int(11) NOT NULL,
  `state` int(11) DEFAULT NULL,
  `sub_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`uid`,`task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='任务数据';

  1.  空表的情况下， 删除操作都可以，从提示看没有改动数据

   2.  开始分别插入一些数据， 左边阻塞了，然后我们看锁表的信息

  3.  查看INNODB_LOCKS 表

suprenum 表示超过索引中的最大值，也就是说 两个事务的间隙锁 范围都是  （-∞, +∞），左边 执行insert 的事务正在等待右边释放锁，这时候如果右边事务也执行insert 就死锁了……

【MySQL】深入理解MySQL事务隔离级别与锁机制
金鳞踏雨
10-31 1359
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁 合理设计索引，尽量缩小锁的范围 尽可能减少检索条件范围，避免间隙锁 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的SQL尽量放在事务最后执行 尽可能低级别事务隔离
读懂MySQL事务的四大隔离级别
Code365
06-22 1073
----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | Jay | 100 | +----+------+---------+ 1 row in set (0.00 sec) 复制代码。可以发现，事务B执行插入操作时，阻塞了~因为事务A在执行select ... lock in share mode的时候，不仅在 id = 3,4 这2条记录上加了锁，而且在id > 2 这个范围上也加了间隙锁。
Mysql insert操作死锁
airwxw的专栏
12-05 824
前段时间系统老是出现insert死锁，很是纠结。经过排查发现是间隙锁！间隙锁是innodb中行锁的一种， 但是这种锁锁住的却不止一行数据，他锁住的是多行，是一个数据范围。间隙锁的主要作用是为了防止出现幻读，但是它会把锁定范围扩大，有时候也会给我们带来麻烦，我们就遇到了。 在数据库参数中， 控制间隙锁的参数是：innodb_locks_unsafe_for_binlog， 这个参数默认值是OFF...
MYSQL 8.0 -- 事务中删除不存在的记录导致死锁
zhangdm的博客
12-16 2448
死锁
MySQL：RR分析死锁一列
cri5768的博客
03-14 331
水平有限 有误请指出 版本：Percona MySQL 5.7.22 对于锁的学习我做了一些输出详细参考如下： https://github.com/gaopengcarl/percona-server...
mysql隔离级别和死锁_MySQL在RR隔离级别下的unique失效和死锁模拟
weixin_28933797的博客
01-28 209
今天在测试MySQL事务隔离级别的时候，发现了一个有趣的问题，也参考了杨一之前总结的一篇。http://blog.itpub.net/22664653/viewspace-1612574/问题的背景是在MySQL隔离级别为RR(Repeatable Read)时，唯一性约束没有失效，多并发的场景下能够复现出下面的问题。这样一个看起来不可能的事情，能否复现呢。我都这么问了，潜台词就是可以，要不今天的...
Mysql死锁场景总结 （引擎：innodb隔离级别：RR）
03-28 877
概述 死锁：死锁一般是事务相互等待对方资源，最后形成环路造成的。 此种场景常见于Springmvc模式中，把事务交由spring管理的场景。这种模式下，由于业务的比较复杂，会导致一个事务内会有多次和数据库进行通信的机会，导致事务一直没提交，产生大事务。 下面具体分析几类在工作中遇到过的死锁场景，主要介绍单表场景，死锁在多表场景中也有，可以按单表的思路进行分析。 死锁场景 一、upd...
MySQL事务 InnoDB下隔离级别为RR级别 解决幻读了吗
kl_Dreaming的博客
09-13 570
InnoDB在可重复读提交下为了解决幻读问题引入MVCC + 间隙锁（Gap Lock） 结合具体的实例
数据库隔离级别RC，什么场景会有间隙锁？
王小明的专栏
07-15 1797
在RC（Read Committed，读已提交）隔离级别下，通常不会有间隙锁，因为间隙锁主要是为了解决可重复读（Repeatable Read，RR）隔离级别下的幻读问题而引入的。然而，在某些特殊场景下，RC级别下也可能出现类似间隙锁的行为，但这通常不是通过间隙锁直接实现的，而是可能与MySQL的锁机制、索引使用、以及特定版本的优化策略有关。
并发性事务带来的问题+并发控制(MySQL事务隔离级别、MySQL 锁)
weixin_44040169的博客
07-15 1282
并发事务带来的问题——不可重复读、幻读和脏读 并发控制——解决方式： MySQL事务隔离级别 MySQL 锁（共享锁，排他锁，乐观锁，悲观锁） Spring事务 各种锁(ReentrantLock、AQS。。)
mysql 为什么默认的隔离级别是RR,间隙锁机制
weixin_38410177的博客
11-29 2851
首先查看mysql的当前事务隔离级别：select @@tx_isolation; 查看全局的事务隔离级别：SELECT @@global.tx_isolation; 修改事务隔离级别 MySQL 提供了 SET TRANSACTION 语句，该语句可以改变单个会话或全局的事务隔离级别。语法格式如下： SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATAB
面试官：MySQL 什么时候会出现死锁问题？为什么不推荐使用RR隔离级别？
qq_45260619的博客
10-06 1674
欢迎关注公众号，持续在我后台回复 「资料」 可领取编程高频电子书！在我后台回复「面试」可领取硬核面试笔记！
面试官：解释下什么是死锁？为什么会发生死锁？怎么避免死锁？
代码界的扛把子
06-04 238
接着，事务 B 也插入 order_no 为 1006 的记录，由于事务 A 已经插入 order_no 值为 1006 的记录，所以事务 B 在插入二级索引记录时会遇到重复的唯一二级索引列值，此时事务 B 想获取一个 S 型 next-key 锁，但是事务 A 并未提交，事务 A 插入的 order_no 值为 1006 的记录上的「隐式锁」会变「显示锁」且锁类型为 X 型的记录锁，所以事务 B 向获取 S 型 next-key 锁时会遇到锁冲突，事务 B 进入阻塞状态。设置事务等待锁的超时时间。
mysql中删除了一条不存在的数据为什么造成死锁了呢？
流星007
07-22 1640
mysql中删除了一条不存在的数据造成死锁
预防 MySQL 死锁的策略
勤劳的小琪
04-01 1783
请注意，预防死锁是一个综合性的工作，需要结合具体的业务场景和数据库架构进行分析和优化。此外，对于复杂的数据库应用，可能需要专业的数据库管理员或性能优化专家来进行深入的调优和监控。较低的隔离级别（如 READ UNCOMMITTED）可以减少锁的粒度和竞争，但可能会导致数据不一致的问题。8、测试和模拟：在实际环境中进行测试，模拟高并发情况下的数据库操作，发现可能的死锁问题，并进行相应的优化。6、避免热点数据：如果某些数据经常成为锁的竞争焦点，可以考虑对这些数据进行分布或缓存，以减少锁的竞争。
记录一次Mysql死锁事件（由Insert与uniqueKey导致）
Diligence is the mother of success.
06-04 967
| 导语记录一次于2023年01月23日遇到的死锁问题。1、基础1.1 数据库隔离级别1.1.1RCREAD COMMITTED：只能读取已经提交的数据；此时：允许幻读和不可重复读，但不允许脏读，所以RC隔离级别要求解决脏读；1.1.2RRREPEATABLE READ：同一个事务中多次执行同一个select,读取到的数据没有发生改变；此时：允许幻读，但不允许不可重复读和脏读，所以RR隔离级别要求...
MYSQL隔离级别及死锁产生的业务场景复现
weixin_43582896的博客
05-11 928
之前一篇文章spring的传播机制和事务控制(https://blog.youkuaiyun.com/weixin_43582896/article/details/138500891),文章更多的是在代码层面的应用,抛开代码层面,最终还是在db中执行按照一定的逻辑顺序执行相应的sql语句来完成相应的业务开发。先了解什么是 锁机制 和 死锁?1.在MySQL的InnoDB 引擎中，锁机制是指MySQL数据库管理系统在处理并发访问时使用的一种机制，用于控制对数据库中数据的访问和修改。
oracle数据表造成死锁，解决方法
qingfengxia2013的专栏
02-13 1016
http://ajoker.iteye.com/blog/2185357今天早上查看数据表的时候发现，数据表中多了一条数据（这条数据不应该存在），像往常一样用delete where语句删除这条语句，中间卡了一下，然后一直显示正在执行中 先前也遇到过类似的情况，但是那时是因为，我删除数据没有提交事务，这次不一样，删除不了，一直卡在这里，点击取消后，有没有事务的提交与回滚。开 始怀疑是因为其它的人...
间隙锁(Gap Lock)
lj_heaven的博客
10-10 7727
关于间隙锁的3w法则
在可重复读隔离级别下如何彻底避免唯一键冲突？
最新发布
09-25
<think> 我们正在讨论在可重复读（Repeatable Read, RR）隔离级别下如何彻底避免唯一键冲突的问题。在RR级别下，MySQL（尤其是InnoDB引擎）通过MVCC和间隙锁（Gap Lock）来防止幻读，但唯一键冲突仍然可能发生，尤其是在高并发插入相同唯一键值的情况下。下面我将详细说明原因和解决方案。 ### 问题分析 唯一键冲突的根本原因是多个事务尝试插入具有相同唯一键（主键或唯一索引）的记录。在RR隔离级别下，虽然通过MVCC保证了可重复读，并且通过间隙锁防止了幻读（即其他事务插入新行导致的结果集变化），但以下情况仍可能导致唯一键冲突： 1. **事务内重复插入**：同一个事务内多次插入同一个键值，第二次插入会直接报错。 2. **并发插入相同键值**：两个事务同时插入相同的唯一键值，即使它们都先检查了该键值不存在，但由于检查时使用了快照读（非锁定读），而插入时使用当前读（锁定读），此时可能同时通过检查，然后插入时其中一个会失败。 ### 彻底避免唯一键冲突的方法 要彻底避免唯一键冲突，我们需要在业务逻辑和数据库操作上采取一些额外措施。以下方法在RR隔离级别下有效： #### 1. **使用`SELECT ... FOR UPDATE`显式加锁** 在插入之前，先使用`SELECT ... FOR UPDATE`查询该键值是否存在。这会施加一个排他锁（X锁），如果记录不存在，则会对间隙加间隙锁（Gap Lock），阻止其他事务在这个间隙中插入。这样，在插入操作执行前，当前事务已经锁定了可能插入该键值的间隙，其他事务无法插入相同的键值。 **操作步骤：** ```sql START TRANSACTION; -- 检查id=100是否存在，并对间隙加锁 SELECT id FROM table WHERE id = 100 FOR UPDATE; -- 如果上述查询返回空，则插入 INSERT INTO table (id, ...) VALUES (100, ...); COMMIT; ``` **注意：** - 如果`id=100`已经存在，那么`SELECT ... FOR UPDATE`会锁定该记录（行锁），其他事务无法修改或删除它，但不会阻止插入（因为记录已存在，插入本身会因为唯一键冲突而失败）。 - 如果`id=100`不存在，那么`SELECT ... FOR UPDATE`会对该间隙加锁（例如，如果id是主键，则会在前后两个存在的键值之间加间隙锁），阻止其他事务插入相同的id。 **缺点：** 在高并发场景下，这种加锁方式可能导致性能问题（锁竞争）。 #### 2. **使用`INSERT ... ON DUPLICATE KEY UPDATE`** 这个语句在插入时如果遇到唯一键冲突，则执行更新操作。它不会报错，而是转为更新。虽然不能完全避免冲突（冲突仍然发生，但被转化为更新操作），但可以避免插入失败。 ```sql INSERT INTO table (id, col) VALUES (100, 'value') ON DUPLICATE KEY UPDATE col = VALUES(col); ``` **注意：** 这不是避免冲突，而是处理冲突。在业务逻辑允许的情况下，这是一种优雅的解决方案。 #### 3. **使用数据库的唯一键约束** 唯一键约束是数据库保证唯一性的最可靠方法。任何违反唯一性的插入都会失败。因此，我们必须正确处理插入失败的情况（例如重试或转更新）。 **如何结合RR隔离级别使用：** - 依赖唯一键约束来捕获冲突（即捕获插入失败的错误）。 - 在业务逻辑中，当捕获到唯一键冲突错误时，进行重试或执行更新操作。 #### 4. **使用序列或自增主键** 如果唯一键是主键，可以考虑使用自增主键。这样，每个事务插入时会自动分配一个唯一的id，从而避免冲突。但这种方法只适用于主键，并且不适用于业务上的唯一键（比如用户名、邮箱等）。 #### 5. **分布式场景下使用全局唯一ID生成器** 在分布式系统中，为了避免多个节点生成相同的ID，可以使用全局唯一ID生成方案（如雪花算法、UUID等），确保生成的ID在分布式环境中唯一。 ### 总结 在可重复读隔离级别下，要彻底避免唯一键冲突，最可靠的方法是**在插入前使用`SELECT ... FOR UPDATE`显式加锁**，确保在插入之前锁定间隙，阻止其他事务插入相同的键值。同时，必须依赖数据库的唯一键约束作为最后防线，并在业务逻辑中处理插入失败的情况（如重试）。 > **关键点总结**： > 1. RR隔离级别通过间隙锁可以防止其他事务插入导致幻读，但无法避免**同一事务内的两次插入冲突**或**两个事务同时插入相同键值**（因为插入前的检查可能都认为键值不存在）。 > 2. 使用`SELECT ... FOR UPDATE`是RR级别下避免并发插入冲突的有效手段。 > 3. 唯一键约束是数据库层保证唯一性的终极机制，任何绕过它的尝试都是危险的。 §§相关问题§§ 1. 在RR隔离级别下，`SELECT ... FOR UPDATE`加锁的详细过程是怎样的？ 2. 如何在高并发场景下优化`SELECT ... FOR UPDATE`的性能？ 3. `INSERT ... ON DUPLICATE KEY UPDATE`在RR隔离级别下会产生死锁吗？如何避免？ 4. 除了数据库层面的解决方案，业务层如何设计幂等性操作来应对唯一键冲突？ </think> 在可重复读（Repeatable Read, RR）隔离级别下彻底避免唯一键冲突，需要结合数据库机制和业务设计策略。以下是经过验证的完整解决方案： ### 核心方法：显式锁定 + 插入优化 #### 1. **`SELECT ... FOR UPDATE` 显式锁定（关键手段）** ```sql BEGIN; -- 锁定可能冲突的间隙（即使记录不存在） SELECT * FROM users WHERE id = 100 FOR UPDATE; -- 对100所在间隙加锁 -- 此时其他事务无法插入id=100的记录 INSERT INTO users (id, name) VALUES (100, 'Alice'); COMMIT; ``` **实现原理**： - 在RR级别下，`FOR UPDATE` 会对查询范围加**间隙锁（Gap Lock）**，锁定 $[99,101)$ 的区间[^5] - 其他事务尝试插入相同ID时会被阻塞，直到当前事务提交 - **优势**：完全避免并发插入导致的唯一键冲突 #### 2. **`INSERT ... ON DUPLICATE KEY UPDATE`** ```sql INSERT INTO users (id, name) VALUES (100, 'Alice') ON DUPLICATE KEY UPDATE name = VALUES(name); ``` **特点**： - 原子性处理冲突（冲突时转为更新） - RR级别下自动加**排他锁（X锁）** 保护操作[^3] - **适用场景**：需要覆盖旧值的业务 #### 3. **`INSERT IGNORE` 静默处理** ```sql INSERT IGNORE INTO users (id, name) VALUES (100, 'Alice'); ``` **特点**： - 冲突时自动跳过，不报错 - 需配合 `ROW_COUNT()` 检查是否插入成功 - **风险**：可能掩盖业务逻辑错误 ### 辅助策略：业务层设计 #### 1. **分布式ID生成** - 使用雪花算法（Snowflake）生成全局唯一ID： $$ \text{ID} = \text{时间戳} \times 2^{22} + \text{机器ID} \times 2^{12} + \text{序列号} $$ - **优点**：彻底消除冲突可能 #### 2. **队列串行化** ```mermaid sequenceDiagram 客户端->>消息队列: 发送插入请求 消息队列->>数据库: 顺序处理请求 数据库-->>客户端: 返回结果 ``` - 通过Kafka/RabbitMQ等实现请求序列化 - **适用场景**：高并发注册/订单创建 #### 3. **乐观锁重试机制** ```python max_retry = 3 for _ in range(max_retry): try: execute_sql("INSERT INTO users ...") break except UniqueViolation: continue ``` ### 不同方法的有效性对比 | 方法 | 冲突避免 | 性能影响 | 实现复杂度 | 适用场景 | |------|----------|----------|------------|----------| | `SELECT FOR UPDATE` | ⭐⭐⭐⭐⭐ | （中） | 🟢（低） | 关键业务数据 | | `ON DUPLICATE UPDATE` | ⭐⭐⭐⭐ | 🟢（低） | （低） | 需要覆盖值的场景 | | 分布式ID | ⭐⭐⭐⭐⭐ | 🟢（低） | 🔴（高） | 分布式系统 | | 消息队列 | ⭐⭐⭐⭐⭐ | 🔴（高） | 🔴（高） | 超高并发场景 | ### 重要注意事项 1. **间隙锁的副作用**：当 `WHERE id=100` 的记录不存在时，`FOR UPDATE` 会锁定临近区间（如99-101），可能引发死锁[^5] 2. **索引设计**：确保唯一键有独立索引，锁范围更精确 3. **锁超时设置**：合理配置 `innodb_lock_wait_timeout` 避免长时间阻塞 > **终极建议**：对核心业务数据采用 **`SELECT FOR UPDATE` + 唯一键约束** 组合方案，既保证数据一致性，又利用数据库原生机制实现最高可靠性[^3][^5]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值