mysql中的事务隔离级别及可重复读读提交详细分析(mvcc多版本控制/undo log)

mysql中的事务隔离级别及可重复读读提交详细分析(mvcc多版本控制/undo log)

一.事物隔离级别

  • 读未提交(read uncommitted)是指,一个事务还没提交时,它做的变更就能被别的事务看到.通俗理解,别人改数据的事务尚未提交,我在我的事务中也能读到。
  • 读提交(read committed)是指,一个事务提交之后,它做的变更才会被其他事务看到。通俗理解,别人改数据的事务已经提交,我在我的事务中才能读到。
  • 可重复读(repeatable read)是指,一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据 是一致的。当然在可重复读隔离级别下,未提交变更对其他事务也是不可见的。通俗理解,别人改数据的事务已经提交,我在我的事务中也不去读。
  • 串行化(serializable ),顾名思义是对于同一行记录,“写”会加“写锁”,“读”会加“读锁”。当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行。通俗理解,我的事务尚未提交,别人就别想改数据。

图片示例讲解

  • 若隔离级别是“读未提交”, 则 V1 的值就是 2。这时候事务 B 虽然还没有提交,但是 结果已经被 A 看到了。因此,V2、V3 也都是 2。
  • 若隔离级别是“读提交”,则 V1 是 1,V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以, V3 的值也是 2。
  • 若隔离级别是“可重复读”,则 V1、V2 是 1,V3 是 2。之所以 V2 还是 1,遵循的就 是这个要求:事务在执行期间看到的数据前后必须是一致的。
  • 若隔离级别是“串行化”,则在事务 B 执行“将 1 改成 2”的时候,会被锁住。直到事 务 A 提交后,事务 B 才可以继续执行。所以从 A 的角度看, V1、V2 值是 1,V3 的值 是 2。
在实现上,数据库里面会创建一个视图,访问的时候以视图的逻辑结果为准。在“可重复 读”隔离级别下,这个视图是在事务启动时创建的,整个事务存在期间都用这个视图。 在“读提交”隔离级别下,这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要 注意的是,“读未提交”隔离级别下直接返回记录上的最新值,没有视图概念;而“串行 化”隔离级别下直接用加锁的方式来避免并行访问。

二.查看mysql的隔离级别

  • mysql> show variables like "transaction_isolation";
  • mysql默认级别: repeatable read

三.事务隔离的实现

  • 每条记录在更新的时候都会同时记录一条回滚操作(也就是说redo log会记录,undo log也会同时记录).

  • 记录上的最新值,通过回滚操作,都可以得到前一个状态的值。

  • 回滚日志删除问题.

    在不需要的时候才删除。也就是说,系统会判断,当没有事务再需要用到这些回滚日志时,回滚日志会被删除。
    什么时候才不需要了呢?就是当系统里没有比这个回滚日志更早的 read-view 的时候。
  • 回滚日志存储位置

    在 MySQL 5.5 及以前的版本,回滚日志是跟数据字典一起放在 ibdata 文件里的(系统表空间),即使长 事务最终提交,回滚段被清理,文件也不会变小。我见过数据只有 20GB,而回滚段有 200GB 的库。最终只好为了清理回滚段,重建整个库。
  • 回滚流程

四.尽量不要使用长事务

  • 长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据,所以这个事务提交之前,数据库里面它可能用到的回滚记录都必须保留,这就会导致大量占用存储空间。

  • 还占用锁资源,也可能拖垮整个库

  • 详解

    比如,在某个时刻(今天上午9:00)开启了一个事务A(对于可重复读隔离级别,此时一个视图read-view A也创建了),这是一个很长的事务……
    
    事务A在今天上午9:20的时候,查询了一个记录R1的一个字段f1的值为1……
    
    今天上午9:25的时候,一个事务B(随之而来的read-view B)也被开启了,它更新了R1.f1的值为2(同时也创建了一个由2到1的回滚日志),这是一个短事务,事务随后就被commit了。
    
    今天上午9:30的时候,一个事务C(随之而来的read-view C)也被开启了,它更新了R1.f1的值为3(同时也创建了一个由3到2的回滚日志),这是一个短事务,事务随后就被commit了。
    
    ……
    
    到了下午3:00了,长事务A还没有commit,为了保证事务在执行期间看到的数据在前后必须是一致的,那些老的事务视图、回滚日志就必须存在了(read-view B,read-view C),这就占用了大量的存储空间。
    
    源于此,我们应该尽量不要使用长事务。

