MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
43177 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 这一次,让我们一起来搞懂MySQL
免费
基础篇 (8讲)
01 | 基础架构:一条SQL查询语句是如何执行的?
02 | 日志系统:一条SQL更新语句是如何执行的?
03 | 事务隔离:为什么你改了我还看不见?
04 | 深入浅出索引(上)
05 | 深入浅出索引(下)
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
07 | 行锁功过:怎么减少行锁对性能的影响?
08 | 事务到底是隔离的还是不隔离的?
实践篇 (37讲)
09 | 普通索引和唯一索引,应该怎么选择?
10 | MySQL为什么有时候会选错索引?
11 | 怎么给字符串字段加索引?
12 | 为什么我的MySQL会“抖”一下?
13 | 为什么表数据删掉一半,表文件大小不变?
14 | count(*)这么慢,我该怎么办?
15 | 答疑文章(一):日志和索引相关问题
16 | “order by”是怎么工作的?
17 | 如何正确地显示随机消息?
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
19 | 为什么我只查一行的语句,也执行这么慢?
20 | 幻读是什么,幻读有什么问题?
21 | 为什么我只改一行的语句,锁这么多?
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
23 | MySQL是怎么保证数据不丢的?
24 | MySQL是怎么保证主备一致的?
25 | MySQL是怎么保证高可用的?
26 | 备库为什么会延迟好几个小时?
27 | 主库出问题了,从库怎么办?
28 | 读写分离有哪些坑?
29 | 如何判断一个数据库是不是出问题了?
30 | 答疑文章(二):用动态的观点看加锁
31 | 误删数据后除了跑路,还能怎么办?
32 | 为什么还有kill不掉的语句?
33 | 我查这么多数据,会不会把数据库内存打爆?
34 | 到底可不可以使用join?
35 | join语句怎么优化?
36 | 为什么临时表可以重名?
37 | 什么时候会使用内部临时表?
38 | 都说InnoDB好,那还要不要使用Memory引擎?
39 | 自增主键为什么不是连续的?
40 | insert语句的锁为什么这么多?
41 | 怎么最快地复制一张表?
42 | grant之后要跟着flush privileges吗?
43 | 要不要使用分区表?
44 | 答疑文章(三):说一说这些好问题
45 | 自增id用完怎么办?
特别放送 (1讲)
直播回顾 | 林晓斌:我的 MySQL 心路历程
结束语 (1讲)
结束语 | 点线网面,一起构建MySQL知识网络
MySQL实战45讲
登录|注册

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

