MySQL 实战 45 讲
林晓斌
网名丁奇,前腾讯云数据库负责人
224873 人已学习
新⼈⾸单¥68
登录后,你可以任选4讲全文学习
课程目录
已完结/共 49 讲
实践篇 (37讲)
特别放送 (1讲)
结课测试 (1讲)
MySQL 实战 45 讲
15
15
1.0x
00:00/00:00
登录|注册

20 | 幻读是什么,幻读有什么问题?

读提交隔离级别和binlog格式的配置
间隙锁的影响
间隙锁的冲突关系
间隙锁的引入
数据一致性问题
语义上的问题
必须使用可重复读隔离级别的场景
线上MySQL配置的隔离级别
配置选择的合理性
可重复读隔离级别
读提交隔离级别
加锁规则的详细讲解
加锁规则的预习问题
加锁规则的介绍
解决幻读的方法
幻读问题
幻读定义
实际场景和案例分析
隔离级别和配置
加锁规则
幻读问题

该思维导图由 AI 生成,仅供参考

在上一篇文章最后,我给你留了一个关于加锁规则的问题。今天,我们就从这个问题说起吧。
为了便于说明问题,这一篇文章,我们就先使用一个小一点儿的表。建表和初始化语句如下(为了便于本期的例子说明,我把上篇文章中用到的表结构做了点儿修改):
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);
这个表除了主键 id 外,还有一个索引 c,初始化语句在表中插入了 6 行数据。
上期我留给你的问题是,下面的语句序列,是怎么加锁的,加的锁又是什么时候释放的呢?
begin;
select * from t where d=5 for update;
commit;
比较好理解的是,这个语句会命中 d=5 的这一行,对应的主键 id=5,因此在 select 语句执行完成后,id=5 这一行会加一个写锁,而且由于两阶段锁协议,这个写锁会在执行 commit 语句的时候释放。
由于字段 d 上没有索引,因此这条查询语句会做全表扫描。那么,其他被扫描到的,但是不满足条件的 5 行记录上,会不会被加锁呢?
我们知道,InnoDB 的默认事务隔离级别是可重复读,所以本文接下来没有特殊说明的部分,都是设定在可重复读隔离级别下。

幻读是什么?

现在,我们就来分析一下,如果只在 id=5 这一行加锁,而其他行的不加锁的话,会怎么样。
下面先来看一下这个场景(注意:这是我假设的一个场景):
确认放弃笔记?
放弃后所记笔记将不保留。
新功能上线,你的历史笔记已初始化为私密笔记,是否一键批量公开?
批量公开的笔记不会为你同步至部落
公开
同步至部落
取消
完成
0/2000
荧光笔
直线
曲线
笔记
复制
AI
  • 深入了解
  • 翻译
    • 英语
    • 中文简体
    • 中文繁体
    • 法语
    • 德语
    • 日语
    • 韩语
    • 俄语
    • 西班牙语
    • 阿拉伯语
  • 解释
  • 总结

本文深入探讨了数据库中的幻读问题及其解决方案。作者以一个小表为例,通过对加锁规则的讨论引出了幻读问题,并分析了在可重复读隔离级别下可能导致的幻读现象。通过具体的例子和图示,生动地展示了幻读的产生和影响。文章强调了幻读是指在当前读下,后一次查询看到了前一次查询没有看到的行,而不是简单的新插入的行。此外,作者还探讨了幻读引发的语义和数据一致性问题,并提出了对幻读问题的解决方案。文章还介绍了间隙锁和next-key lock的引入,以及它们可能带来的并发度影响和死锁问题。最后,文章指出了在可重复读隔离级别下使用间隙锁解决幻读问题的同时,也需要考虑数据和日志一致性的问题,需要将binlog格式设置为row。总的来说,本文对读者快速了解幻读问题具有很好的指导意义,为解决幻读问题提供了深入的技术分析和解决思路。

仅可试看部分内容,如需阅读全部内容,请付费购买文章所属专栏
《MySQL 实战 45 讲》
新⼈⾸单¥68
立即购买
登录 后留言