在information_schema 库的 innodb_trx 这个表中查询长事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

五.事务的启动方式

  • 显式启动事务语句

    begin 或 start transaction。
    配套的提交语句是 commit,
    回滚语句是 rollback。
  • set autocommit=0,会将线程的自动提交关掉.

    意味着如果你只执行一个 select 语句,这个事务就启动了,而且并不会自动提交。这个事务持续存在直到你主 动执行 commit 或 rollback 语句,或者断开连接。
  • select也是事物

六.可重复读隔离级详细分析

可重复读隔离级别下的更新和读取

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);

注意的是事务的启动时机。

在可重复读RR隔离级别模式下,begin/start transaction 命令并不是一个事务的起点,在执行到它们之后的第一个操作 InnoDB 表的语句,事务才真正启动。如果你想要马上启动一个事务,可以使用 start transaction with consistent snapshot 这个命令。

  • 第一种启动方式,begin/start transaction一致性视图是在第执行第一个快照读语句时创建的;
  • 第二种启动方式,start transaction with consistent snapshot一致性视图是在执行 start transaction with consistent snapshot 时创建的。

上面图1执行的结果是:

  • sessionB查询到的k值为3
  • sessionA查询到的k值为1
  • 执行顺序是,先执行sessionC更新,在执行sessionB更新和查询,再执行sessionA的查询
  • 得到上面的执行结果的原因是什么呢,下面分析.

mysql中两个视图概念

  • view。它是一个用查询语句定义的虚拟表,在调用的时候执行查询语句并生成结 果。创建视图的语法是 create view ... ,而它的查询方法与表一样。
  • InnoDB 在实现 MVCC 时用到的一致性读视图,即 consistent read view, 用于支持 RC(Read Committed,读提交)和 RR(Repeatable Read,可重复读)隔 离级别的实现。它没有物理结构,作用是事务执行期间用来定义“我能看到什么数据”。

