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

简聊MySQL并发事务中幻读、虚读问题的解决方案

        在MySQL数据库中,事务的幻读和虚读问题是并发控制中的关键挑战。以下是针对这两个问题的解决方案及原理说明,并附上相关示例。

一、幻读问题及其解决方案

  1. 幻读问题的定义

        幻读是指一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行,这通常是由于其他事务在这个范围内插入了新的数据。

  1. 解决方案及原理

    • 提高事务隔离级别:将事务的隔离级别设置为串行化(Serializable)可以彻底避免幻读问题。在这种隔离级别下,事务会完全隔离,其他事务无法在其执行期间插入新的数据。然而,这种解决方案会导致性能显著下降,因为串行化隔离级别会限制并发性。
    • 使用MVCC(多版本并发控制):MVCC通过为每个事务分配一个唯一的事务ID和版本号,来保证每个事务读取到的数据都是一致的。当一个事务开始时,它会生成一个快照,后续的读取操作都会从这个快照中获取数据,从而避免了幻读问题。MySQL在可重复读(Repeatable Read)隔离级别下,通过MVCC机制来减少幻读问题的发生。
    • 引入Next-Key Lock:在InnoDB存储引擎中,除了行锁之外,还引入了间隙锁(Gap Lock)和Next-Key Lock来解决幻读问题。Next-Key Lock是行锁和间隙锁的组合,它锁定了一个范围,包括索引记录以及它们之间的空隙。当执行范围查询并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁,同时也会对键值在条件范围内但并不存在的记录(即间隙)加锁。这样,其他事务就无法在这个范围内插入新的数据,从而避免了幻读问题。
  2. 示例

    • 假设有一个名为products的表,其中包含idname两列。
    • 事务A执行查询操作:SELECT * FROM products WHERE id > 100;
    • 事务B在事务A查询的范围内插入一条新的数据:INSERT INTO products (id, name) VALUES (150, 'New Product');并提交事务。
    • 事务A再次执行相同的查询操作:SELECT * FROM products WHERE id > 100;
      • 在没有使用间隙锁的情况下,事务A的第二次查询将会返回新增的数据,导致幻读的问题出现。
      • 如果在事务A的查询语句中加入FOR UPDATE,即 SELECT * FROM products WHERE id > 100 FOR UPDATE;,这样事务A在读取数据的同时,会对查询范围内的间隙进行锁定,从而阻止了其他事务的插入操作,避免了幻读的发生。

二、虚读问题及其解决策略

        虚读问题通常是指在可重复读(Repeatable Read)隔离级别下,一个事务读取到另一个事务已经提交但尚未对当前事务可见的数据。然而,在MySQL的InnoDB存储引擎中,由于实现了MVCC机制,实际上在可重复读隔离级别下并不会发生虚读问题。因为MVCC确保事务读取到的数据是事务开始时的快照,即使其他事务在之后对数据进行了修改或提交,也不会影响到当前事务的读取结果。

三、总结

        MySQL通过提高事务隔离级别、使用MVCC机制以及引入Next-Key Lock等策略,有效地解决了幻读问题。同时,由于MVCC机制的实现,MySQL在可重复读隔离级别下实际上并不会发生虚读问题。在实际应用中,开发者需要根据具体的业务场景和需求选择合适的解决方案,以确保数据的一致性和系统的性能。

四、解释“SELECT ...... FOR UPDATE;

        当您在MySQL的InnoDB存储引擎中使用SELECT ... FOR UPDATE语句时,如果查询条件涉及范围查询(如id > 100),InnoDB会自动为该查询添加Next-Key Lock。Next-Key Lock是InnoDB实现的一种锁策略,它结合了行锁(Record Lock)和间隙锁(Gap Lock)来避免幻读问题。

具体来说,当您执行SELECT * FROM products WHERE id > 100 FOR UPDATE;时,InnoDB会:

  1. 对满足条件id > 100的每一行数据加上行锁(Record Lock),确保其他事务不能修改或删除这些行。
  2. 同时,InnoDB还会对id值在100到查询结果中最小id值之间的间隙(Gap)加上间隙锁(Gap Lock),以及查询结果中最大id值到正无穷大之间的间隙加上间隙锁(如果存在的话,实际上在大多数情况下,我们不会关心正无穷大这个边界的间隙锁,因为它不影响插入操作)。这样,其他事务就不能在这个间隙内插入新的数据行。

通过结合行锁和间隙锁,Next-Key Lock能够确保在事务执行期间,查询结果集不会被其他事务修改或插入新行,从而避免了幻读问题。

