第二十章 幻读是什么,幻读有什么问题?_幻读 删除-程序员宅基地

技术标签: MySQL高级  mysql  binglog  可重复读  幻读  事务  

第二十章 幻读是什么,幻读有什么问题?

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
  • 下面的语句序列,是怎么加锁的,加的锁又是什么时候释放的呢 ?
begin;
select * from t where d=5 for update;
commit;
  • select ... for update
    • 对于 非索引字段 进行 updateselect .. for update 操作,代价极高。所有记录上锁,以及所有间隔的锁
    • 对于 索引字段 进行上述操作,代价一般。只有 索引字段 本身和 附近的间隔 会被加锁
    • select ... for update 是为了在查询时,对这条数据进行加锁,避免其他用户以该表进行插入、修改或删除等操作,造成表的不一致性

关于 “这个语句会命中 d=5 的这一行,对应的主键 id=5,因此在 select 语句执行完成后,id=5 这一行会加一个写锁,而且由于两阶段锁协议,这个写锁会在执行 commit 语句的时候释放” 这句话的理解

由于字段 d 上没有索引,因此这条查询语句会做全表扫描。那么,其他被扫描到的,但是不满足条件的 5 行记录上,会不会被加锁呢?

  • RR (可重复读) 级别:扫描到的数据都会加 行锁 和 间隙锁
  • RC (读提交) 级别:扫描到的数据都会加行锁,但是不满足条件的数据,没有到 commit 阶段,就会被释放,违反了两阶段加锁原则
  • 全表扫描一直指的是扫描主键索引

什么是幻读 ?

InnoDB 的默认隔离级别是 可重复读

share in mode:当前读,给数据加读锁

for update:当前读,给数据加写锁

什么是幻读 ?

  • 在同一个事务中,两次读取到的数据不一致的情况,称为 幻读不可重复读
  • 幻读是针对 insert 导致的数据不一致
  • 不可重复读是针对 deleteupdate 导致的数据不一致
  • 可重复读 隔离级别下,事务内查询用 “当前读”,读到本事务外新增的数据,称为 幻读
  • 幻读 是用户在使用 可重复读 隔离级别下,在进行 select 查询时加 IX锁、IS锁才可能出现的,是用户主动打破业务层面上的查询时的一致性视图隔离性
  • 其不属于事务隔离的可见性规则问题(可见性规则是来解决各种事务问题的),而是用户在使用上出现的问题,属于业务问题

什么是快照读 ? 什么是当前读 ?

  • 当前读指的是 select for update 或者 select in share mode ,指的是在更新之前必须先查询当前的值,因此叫当前读
  • 快照读指的是在语句执行之前或者在事务开始的时候会创建一个视图,后面的读都是基于这个视图的,不会再去查询最新的值

当前读 具体包括哪些操作 ?

  • for updatelock in share modeupdatedeleteinsert 都是当前读的规则,就是读取最新的已经提交的数据
  • update 先查询再修改,这里的查询就是使用的当前读
  • delete 要先查询再删除,这里的查询也是要当前读
  • insert 的时候,要判断主键是否已经存在、是否违反唯一约束,此时查看主键是否存在的查询也是当前读

幻读 和 脏读 的区别 ?

  • 幻读是读到了提交了的数据,而脏读是读到了没提交的脏数据
  • 读提交 的隔离级别下,没有讨论幻读的实际意义
  • 而在 可重复读 隔离级别下,当前读 打破了视图的隔离限制,实现了读到不应该读的数据的作用

可重复读 的隔离级别下,幻读只会在查询为哪种性质时才会出现 ?

  • 由于 一致性视图 的作用,因此幻读只会在 “当前读” 情况下发生

幻读 跟 事务的可见性规则 冲突吗 ?

  • 可重复读 隔离级别下,幻读是用户选择使用 当前读 而产生的,符合 当前读 的规则,也不跟事务的 可见性规则 相矛盾

幻读有什么问题 ?

