【眼见为实】自己动手实践理解数据库READ COMMITTED && MVCC

[READ COMMITTED]

首先设置数据库隔离级别为读已提交(READ COMMITTED):

set global transaction isolation level READ COMMITTED ;
set session transaction isolation level READ COMMITTED ;

[READ COMMITTED]能解决的问题

我们来看一下为什么[READ COMMITTED]如何解决脏读的问题:

事务1

START TRANSACTION;
① UPDATE users SET state=1 WHERE id=1;
② SELECT sleep(10);
ROLLBACK;

事务2

START TRANSACTION;
① SELECT * FROM users WHERE id=1;
COMMIT;

事务1先于事务2执行。

事务1的执行信息

[SQL 1]START TRANSACTION;
受影响的行: 0
时间: 0.001s

[SQL 2]
UPDATE users SET state=1 WHERE id=1;
受影响的行: 1
时间: 0.001s

[SQL 3]
SELECT sleep(10);
受影响的行: 0
时间: 10.000s

[SQL 4]
ROLLBACK;
受影响的行: 0
时间: 0.051s

事务2的执行信息

[SQL 1]START TRANSACTION;
受影响的行: 0
时间: 0.001s

[SQL 2]
SELECT * FROM users WHERE id=1;
受影响的行: 0
时间: 0.005s

[SQL 3]
COMMIT;
受影响的行: 0
时间: 0.001s

最终结果

结论: 读已提交[READ COMMITTED]隔离级别可以解决脏读的问题,但是貌似不是按照二级封锁协议解决的脏读问题。

分析: 因为读已提交[READ COMMITTED]隔离级别对应数据库的二级封锁协议。二级封锁协议在修改数据之前对其加X锁,直到事务结束释放X锁。读数据之前必须加S锁,读完即可释放S锁。因为事务1先执行修改,修改前申请持有X锁,事务结束释放X锁。持锁时间段为[SQL 2]开始前到[SQL 4]结束,持锁时间大约为10.056s。事务2在事务1之后进行读操作,按照二级封锁协议所说,事务2在读数据之前会申请持有S锁。但是事务1持有此数据的X锁,所以事务2必须等待事务1释放X锁,这个过程大约在10秒左右。但是我们通过事务2的执行信息可以看到执行查询的时间为0.005s,远远小于10秒。所以我们可以大胆推断Mysql的InnoDB引擎在[READ COMMITTED]隔离级别下对读操作没有加锁。但是[READ COMMITTED]隔离级别确实解决了脏读的问题,那么Mysql是怎么解决的脏读问题呢?

MVCC(多版本并发控制)

答案是多版本并发控制(MVCC),可以认为是行级锁的一个变种,但是它在很多情况下都避免了加锁操作,因此开销更低。实现了非堵塞的读操作,写操作也只需要锁定必要的行。 如果我们理解了MVCC的工作机制,也就可以理解[READ COMMITTED]隔离级别是如何解决脏读问题的。

MVCC具体是如下操作的:

SELECT

InnoDB会根据以下两个条件检查记录:

①InnoDB只会查找版本早于当前事务版本的数据行(也就是,行的版本号小于或是等于事务的系统版本 号),这样可以确保数据读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或修改过的。

②行的删除版本号要么未定义,要么大于当前事务版本号。这可以确保事务读取到的行,在事务开始之前未被删除。

只有符合上述两个条件的记录,才能返回作为查询结果。

INSERT

InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

DELETE

InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

UPDATE

InnoDB为新插入的每一行保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为行删除标识。

Innodb为每行记录都实现了三个隐藏字段:

6字节的事务ID(DBTRXID) 7字节的回滚指针(DBROLLPTR) 隐藏的ID 6字节的事物ID用来标识该行所述的事务

事务1会执行如下操作: ①用排他锁锁定该行 ②记录redo log ③把该行修改前的值Copy到undo log,即上图中下面的行 ④修改当前行的值,填写事务编号,使回滚指针指向undo log中的修改前的行

