MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
43178 人已学习
课程目录
已完结 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讲
登录|注册

08 | 事务到底是隔离的还是不隔离的?

林晓斌 2018-11-30
你好,我是林晓斌。
你现在看到的这篇文章是我重写过的。在第一版文章发布之后,我发现在介绍事务可见性规则时,由于引入了太多概念,导致理解起来很困难。随后,我索性就重写了这篇文章。
现在的用户留言中,还能看到第一版文章中引入的 up_limit_id 的概念,为了避免大家产生误解,再此特地和大家事先说明一下。
我在第 3 篇文章和你讲事务隔离级别的时候提到过,如果是可重复读隔离级别,事务 T 启动的时候会创建一个视图 read-view,之后事务 T 执行期间,即使有其他事务修改了数据,事务 T 看到的仍然跟在启动时看到的一样。也就是说,一个在可重复读隔离级别下执行的事务,好像与世无争,不受外界影响。
但是,我在上一篇文章中,和你分享行锁的时候又提到,一个事务要更新一行,如果刚好有另外一个事务拥有这一行的行锁,它又不能这么超然了,会被锁住,进入等待状态。问题是,既然进入了等待状态,那么等到这个事务自己获取到行锁要更新数据的时候,它读到的值又是什么呢?
我给你举一个例子吧。下面是一个只有两行的表的初始化语句。
mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`k` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);
图 1 事务 A、B、C 的执行流程
这里,我们需要注意的是事务的启动时机。
begin/start transaction 命令并不是一个事务的起点,在执行到它们之后的第一个操作 InnoDB 表的语句,事务才真正启动。如果你想要马上启动一个事务,可以使用 start transaction with consistent snapshot 这个命令。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(360)

  • 夏日雨 置顶
    老师你好,有个问题不太理解,对于文中的例子假设transaction id为98的事务在事务A执行select(Q2)之前更新了字段,那么事务A发现这个字段的row trx_id是98,比自己的up_limit_id要小,那此时事务A不就获取到了transaction id为98的事务更新后的值了吗?
    换句话说对于文中"之后的更新,产生的新的数据版本的 row trx_id 都会大于 up_limit_id"这句话不太理解, up_limit_id是已经提交事务id的最大值,那也可能存在一个没有提交的id小于up_limit_id的事务对数据进行更新?还是说transaction id比up_limit_id小的事务都是保证已经提交的?

    作者回复: 你的问题被引用最多,我回复你哈,其它同学看过来😄

    好吧,今天的课后问题其实比较简单,本来是隐藏在思考题里的彩蛋,被你问出来了哈。

    Innodb 要保证这个规则:事务启动以前所有还没提交的事务,它都不可见。

    但是只存一个已经提交事务的最大值是不够的。 因为存在一个问题,那些比最大值小的事务,之后也可能更新(就是你说的98这个事务)

    所以事务启动的时候还要保存“现在正在执行的所有事物ID列表”,如果一个row trx_id在这列表中,也要不可见。

    虽然踩破了彩蛋,还是赞你的思考哈,置顶让大家学习😄

    2018-11-30
    1
    109
  • 约书亚 置顶
    早。
    思考题,RR下,用另外一个事物在update执行之前,先把所有c值修改,应该就可以。比如update t set c = id + 1。
    这个实际场景还挺常见——所谓的“乐观锁”。时常我们会基于version字段对row进行cas式的更新,类似update ...set ... where id = xxx and version = xxx。如果version被其他事务抢先更新,则在自己事务中更新失败,trx_id没有变成自身事务的id,同一个事务中再次select还是旧值,就会出现“明明值没变可就是更新不了”的“异象”(anomaly)。解决方案就是每次cas更新不管成功失败,结束当前事务。如果失败则重新起一个事务进行查询更新。
    记得某期给老师留言提到了,似乎只有MySQL是在一致性视图下采用这种宽松的update机制。也许是考虑易用性吧。其他数据库大多在内部实现cas,只是失败后下一步动作有区别。

    作者回复: 早



    置顶了

    明天课后问题时间直接指针引用了哈😄

    补充一下:上面说的“如果失败就重新起一个事务”,里面判断是否成功的标准是 affected_rows 是不是等于预期值。
    比如我们这个例子里面预期值本来是4,当然实际业务中这种语句一般是匹配唯一主键,所以预期住值一般是1。

    2018-11-30
    8
    60
  • ithunter 置顶
    请教一个问题,业务上有这样的需求,A、B两个用户,如果互相喜欢,则成为好友。设计上是有两张表,一个是like表,一个是friend表,like表有user_id、liker_id两个字段,我设置为复合唯一索引即uk_user_id_liker_id。语句执行顺序是这样的:
    以A喜欢B为例:
    1、先查询对方有没有喜欢自己(B有没有喜欢A)
    select * from like where user_id = B and liker_id = A
    2、如果有,则成为好友
    insert into friend
    3、没有,则只是喜欢关系
    insert into like

    如果A、B同时喜欢对方,会出现不会成为好友的问题。因为上面第1步,双方都没喜欢对方。第1步即使使用了排他锁也不行,因为记录不存在,行锁无法生效。请问这种情况,在mysql锁层面有没有办法处理

    作者回复: 你这个问题很有趣。我想到一个不错的解法。不过我先置顶。让别的同学来回答看看。

    好问题,谁有想法po出来。

    2018-12-05
    14
    25
  • 心雨鑫晴 置顶
    老师,我有一个问题。当开启事务时,需要保存活跃事务的数组(A),然后获取高水位(B)。我的疑问就是,在这两个动作之间(A和B之间)会不会产生新的事务?如果产生了新的事务,那么这个新的事务相对于当前事务就是可见的,不管有没有提交。

    作者回复: 好问题,有很深入的思考哈

    代码实现上,获取视图数组和高水位是在事务系统的锁保护下做的,可以认为是原子操作,期间不能创建事务。

    2018-12-03
    2
    20
  • 包包up 置顶
    以下是一个错误的理解,在编写评论的过程中用前面刚学到的知识把自己的结论推翻,有一种快感,所以还是决定发出来。 哈哈~

    事务A(100) | 事务B(101)
    -------------------
                 | select(1)
    -------------------
                 | update
    -------------------
      update |
    -------------------
                  | select(2)
    -------------------
    事务A B在事务启动时的up_limit_id为99
    事务B update 之后表格的每一行的row_trx_id变为101
    事务A 再update 之后每一行的row_trx_id变为100
    事务B的select(2)时因为隔离级别是RR,所以去遍历的时候找row_trx_id<=101的版本返回,优先找到版本为100的,就会导致select(2)并没有取到自己的更新。
    对于对于自己的修改也认这句话和undo-log的介绍,我觉的这种情况下会获取不到自己更新的最新的数据。不知道我理解的对不对。

    不对!因为事务A的update是会被行锁锁住的,而且锁是要在事务B结束之后才释放,所以不存在在事务B的update之后还在事务中被事务A给更新,导致上面的问题。

    作者回复: 👍🏿

    我在学习过程中也是最喜欢这种“自己推翻自己结论”的快感

    2018-11-30
    1
    11
  • Leo 置顶
    老师在文中说: "所以,在执行事务 B 的 Q1 语句的时候,一看自己的版本号是 101,最新数据的版本号也是 101,可以用,所以 Q1 得到的 k 的值是 3。",
    1. 这里不参考up_limit_id了吗?
    2. 如果参考,事务B的up_limit_id是在执行update语句前重新计算的,还是在执行Q1语句前重新计算的?

    作者回复: 1. 判断可见性两个规则:一个是up_limit_id ,另一个是“自己修改的”;这里用到第二个规则

    2. 这时候事务Bup_limit_id还是99

    2018-11-30
    5
  • 墨萧 置顶
    可重复读情况下,事务c的102早于事务b的101,如果事务c再get k,那不是就取得101的值了?不太明白。

    作者回复: 咱们例子里面,事务C是直接提交的,再执行一个GET 就是另外一个事务了…

    如果你说的是用begin 来启动一个多语句事务,那么事务c在更新后查询,还是看到row trx_id是102的。 【注意:如果它还没提交,101根本生成不出来,因为事务B被行锁挡着呢】

    2018-11-30
    4
  • 某、人
    这篇理论知识很丰富,需要先总结下
    1.innodb支持RC和RR隔离级别实现是用的一致性视图(consistent read view)

    2.事务在启动时会拍一个快照,这个快照是基于整个库的.
    基于整个库的意思就是说一个事务内,整个库的修改对于该事务都是不可见的(对于快照读的情况)
    如果在事务内select t表,另外的事务执行了DDL t表,根据发生时间,要嘛锁住要嘛报错(参考第六章)

    3.事务是如何实现的MVCC呢?
    (1)每个事务都有一个事务ID,叫做transaction id(严格递增)
    (2)事务在启动时,找到已提交的最大事务ID记为up_limit_id。
    (3)事务在更新一条语句时,比如id=1改为了id=2.会把id=1和该行之前的row trx_id写到undo log里,
    并且在数据页上把id的值改为2,并且把修改这条语句的transaction id记在该行行头
    (4)再定一个规矩,一个事务要查看一条数据时,必须先用该事务的up_limit_id与该行的transaction id做比对,
    如果up_limit_id>=transaction id,那么可以看.如果up_limit_id<transaction id,则只能去undo log里去取。去undo log查找数据的时候,也需要做比对,必须up_limit_id>transaction id,才返回数据

    4.什么是当前读,由于当前读都是先读后写,只能读当前的值,所以为当前读.会更新事务内的up_limit_id为该事务的transaction id

    5.为什么rr能实现可重复读而rc不能,分两种情况
    (1)快照读的情况下,rr不能更新事务内的up_limit_id,
        而rc每次会把up_limit_id更新为快照读之前最新已提交事务的transaction id,则rc不能可重复读
    (2)当前读的情况下,rr是利用record lock+gap lock来实现的,而rc没有gap,所以rc不能可重复读

    作者回复: 👍🏿

    本篇知识点全get

    2018-11-30
    3
    73
  • lucky star
    答案:
          分析: 假设有两个事务A和B, 且A事务是更新c=0的事务; 给定条件: 1, 事务A update 语句已经执行成功, 说明没有另外一个活动中的事务在执行修改条件为id in 1,2,3,4或c in 1,2,3,4, 否则update会被锁阻塞; 2,事务A再次执行查询结果却是一样, 说明什么?说明事务B把id或者c给修改了, 而且已经提交了, 导致事务A“当前读”没有匹配到对应的条件; 事务A的查询语句说明了事务B执行更新后,提交事务B一定是在事务A第一条查询语句之后执行的;

    所以执行顺序应该是:
    1, 事务A select * from t;
    2, 事务B update t set c = c + 4; // 只要c或者id大于等于5就行; 当然这行也可以和1调换, 不影响
    3, 事务B commit;
    4, 事务A update t set c = 0 where id = c; // 当前读; 此时已经没有匹配的行
    5, 事务A select * from t;

    读完第三篇后就陷入了事务执行原理的泥潭中了, 也找了不少相关资料, 但总感觉还不是特别明白, 今天看完这篇终于茅塞顿开呀, 仿佛打通了任督二脉了。。。。

    作者回复: 嗯嗯,分析得很对。

    茅塞顿开的感觉很好,恭喜🎉🎈

    2018-12-15
    1
    29
  • Eric
    我不是dba,这个课程还是需要一些基础才会更有帮助,有些章节对我来说确实看起来有些吃力,但是在坚持,一遍看不懂看两遍、三遍,同时查漏补缺的去找一些资料补充盲点,还组了个一起学习的群,希望能坚持下去,收获满满

    作者回复: 赞👍🏿
    慢慢来

    2018-11-30
    22
  • 老师您好:
        今天重新看了一下这章您的修改地方,有个地方不明白
        落在黄色区域未提交事务集合部分怎么还要分类,低水位+高水位不就是这个数组了吗,之前说,这个数组是记录事务启动瞬间,所有已经启动还未提交的事务ID,那不应该是未提交的事务吗,不就应该是不可读的吗
    之前说的是启动时会获取一个最大row trx_id,所有大于这个id都不认,这个id肯定是已经提交了的事务的才对啊,这个id不才应该是数组的高水位吗,这里有点懵了

    作者回复: 你设计一个“比低水位大,但是在当前事务启动前,就已经提交了的例子😄

    2018-12-03
    2
    17
  • 沙亮亮
    买了很多专栏,丁奇老师绝对是为读者考虑最为细致的,不管是从回复大家的提问,还是从学习者角度考虑优化文章内容,最后到思考题的讲解,都是最细致的

    作者回复: 谢谢你,我倍受鼓舞呀😄

    2018-12-07
    15
  • 薛畅
    评论区的好多留言都认为 up_limit_id 是已经提交事务 id 的最大值,但是老师并未指出有何不对,这让我很困惑。
    老师在第二版的文章中通篇未提 up_limit_id,但是文章中有这么一段话:“InnoDB 为每个事务构造了一个数组,用来保存这个事务启动启动瞬间,当前正在“活跃”的所有事务 ID。“活跃”指的就是,启动了但还没提交。数组里面事务 ID 的最小值记为低水位,当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位”。那么这个 up_limit_id 指的是不是数组里面事务 ID 的最小值,假如是的话,那么 up_limit_id 并不是已经提交事务 id 的最大值,而是活跃事物的最小值。

    作者回复: 在这版里面就是用“低水位”来作为活跃的最小ID的概念,

    嗯其实是为了理解原理,用了不同的表述方式哈。

    后面发现上一版的描述方法太公式化了,不利于人工分析

    2018-12-03
    2
    15
  • 崔根禄
    老师,
    这两天反复读这篇文章,想到一个业务上的问题:减库存的场景
    当前库存:num=200
    假如多线程并发:
    AB同时开启事务,A先请求到行锁,
    A:
    start transaction;
    select num from t where num>0;先查询当前库存值(num>0)
    update t set num=num-200; 库存减量

    B:
    start transaction;
    select num from t where num>0;先查询当前库存值(num>0)
    update t set num=num-200; 库存减量
    ----结果---
    A:查询到num=200,做了库存减量成了0
    B:事务启动后,查询到也是200,等 A 释放了行锁,B进行update,直接变成 -200
    但是 B 查询时,时有库存的,因此才减库存,结果变成负的。
    老师,对于这种场景,怎么避免减成负值?
    给 select 加读锁或者写锁吗 ?这种select 加锁,对业务影响大吗?

    作者回复: 这是个好问题,也是并发业务常见的问题。

    一开始Select 加锁虽然可以,但是会比较严重地影响并发数。

    比较简单的做法是update语句的where 部分加一个条件: where nun >=200 .
    然后在程序里判断这个update 语句的affected_rows,
    如果等于1 那就是符合预期;
    如果等于0,那表示库存不够减了,业务要处理一下去,比如提示“库存不足”

    2018-12-07
    1
    14
  • qpm
    这是典型的“丢失更新”问题。一个事务的更新操作被另外一个事务的更新操作覆盖。在RR状态下,普通select的时候是会获得旧版本数据的,但是update的时候就检索到最新的数据。
    解决方法:在读取的过程中设置一个排他锁,在 begin 事务里, select 语句中增加 for update 后缀,这样可以保证别的事务在此事务完成commit前无法操作记录。参考《MySQL技术内幕 InnoDB存储引擎》
    2018-12-01
    9
  • york
    思考题为何我做出来成功修改为0了啊?

    作者回复: 那就是没复现😄

    2018-11-30
    2
    9
  • 老师回复“你设计一个“比低水位大,但是在当前事务启动前,就已经提交了的例子😄”
    我意思说比低水位大的肯定是已经提交的事务啊,这样的话黄色区域肯定都是已经提交的事务啊,为什么还要区分已经提交和还没有提交的事务呢?应该都是不可读的才对吧
    如果是RC的话,可以理解成每次读之前会再去黄色区域看看有没有提交,但是RR应该就不会再去读黄色区域了才对

    作者回复: 比低水位大的不一定已经提交了哦

    比如一个事务启动时当前活跃事务是[99,100,102], 而101已经提交了

    2018-12-04
    8
  • Sinyo
    原来在同一行数据,最新版本的 row trx_id 是可能会小于旧版本的 row trx_id的,这里才搞明白(惭愧脸)。。

    作者回复: 赞,这个想通的感觉是很爽的

    2018-12-07
    1
    7
  • 小卡向前冲
    明白了,是我之前对高低水位的定义没有搞清楚:RR隔离级别下,事务A在执行Select时,要重算read-view,此时数组是[99, 100, 101],系统最大事务id是102,故低水位是99,高水位是102+1=103。
    这样就可以推出来了~~

    作者回复: 这回理解到位了😄

    2018-12-12
    6
  • Achace0120
    老师,文中图3 数据可见性规则内容中,3(b)的描述不太理解,能否举一个具体场景?
    2019-03-13
    5
收起评论
99+
返回
顶部