需要注意的是,虽然SELECT ... FOR UPDATE会添加Next-Key Lock,但只有在事务隔离级别为可重复读(Repeatable Read)或更高(实际上是串行化,但串行化通常不会用于实际应用中,因为它会极大地降低并发性能)时,这种锁策略才会生效。在读已提交(Read Committed)隔离级别下,InnoDB不会使用间隙锁,因此可能会遇到幻读问题。

五、注意navicat中测试记录锁Gap Locks示例:

        确保事务隔离级别是RR: 

1、打开两个查询窗口:

        相当于开启了两个事务;

        窗口1是开启事务并加记录锁;

        窗口2是执行update、insert、delete等DML操作;

窗口1内容:        

        解释:测试时, “运行已选择的” 图表灰色不可点击说明是开启事务成功;“停止”图表亮起,表示添加行锁记录锁;阻塞了,其他窗口就不能对此行记录进行操作了。

窗口2内容:           

         解释:同窗口1的解释。执行update语句没有成功,在等待其他事务释放锁。

2、两个窗口分别关掉停止

窗口1关掉停止,显示: 

SELECT * FROM EMP WHERE ID=1001 FOR  UPDATE
> 1205 - Lock wait timeout exceeded; try restarting transaction
> 时间: 50.591s

窗口2关掉停止,显示: 

update  EMP set ename='天天向上杰4'  where  ID=1001
> 1205 - Lock wait timeout exceeded; try restarting transaction
> 时间: 51.439s

3、注意⚠️错误示范

        千万不要在一个查询窗口中执行啊,切记!错误示范:

              (有不对的地方,大家多多批评,并发表出您的真知,感谢)  上述这样不会执行成功的。因为在同一个会话中,执行语句的顺序是从上到下,相当于在极短的时间内开启事务后,迅速就提交了。在按照顺序执行过程中,等不及锁加载和阻塞,直接就提交执行下一个事务了。 

        用plsqldev.exe操作Oracle时是很好测试的。(略)

        祝大家测试成功!

六、间隙锁简易示例:

间隙锁用于锁定索引记录之间的“间隙”,但不锁定索引记录本身。

示例

  • 假设表your_tableid字段是索引,且有值1、3、5。事务1开始,查询id在1到3之间(不包含1和3)的数据并加锁,会在这个间隙上加间隙锁:        
    • SELECT * FROM your_table WHERE id > 1 AND id < 3 FOR UPDATE;
  • 事务2开始,尝试插入id = 2的数据(会被阻塞,因为有间隙锁):
    • INSERT INTO your_table (id, name) VALUES (2, 'new_entry');

在这个例子中,事务1在1和3之间的间隙上加了间隙锁,事务2尝试插入id = 2的数据时就会被阻塞。

(望各位潘安、各位子健不吝赐教!多多指正!🙏)


http://www.kler.cn/a/504213.html

相关文章:

  • 【论文阅读】SDA-FC: Bridging federated clustering and deep generative model
  • 深入浅出 Android AES 加密解密:从理论到实战
  • LabVIEW智能水肥一体灌溉控制系统
  • NVIDIA CUDA Linux 官方安装指南
  • 日志系统实践
  • 【9.1】Golang后端开发系列--Gin快速入门指南
  • GPU算力平台|在GPU算力平台部署Qwen-2通义千问大模型的教程
  • sniffer 日志分析吞吐问题
  • 结合night compute分析 利用tensor core 优化K值较大的矩阵乘(超过cublas50%)
  • vue的KeepAlive应用(针对全部页面及单一页面进行缓存)
  • 精通Python (10)
  • 【Linux】8.Linux基础开发工具使用(2)
  • React中的key有什么作用?
  • RabbitMQ-消息入队
  • HarmonyOS NEXT应用开发边学边玩系列:从零实现一影视APP (二、首页轮播图懒加载的实现)
  • SQL刷题快速入门(二)
  • ClickHouse-CPU、内存参数设置
  • 在Linux系统中无网络安装Nginx并配置负载均衡
  • 41_Lua函数
  • uniapp小程序开发,配置开启小程序右上角三点的分享功能
  • 【搭建JavaEE】(1)maven仓库安装配置
  • Vue.js前端框架教程16:Element UI的el-dialog组件
  • WordEmbeddingPositionEmbedding
  • uni-app的学习
  • MySQL:内置函数
  • SQL Server 查看数据库表使用空间 系统表