select .. lock in share modeselect ... for update 的区别 ?

  • select .. lock in share mode 走的是 IS锁(意向锁)
    • 即在符合条件的 rows 上都加了共享锁,这样的话,其他 session 可以读取这些记录,也可以继续添加 IS 锁,但是无法修改这些记录直到你这个加锁的 session 执行完成 (否则直接锁等待超时)
  • select ... for update 走的是 IX锁 (意向排它锁)
    • 即在符合条件的 rows 上都加了排它锁,其他 session 也就无法在这些记录上添加任何的IS锁IX锁
  • 如果不存在 一致性非锁定读的话,那么其他 session 是无法读取和修改这些记录的
  • 但是 innoDB 存在 非锁定读 (快照读并不需要加锁),for update 之后并不会阻塞其他 session 的快照读取操作
  • 除了 select ...lock in share modeselect ... for update 这种显示加锁的查询操作
  • 通过对比,发现 for update 的加锁方式无非是比 lock in share mode 的方式多阻塞了select...lock in share mode 的查询方式,并不会阻塞快照读

幻读 产生的原因 ?

  • 注意:binlog 日志是在 commit 提交时才进行记录的
  • 即使给所有行加上了锁,也避免不了幻读
  • 这是因为给行加锁的时候,这条记录还不存在,没法加锁
  • 例如下面这种情况:假设扫描到的行都被加上了 行锁

Untitled

  • 由于 session A 把所有的行都加了写锁,所以 session B 在执行第一个 update 语句的时候就被锁住了,需要等到 T6 时刻 session A 提交以后,session B 才能继续执行
  • binlog 里面,执行序列是这样的:
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/

update t set d=100 where d=5;/*所有d=5的行,d改成100*/

update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/
  • 按照日志顺序执行,id=0 这一行的最终结果也是 (0,5,5)。所以,id=0 这一行的问题解决了
  • 但是你可以看到,id=1 这一行,在数据库里面的结果是 (1,5,5),而根据 binlog 的执行结果是 (1,5,100),也就是说 幻读 的问题还是没有得到解决
    • 原因是:在 T3 时刻,我们给所有行加锁的时候,id=1 这一行还不存在,不存在也就加不上锁

如何解决幻读 ?

如何解决 幻读 ?

  • InnoDB 在 行锁 的基础上,引入了 间隙锁 (Gap Lock)
  • 间隙锁,锁的就是两个值之间的空隙
  • 比如文章开头的表 t,初始化插入了 6 个记录,这就产生了 7 个间隙
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

Untitled

  • MySQL 会对扫描经过的索引对象,加上间隙锁

行锁 和 行锁 之间会产生冲突,那 间隙锁 和 间隙锁 之间,也会产生冲突吗 ?

  • 行锁分为:读锁、写锁

Untitled

  • 行锁:读锁之间不冲突写锁与读锁冲突写锁与写锁冲突
  • 但是,跟间隙锁存在冲突关系的,是 “往这个间隙中插入一个记录” 这个操作
  • 间隙锁 与 间隙锁 之间,都不存在冲突关系

什么是 next-key lock ?

  • 间隙锁行锁 合称 next-key lock,每个 next-key lock前开后闭区间
  • 如果用 select * from t for update 要把整个表所有记录锁起来,就形成了 7 个 next-key lock,分别是 (-∞,0](0,5](5,10](10,15](15,20](20, 25](25, +supremum]
  • 间隙锁只是锁间隙,没有锁住记录行,而 next-key lock 就是间隙锁基础上锁住右边界行
  • 间隙锁:( ) 开区间
  • next-key lock:( ] 前开后闭区间
  • 如果是表的最后一行数据,则下一个区间是这行数据到表索引的不存在最大值,也就是 +supremum

引入 间隙锁 后,可能会导致什么问题 ?

  • 首先需要了解 间隙锁 的特性:锁的是间隙,是禁止其他操作往间隙中插入数据的。因此间隙锁与间隙锁之间不冲突
  • 所以在 AB 两个事务当中
  • A事务 随机查询某一条数据(+排它锁,也就是 for update )的方式产生间隙锁
  • B事务 也执行同一条SQL,随后 A事务 判定数据不存在时,对其插入,B事务也同样如此操作,则最终导致 A事务 等待 B事务 的间隙锁,B事务 也在等待 A事务 的间隙锁,这将导致死锁
  • 当然,由于 InnoDB 有开启死锁检测,最后 A事务 报错返回
  • 但是,间隙锁 会影响数据的并发度

除了引入 间隙锁, 还有什么方式可以解决幻读 ?

  • RR (可重复读) 改为 RC (读提交),则不存在幻读问题
  • 但此时需要将 binlog 格式改为 row,否则可能出现 数据 和 日志 不一致的问题