林晓斌 2018-12-28
在上一篇文章最后,我给你留了一个关于加锁规则的问题。今天,我们就从这个问题说起吧。
为了便于说明问题,这一篇文章,我们就先使用一个小一点儿的表。建表和初始化语句如下(为了便于本期的例子说明,我把上篇文章中用到的表结构做了点儿修改):
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/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(138)

  • 令狐少侠 置顶
    老师,今天的文章对我影响很大,发现之前掌握的知识有些错误的地方,课后我用你的表结构根据以前不清楚的地方实践了一遍,现在有两个问题,麻烦您解答下
    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
    5
    18
  • xuery 置顶
    老师之前的留言说错了,重新梳理下:
    图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
    6
  • AI杜嘉嘉
    说真的,这一系列文章实用性真的很强,老师非常负责,想必牵扯到老师大量精力,希望老师再出好文章,谢谢您了,辛苦了

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

    2018-12-28
    48
  • 薛畅
    可重复读隔离级别下,经试验:
    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
    1
    31
  • 沉浮
    通过打印锁日志帮助理解问题
    锁信息见括号里的说明。

    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
    21
  • 郭江伟
    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
    17
  • 慧鑫coming
    这篇需要多读几遍,again
    2018-12-28
    10
  • 简海青
    加锁过程的分析,这篇文章也是很棒的;供同学们参考
    http://hedengcheng.com/?p=771
    2019-05-04
    7
  • kabuka
    这样,当你执行 select * from t where d=5 for update 的时候,就不止是给数据库中已有的 6 个记录加上了行锁,还同时加了 还同时加了 7 个间隙锁
    ---------------------------------------------------------------
    老師這句話沒看太明白,數據庫只有一條d=5的記錄,為什麼會給6個記錄加上行鎖呢?

     

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

    2019-03-11
    6
  • en
    老师您好,我mysql的隔离级别是可重复读,数据是(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25),使用了begin;select * from t where c>=15 and c<=20 order by c desc for update;然后sessionB的11阻塞了,但是(6,6,6)的插入成功了这是什么原因呢?
    2018-12-31
    1
    5
  • 郭健
    老师,想请教您几个问题。1.在第六章MDL锁的时候,您说给大表增加字段和增加索引的时候要小心,之前做过测试,给一个一千万的数据增加索引有时需要40分钟,但是增加索引不会对表增加MDL锁吧。除了增加索引慢,还会对数据库有什么影响吗,我问我们dba,他说就开始和结束的时候上一下锁,没什么影响,我个人是持怀疑态度的。2,老师讲到表锁除了MDL锁,还有显示命令lock table的命令的表锁,老师我可以认为,在mysql中如果不显示使用lock table表锁的话,那么mysql是永远不会使用表锁的,如果锁的条件没有索引,使用的是锁住行锁+间隙控制并发。

    作者回复: 1. 在锁方面你们dba说的基本是对的。一开始和结束有写锁,执行中间40分钟只有读锁
    但是1000万的表要做40分钟,可能意味着系统压力大(或者配置偏小),这样可能不是没影响对,比较这个操作还是要吃IO和CPU的

    2. 嗯,innodb引擎是这样的。

    2018-12-30
    5
  • godtrue
    课前思考
    1:幻读是什么?幻读有什么问题?如何避免?
    幻读我的理解是,读出的数据出现了不一致的现象,在事务的读未提交和读已提交这两种事务的隔离级别下会出现幻读的现象,问题嘛?就是数据不一致了,对于数据严格要求一致的场景是不能够允许的。如何避免?在可重复读和串行化的事务隔离级别下应该不会出现
    课后思考
    1:学完此节后发现自己的认知,基本是错的
    1-1:什么是幻读?
    幻读是指在同一个事务中,存在前后两次查询同一个范围的数据,但是第二次查询却看到了第一次查询没看到的行。
    注意,幻读出现的场景
    第一:事务的隔离级别为可重复读,且是当前读
    第二:幻读仅专指新插入的行
    1-2:幻读带来的问题?
    一是,对行锁语义的破坏
    二是,破坏了数据一致性
    1-3:怎么避免幻读?
    存储引擎采用加间隙锁的方式来避免出现幻读
    1-4:为啥会出现幻读?
    行锁只能锁定存在的行,针对新插入的操作没有限定
    1-5:间隙锁是啥?它怎么避免出现幻读的?它引入了什么新的问题?
    间隙锁,是专门用于解决幻读这种问题的锁,它锁的了行与行之间的间隙,能够阻塞新插入的操作
    间隙锁的引入也带来了一些新的问题,比如:降低并发度,可能导致死锁。
    注意,读读不互斥,读写/写读/写写是互斥的,但是间隙锁之间是不冲突的,间隙锁会阻塞插入操作
    另外,间隙锁在可重复读级别下才是有效的
    感谢老师的分享,意料之外的认知很好玩,也纠正了自己的认知偏差。
    感觉自己明白了,看完评论,感觉自己啥都不懂。
    2019-08-01
    1
    4
  • yan华建
    什么是幻读?
    幻读指的是一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行。(幻读在当前读下才会出现;幻读仅专指新插入的行)
    如何解决幻读?
    间隙锁(Gap lock):(两个值之间的锁)。
    间隙锁和行锁合称 next-key lock,每个 next-key lock 是前开后闭区间。
    间隙锁为开区间。
    next-key-lock为前开后闭区间。
    间隙锁引入什么问题?
    可能会导致同样的语句锁住更大的范围,这其实是影响了并发度的。
    间隙锁在RR级别下才有效,RC级别下无间隙锁。
    不使用间隙锁方法:
    使用读提交隔离级别+ binlog_format=row组合。
    2019-06-16
    4
  • 南友力max先森🌈
    丁老师,想问下,innodb的行锁是怎么实现的,有单独的数据结构存放哪些数据块记录是被锁的么?还是在聚簇索引上对该行数据进行锁定标记?或者是其他?

    作者回复: 看下08篇哦,
    里面有介绍到行锁
    还有问题再在那个文章下面发哈

    2019-02-27
    3
  • 杰哥长得帅
    想问一下老师哪一张会讲意向锁。后面会不会对mysql所有的锁种类做一个总结 😷

    作者回复: 由于有metadata lock,意向锁其实没什么作用了,所以不会专门介绍哦,可能会在讨论其他问题的时候顺便带一下

    2019-01-16
    3
  • 滔滔
    老师,听了您的课收获满满~~感谢您的付出!您可不可以在分析死锁的时候讲一下如何分析死锁日志,期待~~😀

    作者回复: 谢谢你的肯定。

    嗯死锁分析会有一篇专门说。

    不过你可以提前说一下碰到的疑问😄

    2018-12-29
    3
  • N
    老师您好,gap lock是为了解决幻读问题,但是为什么在可重复读的隔离级别就生效了呢?但是我们知道可重复读是不可避免幻读发生的,请问我们该如何理解这个问题?
    2019-08-18
    1
    2
  • Cv
    gap锁是否只会在可重复读的情况下才有?
    在提交读和有唯一索引的情况下, 我也有遇到过因为gap死锁的情况
    大致是这种sql
    session1
    delete from t where id in (1,3,5);
    insert into t id(1,3,5);
    session2
    delete from t where id in (2,4,6);
    insert into t id(2,4,6);

    作者回复: 读提交隔离级别一般没有gap lock,不过也有例外情况, 比如insert 出现主键冲突的时候,也可能加间隙锁

    2019-03-07
    2
  • Geek_89bbab
    表结构
    CREATE TABLE `t2` (
      `id` int(11) DEFAULT NULL,
      `v` int(11) DEFAULT NULL
    ) ENGINE=InnoDB;
    两个session,
    session1, | session2
    step1: set session transaction isolation level repeatable read;(session1) | set session transaction isolation level repeatable read;(session2)
    step2: begin;(session1)
    step3: begin; (session2)
    step4: insert into t2 (id,v) values(1,1); (session1)
    step5: insert into t2 (id,v) select 2,2 from dual where not exists(select * from t2 where id=2); (session2) // 这里为什么会阻塞,直到session1提交呢?
    step6: commit; (session1) 该句执行完 session2不再阻塞
    step7:commit;(session2)
    我的疑惑就是为什么step5 那一步会阻塞?select * from t2 where id=2 不是快照读吗?也没有用for update,share lock 之类的语句,而且insert into 也没有什么唯一键约束,主键约束,怎么用数据库锁和隔离级别的知识来解释这个现象呢?请老师指点

    作者回复: 好问题

    Insert...select 是会给select部分加读锁的

    这个也是为了保证一致性

    2019-01-07
    2
  • Mr.Strive.Z.H.L
    看了@令狐少侠 提出的问题,对锁有了新的认识:
    对于非索引字段进行update或select .. for update操作,代价极高。所有记录上锁,以及所有间隔的锁。
    对于索引字段进行上述操作,代价一般。只有索引字段本身和附近的间隔会被加锁。

    这次终于明白,为什么说update语句的代价高!

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

    2019-01-03
    2
收起评论
99+
返回
顶部