全部留言(301)

  • 最新
  • 精选
  • 忍者无敌1995
    置顶
    老师之前的留言说错了,重新梳理下: 图8:间隙锁导致的死锁;我把innodb_locks_unsafe_for_binlog设置为1之后,session B并不会blocked,session A insert会阻塞住,但是不会提示死锁;然后session B提交执行成功,session A提示主键冲突 这个是因为将innodb_locks_unsafe_for_binlog设置为1之后,什么原因造成的?

    作者回复: 对, innodb_locks_unsafe_for_binlog 这个参数就是这个意思 “不加gap lock”, 这个已经要被废弃了(8.0就没有了),所以不建议设置哈,容易造成误会。 如果真的要去掉gap lock,可以考虑改用RC隔离级别+binlog_format=row

    2019-01-28
    8
    40
  • 令狐少侠
    置顶
    老师,今天的文章对我影响很大,发现之前掌握的知识有些错误的地方,课后我用你的表结构根据以前不清楚的地方实践了一遍,现在有两个问题,麻烦您解答下 1.我在事务1中执行 begin;select * from t where c=5 for update;事务未提交,然后事务2中begin;update t set c=5 where id=0;执行阻塞,替换成update t set c=11 where id=0;执行不阻塞,我觉得原因是事务1执行时产生next-key lock范围是(0,5].(5,10]。我想问下update set操作c=xxx是会加锁吗?以及加锁的原理。 2.一直以为gap只会在二级索引上,看了你的死锁案例,发现主键索引上也会有gap锁?

    作者回复: 1. 好问题。你可以理解为要在索引c上插入一个(c=5,id=0)这一行,是落在(0,5],(5,10]里面的,11可以对吧 2. 嗯,主键索引的间隙上也要有Gap lock保护的

    2018-12-28
    97
    96
  • 杜嘉嘉
    说真的,这一系列文章实用性真的很强,老师非常负责,想必牵扯到老师大量精力,希望老师再出好文章,谢谢您了,辛苦了

    作者回复: 精力花了没事,睡一觉醒来还是一条好汉😄 主要还是得大家有收获,我就值了😄

    2018-12-28
    7
    246
  • 薛畅
    可重复读隔离级别下,经试验: SELECT * FROM t where c>=15 and c<=20 for update; 会加如下锁: next-key lock:(10, 15], (15, 20] gap lock:(20, 25) SELECT * FROM t where c>=15 and c<=20 order by c desc for update; 会加如下锁: next-key lock:(5, 10], (10, 15], (15, 20] gap lock:(20, 25) session C 被锁住的原因就是根据索引 c 逆序排序后多出的 next-key lock:(5, 10] 同时我有个疑问:加不加 next-key lock:(5, 10] 好像都不会影响到 session A 可重复读的语义,那么为什么要加这个锁呢?

    作者回复: 是的,这个其实就是为啥总结规则有点麻烦,有时候只是因为代码是这么写的😓

    2018-12-29
    25
    88
  • Mr.Strive.Z.H.L
    看了@令狐少侠 提出的问题,对锁有了新的认识: 对于非索引字段进行update或select .. for update操作,代价极高。所有记录上锁,以及所有间隔的锁。 对于索引字段进行上述操作,代价一般。只有索引字段本身和附近的间隔会被加锁。 这次终于明白,为什么说update语句的代价高!

    作者回复: 是的,update、delete语句用不上索引是很恐怖的😄

    2019-01-03
    10
    76
  • 沉浮
    通过打印锁日志帮助理解问题 锁信息见括号里的说明。 TABLE LOCK table `guo_test`.`t` trx id 105275 lock mode IX RECORD LOCKS space id 31 page no 4 n bits 80 index c of table `guo_test`.`t` trx id 105275 lock_mode X Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 ----(Next-Key Lock,索引锁c(5,10]) 0: len 4; hex 8000000a; asc ;; 1: len 4; hex 8000000a; asc ;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 ----(Next-Key Lock,索引锁c (10,15]) 0: len 4; hex 8000000f; asc ;; 1: len 4; hex 8000000f; asc ;; Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 ----(Next-Key Lock,索引锁c (15,20]) 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000014; asc ;; Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 ----(Next-Key Lock,索引锁c (20,25]) 0: len 4; hex 80000019; asc ;; 1: len 4; hex 80000019; asc ;; RECORD LOCKS space id 31 page no 3 n bits 80 index PRIMARY of table `guo_test`.`t` trx id 105275 lock_mode X locks rec but not gap Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 ----(记录锁 锁c=15对应的主键) 0: len 4; hex 8000000f; asc ;; 1: len 6; hex 0000000199e3; asc ;; 2: len 7; hex ca000001470134; asc G 4;; 3: len 4; hex 8000000f; asc ;; 4: len 4; hex 8000000f; asc ;; Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000014; asc ;; ----(记录锁 锁c=20对应的主键) 1: len 6; hex 0000000199e3; asc ;; 2: len 7; hex ca000001470140; asc G @;; 3: len 4; hex 80000014; asc ;; 4: len 4; hex 80000014; asc ;; 由于字数限制,正序及无排序的日志无法帖出,倒序日志比这两者,多了范围(Next-Key Lock,索引锁c(5,10]),个人理解是,加锁分两次,第一次,即正序的锁,第二次为倒序的锁,即多出的(5,10],在RR隔离级别, innodb在加锁的过程中会默认向后锁一个记录,加上Next-Key Lock,第一次加锁的时候10已经在范围,由于倒序,向后,即向5再加Next-key Lock,即多出的(5,10]范围

    作者回复: 优秀

    2018-12-28
    6
    68
  • kabuka
    这样,当你执行 select * from t where d=5 for update 的时候,就不止是给数据库中已有的 6 个记录加上了行锁,还同时加了 还同时加了 7 个间隙锁 --------------------------------------------------------------- 老師這句話沒看太明白,數據庫只有一條d=5的記錄,為什麼會給6個記錄加上行鎖呢?

    作者回复: 因为d上没有索引,这个语句要走全表扫描

    2019-03-11
    11
    53
  • 郭江伟
    insert into t values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25); 运行mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t where c>=15 and c<=20 order by c desc for update; c 索引会在最右侧包含主键值,c索引的值为(0,0) (5,5) (10,10) (15,15) (20,20) (25,25) 此时c索引上锁的范围其实还要匹配主键值 。 思考题答案是,上限会扫到c索引(20,20) 上一个键,为了防止c为20 主键值小于25 的行插入,需要锁定(20,20) (25,25) 两者的间隙;开启另一会话(26,25,25)可以插入,而(24,25,25)会被堵塞。 下限会扫描到(15,15)的下一个键也就是(10,10),测试语句会继续扫描一个键就是(5,5) ,此时会锁定,(5,5) 到(15,15)的间隙,由于id是主键不可重复所以下限也是闭区间; 在本例的测试数据中添加(21,25,25)后就可以正常插入(24,25,25)

    作者回复: 感觉你下一篇看起来会很轻松了哈👍🏿

    2018-12-28
    9
    42
  • 发条橙子 。
    老师 , 看到幻读的定义是 : 幻读是一个事物在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行 。 那么我感觉 1. 读提交事务隔离级别 2.可重复读事务隔离级别的当前读 这两个都符合这个定义 。 那是不是说 在 1 、 2 条件下都会发生幻读 。 但是我看一些文章都说幻读是rr级别下的 , rc 是不可重复读 。请问是我理解有误还是文章写的不准确

    作者回复: 其实读提交隔离级别下看到的,严格来说不算。 因为这个就是读提交隔离级别下“设计内”的问题😄 这种感觉就是,对于读提交隔离级别,这个算“feature”,(是不是恨熟悉) 对于可重复读,这个是”bug”, 所以要解决,称呼这个bug为幻读😄

    2019-01-01
    4
    27
  • hetiu
    mysql官方提到自增锁是个表级锁,老师能介绍下这个吗,以及实际项目中高并发insert是否需要避免自增主键?

    作者回复: 好问题,innodb_auroinc_lock_mode设置为2,binlog_formate设置成row就行,没有表锁问题

    2019-01-06
    5
    20
收起评论
显示
设置
留言
99+
收藏
沉浸
阅读
分享
手机端
快捷键
回顶部