为什么 RC (读提交) 下,需要将 binlog 格式改为 row 呢 ?

  • RC 没有幻读问题,也没有间隙锁,但在显示控制事务更新
  • 若在 事务A 还未提交之前,有别的 事务B 后执行并且添加的数据是 事务A 的更新语句逻辑层面会命中的条件
  • 那等 事务A 提交之后,statement 的 binlog 记录上,事务B的记录在前,而事务A的记录在后(这没毛病,谁先提交谁先记录,但恢复的时候会产生问题)
  • 当执行日志恢复时,后提交的 事务A 的记录会把 事务B 的记录也进行更新,这就导致了数据恢复错误
  • 此时,如果改成 row 格式,binlog 会具体记录语句的各项条件,这样在恢复时便不会恢复错误了
  • 注意:除了恢复,还有主从库的同步也会有这个问题
  • 举个例子:
  • 例子一:
  • 删除 statement 记录的是这个删除的语句
delete from t where age>10 and modified_time<='2020-03-04' limit 1;
  • row 格式记录的是实际受影响的数据是真实删除行的主键id
delete from t where id=3 and age=12 and modified_time='2020-03-05';
  • 例子二:
-- statemnt格式
beginupdate t set d=5 where id=0;
commit;

-- row格式
beginupdate t where id=0 and c=0 and d=0
set id=0,c=0,d=5
commit;

关于row格式的参考资料

为什么要把MySQL的binlog格式修改为row

大家都用读提交,可是逻辑备份的时候,mysqldump 为什么要把备份线程设置成可重复读呢 ?

  • 官方自带的逻辑备份工具是 mysqldump
  • mysqldump 使用参数 –single-transaction 的时候,导数据之前就会启动一个事务,来确保拿到一致性视图
  • 因为 RR (可重复读) 的一致性读视图可以保证 数据备份时,不阻塞其他数据写入

在备份期间,备份线程用的是可重复读,而业务线程用的是读提交。同时存在两种事务隔离级别,会不会有问题 ?

  • 没有问题,不管是 读提交 还是 可重复读,都是 MVCC 支持
  • 备份是从某个快照时间之后开始的,数据是固定一致准确的

一条加了 排它锁 的查询语句,如果查询是全表扫描,那么扫描过的语句会如何 ?

  • 可重复读 隔离级别下,MySQL 会对扫过的语句加 next-key lock

总结

  • 重复读 更新时采用 当前读,这是为了防止数据丢失
  • 但使用 当前读,可能造成更新层面语义的破坏日志数据 不一致的问题
  • 为解决更新层面幻读 问题,可重复读时,引入了 next-key lock
  • 若是直接采取 读提交,则解决了语义层面的破坏,但需要通过将 binlog 改为 maxrow 的形式,防止日志数据不一致
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/sj15814963053/article/details/123599828

智能推荐

你知道slice()、splice()、substr()、substring()的区别吗?_js中slice,substring,substr,splice区别-程序员宅基地

文章浏览阅读301次。这个问题在我初学js的时候就一直傻傻分不清楚,当时只大概知道有的可以操作字符串、有的可以操作数组、有的可以截取、有的可以插入、有的还能删除,有的是参数截取长度、有的参数是结束位置、有的参数可以是负数、有的不能接受负数参数······用的时候全靠试,瞎猫遇见死耗子,试对了就写上了,用了这么些年了,你要让我一一说出他们的特点,我还真的想一会儿。相信很多同学跟我一样,为了可以更好的记住他们,今天我来好好..._js中slice,substring,substr,splice区别

SSH无法连接到RHEL服务器_mac无法ssh到redhat8服务器-程序员宅基地

文章浏览阅读3.9k次。http://blog.swine123.com/archives/905.htmlSecureCRT无法连接上服务器了,重启了ssh服务,service sshd restart,依然不行。[root@localhost ~]# ssh [email protected]_exchange_identification: Connection closed by remote h_mac无法ssh到redhat8服务器

当AI开始“踢脏球”,你还敢信任强化学习吗?-程序员宅基地

文章浏览阅读388次。足球机器人排成一排向球门发起射击,但守门员却并没有准备防守,而是一屁股倒在地上开始胡乱摆动起了双腿。然后,前锋跳了一段十分令人困惑的舞蹈,跺跺脚,挥挥手...

问题3:AttributeError: ‘open3d.open3d.geometry.PointCloud‘ object has no attribute ‘voxel_down_sample‘_attributeerror: 'open3d.cpu.pybind.geometry.pointc-程序员宅基地

