mysql怎么解决幻读问题(详解MySQL幻读及如何消除)
mysql怎么解决幻读问题
详解MySQL幻读及如何消除这是一篇数据库隔离级别的科普文章,旨在了解数据库中著名的幻读现象,为了专注,对脏读、不可重复读不作讨论。
事务隔离级别
mysql有四级事务隔离级别:
读未提交 read-uncommitted: 存在脏读,不可重复读,幻读的问题
读已提交 read-committed:不存在脏读,但存在不可重复读,幻读问题
可重复读 repeatable-read:不存在脏读,不可重复读问题,但存在幻读问题
序列化serializable:解决脏读,不可重复读,幻读问题,但完全串行执行,性能最低
什么是幻读
幻读错误的理解:说幻读是事务a 执行两次 select 操作得到不同的数据集,即 select 1 得到10条记录,select 2 得到11条记录。这其实并不是幻读,这是不可重复读的一种,只会在 r-u r-c 级别下出现,而在 mysql 默认的 rr 隔离级别是不会出现的。
这里给出我对幻读的理解:
幻读,并不是说事务中多次读取获取的结果集不同,幻读更重要的是某次的 select 操作得到的结果集所表征的数据状态无法支撑后续的业务操作。更为具体一些:select 记录不存在,准备插入此记录,但执行 insert 时发现此记录已存在,无法插入,如同产生了幻觉
举个例子可能会简化理解:
|
mysql> show create table user \g *************************** 1. row *************************** table : user create table : create table ` user ` ( `id` int (11) not null , ` name ` varchar (32) default null , primary key (`id`) ) engine=innodb default charset=utf8 |
分别开启两个事务t1 & t2,并设置其隔离级别为reaptable-read:
t1:
|
mysql> set global transaction isolation level repeatable read ; mysql> begin ; mysql> select * from user ; mysql> insert into user values (1, 'jeff' ); error 1062 (23000): duplicate entry '1' for key 'primary' mysql> select * from user ; |
t2:
|
mysql> set global transaction isolation level repeatable read ; mysql> begin ; mysql> insert into user values (1, 'jeff' ); mysql> commit ; |
t1 事务检测表中是否有 id 为 1 的记录,没有则插入
t2 插入干扰记录,造成t1出现幻读。
上例中需要确保t1事务执行begin后才开始执行事务t2。
上例中t1就发生了幻读,因为 t1读取的数据状态与后面的动作发生了语义上的冲突:查询的时候明明提示记录不存在,插入的时候去提示主键重复,类似于出现幻影,因而称之为幻读。
如何消除幻读
mysql当前有两种方式可以消除幻读:
1. 通过对select操作手动加行x锁(select ... for update )。原因是innodb中行锁锁定的
是索引,纵然当前记录不存在,当前事务也会获得一把记录锁(记录存在就加行x锁,不
存在就加next-key lock间隙x锁),这样其他事务则无法插入此索引的记录,杜绝幻
读。
2. 进一步提升隔离级别为serializable
测试一下效果
|
mysql> begin ; mysql> select * from user where id = 2 for update ; mysql> insert into user values (2, 'tony' ); mysql> commit ; |
t2:
|
mysql> begin ; mysql> insert into user values (2, 'jimmy' ); error 1062 (23000): duplicate entry '2' for key 'primary' |
现在t1查询时携带了for update,在innodb内会对该索引加锁(即使当前不存在),于是事务t2的insert会被阻塞直到t1显示提交,这样t1成功了,对于t1来说,幻读确实被消除了,但t2的插入会报主键重复,这也符合预期。
至于另外一种提升隔离级别消除幻读的方式感兴趣的可以自己尝试,这里不再重复,其本质是类似的,只是让系统代替了手工加锁。
总结
rr作为 mysql 事务默认隔离级别,是事务安全与性能的折中,正确认识幻读后,开发者便可以根据需求自行决定是否需要防止幻读。
serializable则是悲观的认为幻读时刻都会发生,故会自动的隐式的对事务所需资源加排它锁,其他事务访问此资源会被阻塞等待,故事务是安全的,但需要认真考虑性能。
innodb的锁是针对索引,这点需要引起注意。对行记录加锁,如果存在,加x锁,否则会加 next-key lock / gap 锁 / 间隙锁,故innodb可以实现事务对某记录的预先占用,只要本事务还在,其他事务就别想占有它。关于锁,后面还会再有专门的文章讨论。
以上就是详解mysql 幻读及如何消除的详细内容,更多关于mysql 幻读及消除的资料请关注开心学习网其它相关文章!
原文链接:https://zhuanlan.zhihu.com/p/360254683
- 2021-10-04 01:06:07
- mysql8.0.20安装教程图解(mysql 8.0.24 安装配置方法图文教程)
- mysql双向同步原理(详解MySQL的半同步)
- mysql中null的用法(为什么mysql字段要使用NOT NULL)
- mysql的sql语句优化5种方式(MySQL:五个常见优化SQL的技巧)
- mysqlset用法(mysql descDESCRIBE命令实例讲解)
- MySQL中TIMESTAMP和DATETIME
- mysql数据库导入错误怎么处理(MySQL数据库导入导出数据之报错解答实例讲解)
- 如何用mysql建立图书管理系统(图书管理系统的sqlserver数据库设计示例)
- mysqlupdate怎么设置(MySQL update 语句的正确用法)
- mysql如何检查索引失效(mysql索引失效的几种情况分析)
- mysql索引面试总结(Mysql数据库索引面试题程序员基础技能)
- mysql给字段加默认值(MySQL 如何处理隐式默认值)
- navicat连接mysql1045解决方法(Navicat 连接服务器端中的docker数据库的方法)
- mysql8.0如何配超时时间(解决MySQL8.0时区的问题步骤)
- mysqldump 命令详解(mysqldump你可能不知道的参数)
- 岳云鹏跟凤凰传奇谈心,说出了人生中最重要的三个人,这才成功(岳云鹏跟凤凰传奇谈心)
- 爱情可以当饭吃吗(爱情能当饭吃吗)
- Top 3 JSHS《运动与健康科学 英文 》跻身SCI体育学期刊世界前三(Top3JSHS运动与健康科学)
- 体坛传媒LOGO全新升级,多元发展迈出坚实步伐(体坛传媒LOGO全新升级)
- 超撩人治愈的绝美水彩,原来出自她之手 一笔一画令无数人沉醉(超撩人治愈的绝美水彩)
- 新手的勾线(新手的勾线)
热门推荐
- dedecms标签调用原理(DEDECMS栏目页开伪静态的方法apache服务器)
- h5抽奖的弹框制作(html5实现九宫格抽奖可固定抽中某项奖品)
- nginx反向代理性能(nginx作grpc的反向代理踩坑总结)
- react组件参数(浅析React中的受控组件和非受控组件)
- IIS8.0安装配置完整图文教程 IIS8.0的Web Server asp设置(IIS8.0安装配置完整图文教程 IIS8.0的Web Server asp设置)
- 浏览器内核有哪些
- 使用 LINQPad 调试linq以及lambda表达式
- dedecms标签调用(dedecms获取文档当前栏目所在目录链接URL)
- javascript写计算器教程(基于JavaScript实现简易计算器)
- 腾讯云轻量和cvm的区别(腾讯云服务器CVM的购买及简单评测分析)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9