如果事务1最后执行COMMIT操作,则什么操作都不用做。如果执行ROLLBACK操作,则需要通过回滚指针从undo log中还原修改前的数据。

read view 判断当前版本数据项是否可见

在InnoDB中,创建一个新事务的时候,InnoDB会将当前系统中的活跃事务列表(trxsys->trxlist)创建一个副本(read view),副本中保存的是系统当前不应该被本事务看到的其他事务id列表。当用户在这个事务中要读取该行记录的时候,InnoDB会将该行当前的版本号与该read view进行比较。 具体的算法如下:

设该行的当前事务id为trxid,read view中最早的事务id为trxidmin, 最迟的事务id为trxid_max。

  • 如果trxid< trxid_min的话,那么表明该行记录所在的事务已经在本次新事务创建之前就提交了,所以该行记录的当前值是可见的。
  • 如果trxid>trxid_max的话,那么表明该行记录所在的事务在本次新事务创建之后才开启,所以该行记录的当前值不可见。
  • 如果trxidmin <= trxid <= trxidmax, 那么表明该行记录所在事务在本次新事务创建的时候处于活动状态,从trxidmin到trxidmax进行遍历,如果trxid等于他们之中的某个事务id的话,那么不可见。 从该行记录的DBROLLPTR指针所指向的回滚段中取出最新的undo-log的版本号的数据,将该可见行的值返回。

需要注意的是,新建事务(当前事务)与正在内存中commit 的事务不在活跃事务链表中

对应源代码如下:

函数:read_view_sees_trx_id。
read_view中保存了当前全局的事务的范围:
【low_limit_id, up_limit_id】
1. 当行记录的事务ID小于当前系统的最小活动id,就是可见的。
      if (trx_id < view->up_limit_id) {
        return(TRUE);
      }
2. 当行记录的事务ID大于当前系统的最大活动id(也就是尚未分配的下一个事务的id),就是不可见的。
      if (trx_id >= view->low_limit_id) {
        return(FALSE);
      }
3. 当行记录的事务ID在活动范围之中时,判断是否在活动链表中,如果在就不可见,如果不在就是可见的。
      for (i = 0; i < n_ids; i++) {
        trx_id_t view_trx_id
          = read_view_get_nth_trx_id(view, n_ids - i - 1);
        if (trx_id <= view_trx_id) {
        return(trx_id != view_trx_id);
        }
      }

事务2会执行如下操作: 理想状态下,事务1的事务id=1,事务2的事务id=2。因为事务2执行时查询时,事务1正处于等待状态。所以read view为{1},事务2读取的数据行 trxid=1,read view中最早的事务id为trxidmin=1, 最迟的事务id为trxidmax=1。因为trxidmin <= trxid <= trxidmax,并且trxidmin = trxid = trxidmax,说明该行记录所在事务在本次新事务创建的时候处于活动状态,不可见。所以从该行记录的DBROLL_PTR指针所指向的回滚段中取出最新的undo-log的版本号的数据,将该可见行的值返回。所以不会出现脏读的现象。

[READ COMMITTED]不能解决的问题

[READ COMMITTED]隔离级别解决不了不可重复读的问题,一个事务中两次读取可能会出现不同的结果。 我们来模拟一下:

事务1

START TRANSACTION;
① SELECT sleep(5);
② UPDATE users SET state=1 WHERE id=1;
COMMIT;

事务2

START TRANSACTION;
① SELECT * FROM users WHERE id=1;
② SELECT sleep(10);
③ SELECT * FROM users WHERE id=1;
COMMIT;

事务1先于事务2执行。

执行结果

结论: 读已提交[READ COMMITTED]隔离级别不能解决不可重复读的问题,但是如果按照上面所说,Mysql的InnoDB引擎是通过read view来判断当前版本数据项是否可见的。那么读已提交[READ COMMITTED]隔离级别下应该也不会出现不可重复读的问题,但是现实并不是。