文章浏览阅读5.4k次,点赞2次,收藏4次。Issue:============== Code ===============================================import open3d as o3dpcd = o3d.io.read_point_cloud("apple_object_50.pcd")print("Downsample the point cloud with a voxel of 0.05")downpcd = pcd.voxel_down_sample(voxel_size=0_attributeerror: 'open3d.cpu.pybind.geometry.pointcloud' object has no attrib

robot cmd命令的运行-程序员宅基地

文章浏览阅读942次。1、robot 下运行 cmdOperatingSystem.Run${a} OperatingSystem.Run C:/Users/MC/Desktop/testbaidu/EOC_Test/power_off${power_port}.bat运行bat文件涉及的命令 如果需要路径 注意设置环境变量转载于:https://www.cnblogs.com/classi..._operatingsystem.run

2018年终学习工作总结&2019展望:状态海星,持续拼搏。-程序员宅基地

文章浏览阅读662次。欢迎关注天善智能,我们是专注于商业智能BI,人工智能AI,大数据分析与挖掘领域的垂直社区,学习,问答、求职一站式搞定!对商业智能BI、大数据分析挖掘、机器学习,python,R等数据领域感兴趣的同学加微信:tstoutiao,邀请你进入数据爱好者交流群,数据爱好..._2018年终工作总结

随便推点

Qt 6中的Qt Network_qt6 cmake 添加network-程序员宅基地

文章浏览阅读2.5k次,点赞6次,收藏4次。这篇文章将总结在Qt 6中Qt Network模块的最新更新和修改,以及未来可能的发展方向。_qt6 cmake 添加network

admin_socket: exception getting command descriptions: [Errno 2] No such file or directory问题解决-程序员宅基地

文章浏览阅读1.7w次,点赞2次,收藏8次。1、问题发生原因:我遇到这个问题是因为在之前安装了ceph-0.94.6的环境中重新安装ceph-10.2.5,只是卸载了ceph的包,没有对ceph的一些配置文件进行删除。[root@node-1 tmp]# ceph-deploy new node-1[ceph_deploy.conf][DEBUG ] found configuration file at: /root/.cephdeplo_admin_socket: exception getting command descriptions: [errno 2] no such file

鸿蒙OS电脑体验,华为鸿蒙OS体验抢先曝光!有多个更新版本,界面和安卓完全不同...-程序员宅基地

文章浏览阅读390次。华为自主研发的取代安卓系统的鸿蒙OS早在2016年就开始研发,后来因为谷歌功能被限制使用之后,华为宣布了自己的替代方案,鸿蒙OS系统。不过从华为官方发布了鸿蒙OS之后,华为手机却一直迟迟没有将鸿蒙OS公开,所以外界一直怀疑华为的鸿蒙OS能够代替安卓系统是华为吹的牛。不过细心的网友发现,华为鸿蒙OS已经开始在部分华为手机用户上升级安装,并且安装成功之后的鸿蒙OS的操作界面和安卓系统完全不同。有不少华..._鸿蒙电脑系统界面

windows系统安装资料汇总_windows server 2003 r2, standard x64 edition with -程序员宅基地

文章浏览阅读908次。常用博客:(IT行业)博客园CSDNO’REILLY(English)找到一个超级好的下载正统的地方MSDN,我告诉你Windows Server 2003下载的密钥点击这里Windows Server 2003 R2, Enterprise x64 Edition with SP2 - Disc 2 - VL (Simplified Chine_windows server 2003 r2, standard x64 edition with sp2 - disc 1 - vl (simplif

django官方教材(3, 4)_django 3 4-程序员宅基地

文章浏览阅读533次。我们接着上一小节继续官方教程:投票应用第3节:视图和模板 | 第4节:表单和通用视图我们的投票应用中,我们需要下列几个视图:问题索引页——展示最近的几个投票问题。问题详情页——展示某个投票的问题和不带结果的选项列表。问题结果页——展示某个投票的结果。投票处理器——用于响应用户为某个问题的特定选项投票的操作。编写更多的视图polls/views.pydef detail(r..._django 3 4

numpy的基本用法(六)——numpy array的copy_numpy.array中的copy-程序员宅基地

文章浏览阅读9.5k次。numpy的基本用法(六)——numpy array的copy_numpy.array中的copy