快照在MVCC里是怎么工作的?

  • 在可重复读隔离级别下,事务在启动的时候就“拍了个快照”。注意,这个快照是基于整库的。

  • InnoDB 里面每个事务有一个唯一的事务 ID,叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的,是按申请顺序严格递增的。

  • 每行数据也都是有多个版本的,涉及到transaction id

    • 每次事务更新数据的时候,都会生成一个新的数据版本,并且把 transaction id 赋值给这个数据版本的事务 ID,记为 row trx_id

    • 同时,旧的数据版本要保留,并且在新的数据版本中,能够有信息可以直接拿到它。

    • 也就是说,数据表中的一行记录,其实可能有多个版本 (row),每个版本有自己的 row trx_id。

    • 图中虚线框里是同一行数据的 4 个版本,当前最新版本是 V4,k 的值是 22,它是被transaction id 为 25 的事务更新的,因此它的 row trx_id 也是 25。

    • 前面的文章不是说,语句更新会生成 undo log(回滚日志)吗?那么,undo log 在哪呢?

    • 实际上,图 2 中的三个虚线箭头,就是 undo log;而 V1、V2、V3 并不是物理上真实存 在的,而是每次需要的时候根据当前版本和 undo log 计算出来的。比如,需要 V2 的时 候,就是通过 V4 依次执行 U3、U2 算出来。

  • 按照可重复读的定义,一个事务启动的时候,能够看到所有已经提交的事务结果。但是之后,这个事务执行期间,其他事务的更新对它不可见。

  • 一个事务只需要在启动的时候声明说,“以我启动的时刻为准,如果一个数据版本是在我启动之前生成的,就认;如果是我启动以后才生成的,我就不认,我必须要找到它的上一个版本”。
    当然,如果“上一个版本”也不可见,那就得继续往前找。还有,如果是这个事务自己更
    新的数据,它自己还是要认的。

  • 在实现上, InnoDB 为每个事务构造了一个数组,用来保存这个事务启动瞬间,当前正 在“活跃”的所有事务 ID。“活跃”指的就是,启动了但还没提交。数组里面事务 ID 的最小值记为低水位,当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。这个视图数组和高水位,就组成了当前事务的一致性视图(read-view)。

  • 当开启事务时,需要保存活跃事务的数组(A),然后获取高水位(B)两者中间会不会产生新的事务?

    • A和B之间在事务系统的锁保护下做的,可以认为是原子操作,期间不能创建事务。
    • 高水位不在视图数组里面,高水位应该就是属于未来未开始事务了
    • 事务A启动时,当前活跃事务数组包不包括自己的trx_id,因为如果是自己更新的,总是可见的
  • 数据版本的可见性规则,就是基于数据的 row trx_id 和这个一致性视图的对比结果得到 的。这个视图数组把所有的 row trx_id 分成了几种不同的情况。

    • 对于当前事务的启动瞬间来说,假设当前trx id为98 , 在当前事务开始后,计算活跃事务之前又产生了个新事务trx id为99没有commit,假设活跃事务的id组成的数据为下面的数组[80,88,99],此时事务80/88/99为活跃事务,99为当前系统中事务最大ID, 高水位100是当前系统最大事务id99加1计算出来的,则会有以下几种可能:

      1. 如果落在绿色部分,表示这个版本是已提交的事务或者是当前事务自己生成的,这个数据是可见的; 即80以前的事务都可见

      2. 如果落在红色部分,表示这个版本是由将来启动的事务生成的,是肯定不可见的; 100及100以后的事务都不可见

      3. 如果落在黄色部分,那就包括两种情况

        a. 若 row trx_id 在数组中,表示这个版本是由还没提交的事务生成的,不可见; 80/88/99为活跃事务,不可见

        b. 若 row trx_id 不在数组中,表示这个版本是已经提交了的事务生成的,可见。80~99中间,去除80/88/99,比如81等其余的是可见的.

  • InnoDB 利用了“所有数据都有多个版本”的这个特性,利用数据可见性规则实现了“秒级创建快照”的能力。

  • 为什么会出现sessionB查询到的k值为3,sessionA查询到的k值为1呢,根据上面的数据可见性分析如下:

    • 这里,我们不妨做如下假设:

      1. 事务 A 开始前,系统里面只有一个活跃事务 ID 是 99;
      2. 事务 A、B、C 的版本号分别是 100、101、102,且当前系统里只有这四个事务;
      3. 三个事务开始前,(1,1)这一行数据的 row trx_id 是 90。
    • 事务 A 的视图数组就是 [99,100], 事务 B 的视图数组是 [99,100,101], 事务 C 的视 图数组是 [99,100,101,102]。

      从图中可以看到,第一个有效更新是事务 C,把数据从 (1,1) 改成了 (1,2)。这时候,这个数据的最新版本的 row trx_id 是 102,而 90 这个版本已经成为了历史版本。
      
      第二个有效更新是事务 B,把数据从 (1,2) 改成了 (1,3)。这时候,这个数据的最新版本 (即 row trx_id)是 101,而 102 又成为了历史版本。{备注:按理说事务B是[99,100,101],此时找到(1,2)的时候判断出row trx_id=102,比它自己的高水位大,处于红色区域,不可见,应该往前找,找(1,1)版本,但是此时它却是找的(1,2)row trx_id=102的版本,这是什么原因的,是因为更新都是“当前读”(current read),当前读这个概念下面解释}
      
      你可能注意到了,在事务 A 查询的时候,其实事务 B 还没有提交,但是它生成的 (1,3) 这 个版本已经变成当前版本了。但这个版本对事务 A 必须是不可见的,否则就变成脏读了。
      
      好,现在事务 A 要来读数据了,它的视图数组是 [99,100]。当然了,读数据都是从当前版本读起的。所以,事务 A 查询语句的读数据流程是这样的:
              找到 (1,3) 的时候,判断出 row trx_id=101,比高水位大,处于红色区域,不可见;
              接着,找到上一个历史版本,一看 row trx_id=102,比高水位大,处于红色区域,不可见;
              再往前找,终于找到了(1,1),它的 row trx_id=90,比低水位小,处于绿色区域,可见。
      
      这样执行下来,虽然期间这一行数据被修改过,但是事务 A 不论在什么时候查询,看到这 行数据的结果都是一致的,所以我们称之为一致性读。
  • 上面的分析判断规则是从代码逻辑直接转译过来的,一个数据版本,对于一个事务视图来说,除了自己的更新总是可见以外,有三种情况:

    1. 版本未提交,不可见;
    2. 版本已提交,但是是在视图创建后提交的,不可见;
    3. 版本已提交,而且是在视图创建前提交的,可见。