分析: 读已提交[READ COMMITTED]隔离级别下出现不可重复读是由于read view的生成机制造成的。在[READ COMMITTED]级别下,只要当前语句执行前已经提交的数据都是可见的。在每次语句执行的过程中,都关闭read view, 重新创建当前的一份read view。这样就可以根据当前的全局事务链表创建read view的事务区间。

那么在我们模拟的事务中,事务1的事务id trxid1=1,事务2的事务id trxid2=2。假设事务2第一次读取数据前的此行数据的事务id=0。事务2中语句①执行前生成的read view为{1},trxidmin=1,trxidmax=1。因为trxid(0)< trxid_max(1),此行数据对本次事务可见,将该可见行的值state=0返回。语句①执行后等待10秒,第5秒时事务1对数据加X锁进行修改操作0->1,然后提交事务释放锁。语句②执行前生成的read view为{null},说明当前系统中的不存在其他的活跃事务,也就不存在不应该被本事务看到的其他事务,因此该行记录的当前值state=1可见。就出现两次读取数据不一致的问题,也就是不可重复读。

不可重复读的问题在Mysql默认的隔离级别[REPEATABLE READ]中得到了解决。至于是如何解决的,先卖个关子。可以给个小提示,也是和read view的生成机制有关。预知后事如何,请看下篇博客。

-----END-----

原文发布于微信公众号 - 撸码那些事(lumanxs)

原文发表时间:2018-05-11

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java架构师历程

MySQL的三大引擎

InnoDB和MyISAM是在使用MySQL最常用的两个表类型,各有优缺点,视具体应用而定。基本 的差别为:

1432
来自专栏北京马哥教育

优化临时表使用,SQL语句性能提升100倍

【问题现象】 线上mysql数据库爆出一个慢查询,DBA观察发现,查询时服务器IO飙升,IO占用率达到100%, 执行时间长达7s左右。 SQL语句如下: SE...

2688
来自专栏微信公众号:Java团长

深入理解Mysql——锁、事务与并发控制

本文对锁、事务、并发控制做一个总结,看了网上很多文章,描述非常不准确。如有与您观点不一致,欢迎有理有据的拍砖!

903
来自专栏恰同学骚年

Microsoft SQL Server中的事务与并发详解

  事务是数据库并发控制的基本单位,一条或者一组语句要么全部成功,对数据库中的某些数据成功修改; 要么全部不成功,数据库中的数据还原到这些语句执行之前的样子。

803
来自专栏MYSQL轻松学

insert事务产生duplicate key error引发的死锁分析

先看程序报错: 2017-06-12 21:18:40.856 [ForkJoinPool.commonPool-worker-12] ERROR com....

4114
来自专栏岑玉海

Hive Tunning(三) 最佳实践

在上一讲的基础上,我们来做来一个实际的例子来展示如何在实操中进行高效的hive查询作业。 (1)首先我们建立一个表 CREATE EXTERNAL TA...

3567
来自专栏在线教育技术团队专栏

MySQL 开发实践 8 问,你能 hold 住几个?

最近研发的项目对 DB 依赖比较重,梳理了这段时间使用 MySQL 遇到的8个比较具有代表性的问题。

8.3K5
来自专栏携程技术中心

干货 | MySQL锁之源码探索

1122
来自专栏小怪聊职场

MySQL(一)|性能分析方法、SQL性能优化和MySQL内部配置优化

3628
来自专栏更流畅、简洁的软件开发方式

利用虚拟硬盘(把内存当作硬盘)来提高数据库的效率(目前只针对SQL Server 2000)可以提高很多

      虚拟硬盘:就是把内存当作硬盘来用,比如有2G的内存,那么可以拿出来1G的内存当作硬盘来用。       自从知道了“虚拟硬盘”这个东东,我就一直在想...

3265

扫码关注云+社区