【Mysql】next-key 锁范围

原创 已于 2023-11-10 12:30:34 修改 · 938 阅读 · 4 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库

于 2023-11-10 11:24:09 首次发布
本文详细解释了Mysql中的next-key锁机制如何解决幻读问题，通过实例展示了等值查询、非唯一索引等场景下的锁范围和行为，以及如何导致死锁。涵盖了主键索引和非唯一索引的区别，以及limit语句对锁的影响。

背景

Mysql RR场景下通过next-key 锁解决了幻读的问题，而幻读通常是由 insert 新增的数据导致。所以next-key锁最终通过锁机制防止了一定条件下的新增数据从而解决了幻读问题。

规律

next-key锁可以由以下几条规律总结出锁范围

  • next-key会对查询过程中访问到的对象进行加锁
  • next-key锁通常是左开右闭的
  • 在唯一索引上做等值查询时，next-key锁会退化成行锁
  • 在做等值查询时，查询最后访问到的一个对象所加的next-key上不包含右节点，也就是变成了两边都是开区间
  • 在唯一索引上做范围查询时，会访问到下一个不满足条件的对象上（Mysql 5.7版本）

实例

表数据结构
id 列是主键，c列上有普通索引，表内数据如下

id c d
0 0 0
5 5 5
10 10 10
15 15 15
20 20 20
25 25 25

等值查询间隙锁