更新逻辑

事务 B 的 update 语句,如果按照一致性读,好像结果不对 哦?事务 B 的视图数组是先生成的,之后事务 C 才提交,不是应该看不见 (1,2) 吗,怎么能算出 (1,3) 来?

  • 是的,如果事务 B 在更新之前查询一次数据,这个查询返回的 k 的值确实是 1。
  • 但是,当它要去更新数据的时候,就不能再在历史版本上更新了,否则事务 C 的更新就丢 失了。因此,事务 B 此时的 set k=k+1 是在(1,2)的基础上进行的操作。
  • 这里就用到了这样一条规则: 更新数据都是先读后写的,而这个读,只能读当前的 值,称为“当前读”(current read)。
  • 因此,在更新的时候,当前读拿到的数据是 (1,2),更新后生成了新版本的数据 (1,3),这 个新版本的 row trx_id 是 101。
  • 所以,在执行事务 B 查询语句的时候,一看自己的版本号是 101,最新数据的版本号也是 101,是自己的更新,可以直接使用,所以查询得到的 k 的值是 3。
  • 除了 update 语句外,select 语句如果加 锁,也是当前读。
    • mysql> select k from t where id=1 lock in share mode; 读锁(S 锁,共享锁)
    • mysql> select k from t where id=1 for update; 写锁(X 锁,排他锁)

假设事务 C 不是马上提交的,而是变成了下面的事务 C’

事务 C’的不同是,更新后并没有马上提交,在它提交前,事务 B 的更新语句先发起了。前面说过了,虽然事务 C’还没提交,但是 (1,2) 这个版本也已经生成了,并且是当前的 最新版本。那么,事务 B 的更新语句会怎么处理呢?

  • 上一篇文章中提到的“两阶段锁协议”就要上场了
  • 事务 C’ 没提交,也 就是说 (1,2) 这个版本上的写锁还没释放。而事务 B 是当前读,必须要读最新版本,而且 必须加锁,因此就被锁住了,必须等到事务 C’释放这个锁,才能继续它的当前读。
  • 这样一致性读、当前读和行锁就串起来了.在一致性读的环境下,事务C" 执行更新,此时C"没有commit,事务B就开启了,因为事务B要进行当前读,获取最新的信息,读的时候要加锁(读完立马更新),但是此时事务C"还没有commit,锁(行锁)还没释放,所以事务B需要等待事务C"释放锁之后才能获取锁,然后才能执行当前读,读到事务B更新了的(1,2),既而更新为(1,3),同时因为(1,3)是事务B自身更新的,所以事务B在查询id=1的值时,自然而然的就查到了k为3. 但是对于事务A来说,查询的时候,因为事务C"和事务B的更新都是在事务A开始之后,所以对于事务A都不可见,所以事务A读取到的值为1. 上面的分析同样适用于事务A/B/C

可重复读隔离级别RR核心

  • 核心就是一致性读(consistent read),正式因为一致性读的原因,所以本事务开始之后,就算其他事务更新了相关的值,此时本事务还是能查到本事务开始之前的值,而不是其他事务更新后的值.
  • 读提交的逻辑和可重复读的逻辑类似,它们最主要的区别是
    • 在可重复读隔离级别下,只需要在事务开始的时候创建一致性视图,之后事务里的其他查询都共用这个一致性视图;
    • 在读提交隔离级别下,每一个语句执行前都会重新算出一个新的视图。

读提交RC隔离级别

start transaction with consistent snapshot; 在都提交下与start transaction等效.

  • 事务 A 的查询语句的视图数组是在执行这个语句的时候创建的,时序上 (1,2)、(1,3) 的生成时间都在创建这个视图数组的时刻之前。
  • 但是(1,3) 还没提交,属于情况 1,不可见; (1,2) 提交了,属于情况 3,可见。
  • 所以,这时候事务 A 查询语句返回的是 k=2。
  • 显然地,事务 B 查询结果 k=3。

站在巨人的肩膀上摘苹果:

https://time.geekbang.org/column/intro/100020801