最低0.47元/天 解锁文章
MySQL Next-Key Lock 加锁范围总结
PixelNovaO的博客
09-19 409
间隙锁范围：Next-Key Lock 会在索引键之间的间隙上加上间隙锁，用于防止并发事务在同一间隙内插入新的索引键。) 之间的间隙上加上间隙锁。例如，如果有一个唯一索引（Unique Index）或主键（Primary Key）包含列 A、B 和 C，那么一个查询语句 WHERE A = 1 AND B = 2 AND C = 3 将会在索引键 (1, 2, 3) 上加上行锁。然后，会话 2 的后续查询语句在不同的索引键上加上了行锁，以及在索引键 (0) 和 (1) 之间的间隙上加上了间隙锁。
MySQL Next-Key Lock 锁定范围总结
MbfrDeveloper的博客
09-29 434
在实际使用中，开发人员无需显式地使用Next-Key Lock，因为InnoDB存储引擎会自动根据查询条件和索引规则为相关记录加上适当的锁。原理：Next-Key Lock的原理是在索引记录之间的间隙上设置共享锁，以防止其他事务插入或修改可能破坏索引顺序的数据。锁定范围：Next-Key Lock锁定的范围是包含给定索引值的记录以及该索引值前后的间隙。通过使用Next-Key Lock，MySQL的InnoDB存储引擎能够提供高度的并发性和数据一致性，同时减少死锁的可能性。
MySQL间隙锁、Next-Key Lock主要知识点
zmflying8177的博客
03-20 1444
MySQL间隙锁、Next-Key Lock主要知识点
MySQL next-key lock 加锁范围是什么?
程序员小航
06-06 870
前言 某天，突然被问到 MySQL 的 next-key lock，我瞬间的反应就是： 这都是啥啥啥？？？ 这一个截图我啥也看不出来呀？ 仔细一看，好像似曾相识，这不是《MySQL 45 讲》里面的内容么？ 什么是 next-key lock A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record. 官网的解释大概意思
MySQL中next-key lock 加锁范围是什么？
一只苟延残喘的卑微蝼蚁
01-03 218
MySQL中next-key lock 加锁范围是什么？
MySQL 通过 Next-Key Locking 技术（行锁+间隙锁）避免幻读问题
lgx211
10-16 1320
Next-Key Locking 是一种锁定区间行锁（Record Lock）：锁定精确的一行数据，防止其他事务对该行数据的修改。间隙锁（Gap Lock）：锁定一行数据之间的“间隙”，防止其他事务在该间隙中插入新数据。Next-Key Locking 锁住了当前查询的行及其“前后”的间隙，这样不仅可以防止已有记录的修改，还能防止在查询范围内插入新数据，避免了幻读问题。通过将行锁和间隙锁结合起来，解决了MySQL中隔离级别下的幻读问题。
MySQL高级----详细介绍MySQL中的锁
qq_63945982的博客
07-07 1178
详细介绍MySQL中的全局锁，表锁，元数据锁，意向锁，行级锁等。
RR模式下NEXT-KEY LOCK范围到底有多大
cri5768的博客
10-13 214
我的《深入理解MySQL主从原理》发布，具体可以点击：ttps://j.youzan.com/yEY_Xi 我们知道MYSQL NEXT-KEY LOCK是用来防止幻读，在RR模式下就有了用武之...
[mysql 学习笔记] - Next-key 锁
Chlapis的博客
02-13 1932
三种行锁算法 InnoDB 支持行锁，包含三种行锁算法： Record Lock: 单个记录上的锁； Gap Lock: 间隙锁，它锁住的是一个范围，不包含记录本身； Next-key Lock: 等效于 Record Lock + Gap Lock。 特别注意：InnoDB 行锁是通过 给索引上的索引项加锁来实现的。因此，在执行当前读的sql语句时，一定要记得带上索引条件，否则，mysql 将会执行全表扫描，将整个表锁住。 假设有一张测试表 t： create table t (a i
MySQL next-key lock 加锁范围总结
程序员小航
06-08 995
前言 三篇文章分别通过实际操作，介绍了主键、非主键唯一索引、普通索引、普通字段四个方面介绍了加锁的范围。 本篇文章再做一个总结。 data_locks select * from performance_schema.data_locks; LOCK_MODE LOCK_DATA 锁范围 X,REC_NOT_GAP 15 15 那条数据的行锁 X,GAP 15 15 那条数据之前的间隙，不包含 15 X 15 15 那条数据的间隙，包含 15 LOCK_MODE = X 是前
MySQL行锁、间隙锁、Next-Key锁
weixin_34376562的博客
02-09 1131
InnoDB是一个支持行锁的存储引擎，它有三种行锁的算法： Record Lock：行锁，单个行记录上的锁。 Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止幻读、防止间隙内有新数据插入、防止已存在的数据更新为间隙内的数据。 Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。InnoDB...
MySQL间隙锁，next-key锁
qq_36037795的博客
09-14 1489
间隙锁是对索引记录之间的间隙的锁，或者是对第一个索引记录之前或最后一个索引记录之后的间隙的锁。next-key锁锁定的范围为间隙锁+记录锁，
Innodb锁机制：Next-Key Lock 浅谈
qiuyepiaoling的专栏
11-18 860
原文：http://www.cnblogs.com/zhoujinyi/p/3435982.html 数据库使用锁是为了支持更好的并发，提供数据的完整性和一致性。InnoDB是一个支持行锁的存储引擎，锁的类型有：共享锁（S）、排他锁（X）、意向共享（IX）、意向排他（IX）。为了提供更好的并发，InnoDB提供了非锁定读：不需要等待访问行上的锁释放，读取行的一个快照。该方法是通过
【MySQL】说透锁机制（二）行锁 加锁规则 之 范围查询（你知道会锁表吗？）
天罡gg的专栏
12-15 5036
本文会按照 聚集集索->唯一索引->普通索引 的顺序 地毯式分析 范围查询中 = 的行锁情况，锁表分析在唯一索引 章节，万字长文，力求分析全面，很硬核全网独一份，别忘了收藏！ 当然如果落下什么欢迎大家评论指出！ 聚集索引 对于 聚集索引下的范围查询 =，无论是否组合，都会遵循如下规则： 所有匹配的索引记录：只有>= 的等值(=)匹配 上Record Lock，其它 上Next-key Lock; 对于 < 和
MySQL锁的类型及加锁范围
xsjzn的博客
08-27 1933
MySQL锁
【mysql 锁】Next-Key Lock 详解
最新发布
wamp0001的博客
07-15 900
在范围查询中，Next-Key Lock 可以保证事务多次查询的结果一致，避免其他事务在范围内插入或修改数据。通过合理使用 Next-Key Lock，可以在保证数据一致性的同时，平衡数据库的并发性能。隔离级别下为防止幻读（Phantom Read）而引入的一种锁机制，结合了。幻读是指事务在两次查询中发现其他事务插入了新数据。Next-Key Lock 是。假设表中存在索引值为。
MySQL范围查询中next-key lock优化问题
qq_18122705的博客
03-05 960
文章目录背景实验(MySQL v8.0.11)表结构无优化的情况有优化的情况问题 背景 《MySQL45讲》里提到间隙锁(gap lock)与next-key lock的区别，并提到了索引上发生等值查询时，向右遍历时且最后⼀个值不满⾜等值条件的时候， next-key lock退化为间隙锁。另外也阐述了在不等号查询（范围查询）中会存在等值查询所引起的退化。 然而在实测中发现相关实验出现了相互矛盾的...
什么是Mysql的next-key、插入缓冲、二次写、自适应哈希索引和预读
加油，努力，认真
04-18 1928
目录楔子间隙锁（Next-Key锁）插入缓冲(Insert Buffer)什么是insert buffer？insert buffer的触发条件？为什么不能是唯一索引？insert buffer的升级版change bufferInsert Buffer的实现辅助索引的插入过程Merged Insert Buffer的时机double write（二次写）什么是double writedouble write原理double write恢复流程doublewrite的负载监控double write工作负载
MySQL的间隙锁简析
及时雨的csdn
12-09 541
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next -Key锁）。举例来说，假如emp表中只有101条记录，其empid的值分别是1,2,...,100,101，下面的SQL：Select * from emp where emp id > 100 for update; 是一个范围条件的检索，InnoDB
mysql8.0.25 insert into select next-key锁
03-25
<think>嗯，用户想了解MySQL 8.0.25中使用INSERT INTO SELECT语句时涉及的next-key锁行为以及解决方法。我需要先回忆一下MySQL的锁机制，尤其是InnoDB的next-key锁。 首先，next-key锁是InnoDB在可重复读隔离级别下用来防止幻读的一种锁机制。它结合了记录锁和间隙锁，锁定一个范围。当执行INSERT INTO SELECT时，如果源表有数据被扫描，可能会对扫描到的范围加next-key锁，特别是当使用唯一索引或非唯一索引的时候。 然后，用户提到的版本是8.0.25，需要确认这个版本是否有相关的变化。根据引用[2]，MySQL 8.0.22开始，DML操作读取grant表时不加读锁，但这里讨论的是普通的表操作。可能需要查看MySQL官方文档关于锁的部分。 接下来，INSERT INTO SELECT在复制数据时，如果目标表有触发器或外键约束，或者源表有索引扫描，可能会导致更长时间的锁定，从而引发性能问题甚至死锁。比如，当从大表中SELECT数据插入到另一个表时，如果源表的索引范围被锁定，其他事务可能被阻塞。 解决方法可能包括调整事务隔离级别，比如使用读已提交来减少间隙锁的使用，或者优化索引，减少扫描范围。另外，分批处理数据可能有助于减少锁的持有时间。引用[3]提到MySQL 8.0提高了可靠性，可能在锁管理方面有所优化，但具体到8.0.25需要确认。 用户可能遇到的情况是，在执行INSERT INTO SELECT时，源表被长时间锁定，影响其他事务。需要解释这种情况下next-key锁的行为，并提供具体的解决方案，比如使用主键顺序扫描、降低隔离级别、拆分事务等。同时，结合用户提供的引用，可能引用相关版本文档来支持解答。</think>### MySQL 8.0.25中`INSERT INTO SELECT`的Next-Key锁行为分析 #### 1. Next-Key锁的基本机制 在InnoDB存储引擎中，`INSERT INTO SELECT`语句在**可重复读（REPEATABLE READ）**隔离级别下，若涉及索引范围扫描，可能触发Next-Key锁。Next-Key锁是记录锁（Record Lock）和间隙锁（Gap Lock）的组合，用于防止幻读（Phantom Read）[^2]。 例如： ```sql INSERT INTO target_table SELECT * FROM source_table WHERE indexed_column BETWEEN 100 AND 200; ``` 如果`source_table`的`indexed_column`是非唯一索引，InnoDB会对该范围内所有记录及间隙加锁，阻止其他事务插入符合此范围的新数据。 #### 2. 具体行为表现 - **主键/唯一索引扫描**：若`SELECT`条件使用主键或唯一索引精确匹配（如`WHERE id = 5`），仅加记录锁。 - **非唯一索引扫描**：若使用非唯一索引或全表扫描，会对扫描范围加Next-Key锁。 - **无索引扫描**：全表扫描时，可能对表中所有间隙加锁，导致严重锁竞争[^3]。 #### 3. 典型问题场景 ```sql -- 事务1（长时间运行） INSERT INTO orders_archive SELECT * FROM orders WHERE create_time >= '2023-01-01'; -- 事务2（被阻塞） INSERT INTO orders (create_time) VALUES ('2023-01-02'); ``` 若`orders.create_time`有非唯一索引，事务1会锁定`2023-01-01`之后的间隙，导致事务2被阻塞。 #### 4. 优化解决方案 ##### 方法1：调整隔离级别 将事务隔离级别改为**读已提交（READ COMMITTED）**，该级别下InnoDB不使用间隙锁： ```sql SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN; INSERT INTO target_table SELECT ...; COMMIT; ``` 注意：需评估幻读风险是否可接受。 ##### 方法2：索引优化 为`SELECT`条件添加高效索引，减少锁定范围： ```sql ALTER TABLE source_table ADD INDEX idx_column(column); ``` ##### 方法3：分批处理 使用主键范围分批操作，减少单次锁持有时间： ```sql INSERT INTO target_table SELECT * FROM source_table WHERE id BETWEEN 1 AND 1000; INSERT INTO target_table SELECT * FROM source_table WHERE id BETWEEN 1001 AND 2000; ``` ##### 方法4：使用`LIMIT`与循环 通过应用层控制分批逻辑： ```python last_id = 0 batch_size = 1000 while True: cursor.execute(f""" INSERT INTO target_table SELECT * FROM source_table WHERE id > {last_id} ORDER BY id LIMIT {batch_size} """) last_id = get_last_insert_id() if affected_rows < batch_size: break ``` #### 5. 监控锁状态 通过以下命令实时监控锁行为： ```sql -- 查看当前锁信息 SELECT * FROM performance_schema.data_locks; -- 查看阻塞关系 SELECT * FROM sys.innodb_lock_waits; ``` --- ###
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值