MySQL事务隔离解析

 

目录

1.隔离级别

2.事务隔离的实现

3.事务的启动方式

4.“快照”在MVCC里的实现

5.事务更新的逻辑

---

1.隔离级别

SQL标准的事务隔离级别包括： 读未提交（ read uncommitted） 、读提交（ read committed） 、 可重复读（ repeatable read） 和串行化（ serializable ）。读未提交是指， 一个事务还没提交时， 它做的变更就能被别的事务看到。读提交是指， 一个事务提交之后， 它做的变更才会被其他事务看到。可重复读是指， 一个事务执行过程中看到的数据， 总是跟这个事务在启动时看到的数据是一致的。 当然在可重复读隔离级别下， 未提交变更对其他事务也是不可见的。串行化， 顾名思义是对于同一行记录， “写”会加“写锁”， “读”会加“读锁”。 当出现读写锁冲突的时候， 后访问的事务必须等前一个事务执行完成， 才能继续执行。

下面用一个例子来说明四个级别的不同结果：

mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);

事务A	事务B
启动事务查询得到值1	启动事务
	查询得到值1
	将1改成2
查询得到值V2
	提交事务B
查询得到值V2
提交事务A
查询得到值V3

1.若隔离级别是“读未提交”， 则V1的值就是2。 这时候事务B虽然还没有提交， 但是结果已经被A看到了。 因此， V2、 V3也都是2。

2.若隔离级别是“读提交”， 则V1是1， V2的值是2。 事务B的更新在提交后才能被A看到。 所以，V3的值也是2。
3.若隔离级别是“可重复读”， 则V1、 V2是1， V3是2。 之所以V2还是1， 遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
4.若隔离级别是“串行化”， 则在事务B执行“将1改成2”的时候， 会被锁住。 直到事务A提交后，事务B才可以继续执行。 所以从A的角度看， V1、 V2值是1， V3的值是2。

在实现上， 数据库里面会创建一个视图， 访问的时候以视图的逻辑结果为准。 在“可重复读”隔离级别下， 这个视图是在事务启动时创建的， 整个事务存在期间都用这个视图，使用“可重复读”隔离级别就很方便。 事务启动时的视图可以认为是静态的， 不受其他事务更新的影响。 在“读提交”隔离级别下， 这个视图是在每个SQL语句开始执行的时候创建的。 这里需要注意的是， “读未提交”隔离级别下直接返回记录上的最新值， 没有视图概念； 而“串行化”隔离级别下直接用加锁的方式来避免并行访问。可以看到在不同的隔离级别下， 数据库行为是有所不同的。 Oracle数据库的默认隔离级别其实就是“读提交”， 因此对于一些从Oracle迁移到MySQL的应用， 为保证数据库隔离级别的一致，MySQL的隔离级别设置为“读提交”。

2.事务隔离的实现

在MySQL中， 实际上每条记录在更新的时候都会同时记录一条回滚操作。 记录上的最新值， 通过回滚操作， 都可以得到前一个状态的值。假设一个值从1被按顺序改成了2、 3、 4， 在回滚日志里面就会有类似下面的记录。

当前值是4， 但是在查询这条记录的时候， 不同时刻启动的事务会有不同的read-view。 如图中看到的， 在视图A、 B、 C里面， 这一个记录的值分别是1、 2、 4， 同一条记录在系统中可以存在多个版本， 就是数据库的多版本并发控制（ MVCC） 。 对于read-view A， 要得到1， 就必须将当前值依次执行图中所有的回滚操作得到。同时你会发现， 即使现在有另外一个事务正在将4改成5， 这个事务跟read-view A、 B、 C对应的事务是不会冲突的。

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

3.事务的启动方式

MySQL的事务启动方式有以下几种：
1. 显式启动事务语句， begin 或 start transaction。 配套的提交语句是commit， 回滚语句是rollback。
2. set autocommit=0， 这个命令会将这个线程的自动提交关掉。 意味着如果你只执行一个select语句， 这个事务就启动了， 而且并不会自动提交。 这个事务持续存在直到你主动执行commit 或 rollback 语句， 或者断开连接。有些客户端连接框架会默认连接成功后先执行一个set autocommit=0的命令。 这就导致接下来的查询都在事务中， 如果是长连接， 就导致了意外的长事务。
因此建议使用set autocommit=1, 通过显式语句的方式来启动事务。但是对于一个需要频繁使用事务的业务， 第二种方式每个事务在开始时都不需要主动执行一次 “begin”， 减少了语句的交互次数。 如果你也有这个顾虑， 建议使用commit work and chain语法。在autocommit为1的情况下， 用begin显式启动的事务， 如果执行commit则提交事务。 如果执行commit work and chain， 则是提交事务并自动启动下一个事务， 这样也省去了再次执行begin语句的开销。 同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

如何避免长事务对业务的影响？
可以从应用开发端和数据库端来看。 首先，从应用开发端来看：
1. 确认是否使用了set autocommit=0。这个确认工作可以在测试环境中开展，把MySQL的general_log开起来，然后随便跑一个业务逻辑，通过general_log的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成1。
2. 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用begin/commit框起来。我见过有些是业务并没有这个需要，但是也把好几个select语句放到了事务中。这种只读事务可以去掉。
3. 业务连接数据库的时候，根据业务本身的预估，通过SET MAX_EXECUTION_TIME命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。其次，从数据库端来看：
1. 监控 information_schema.Innodb_trx表，设置长事务阈值，超过就报警/或者kill；
2. Percona的pt-kill这个工具不错，推荐使用；
3. 在业务功能测试阶段要求输出所有的general_log，分析日志行为提前发现问题；
4. 如果使用的是MySQL 5.6或者更新版本，把innodb_undo_tablespaces设置成2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

4.“快照”在MVCC里的实现

在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。InnoDB里面每个事务有一个唯一的事务ID，叫作transaction id。它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记为row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。也就是说，数据表中的一行记录，其实可能有多个版本(row)，每个版本有自己的row trx_id。如图所示，就是一个记录被多个事务连续更新后的状态。

图中虚线框里是同一行数据的4个版本，当前最新版本是V4， k的值是22，它是被transaction id为25的事务更新的，因此它的row trx_id也是25。图2中的三个虚线箭头，就是回滚日志undo log；而V1、 V2、 V3并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。比如，需要V2的时候，就是通过V4依次执行U3、 U2算出来。

在实现上， InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID。 “活跃”指的就是，启动了但还没提交。数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（ read-view）。而数据版本的可见性规则，就是基于数据的row trx_id和这个一致性视图的对比结果得到的。这个视图数组把所有的row trx_id 分成了几种不同的情况。

事务版本的可见性规则：

这样，对于当前事务的启动瞬间来说，一个数据版本的row trx_id，有以下几种可能：
1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
3. 如果落在黄色部分，那就包括两种情况：a. 若 row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见；b. 若 row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见。InnoDB利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建 快照”的能力。
以下面为例：

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开始前，系统里面只有一个活跃事务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又成为了历史版本。在事务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. 版本已提交，而且是在视图创建前提交的，可见

对应上述例子：(1,3)还没提交，属于情况1，不可见；(1,2)虽然提交了，但是是在视图数组创建之后提交的，属于情况2，不可见；(1,1)是在视图数组创建之前提交的，可见。

5.事务更新的逻辑

对于事务中有更新语句的情况，更新数据都是先读后写的，而这个读，只能读当前的 值，称为“当前读”（ current read）。

当要去更新数据的时候，就不能再在历史版本上更新了，否则事务C的更新就丢失了。因此，事务B此时的set k=k+1是在（ 1,2）的基础上进行的操作。其实，除了update语句外， select语句如果加锁，也是当前读。

所以，如果把事务A的查询语句select * from t where id=1修改一下，加上lock in share mode 或for update，也都可以读到版本号是101的数据，返回的k的值是3。下面这两个select语句，就是分别加了读锁（ S锁，共享锁）和写锁（ X锁，排他锁）。

mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;

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

事务C’的不同是，更新后并没有马上提交，在它提交前，事务B的更新语句先发起了。前面说过了，虽然事务C’还没提交，但是(1,2)这个版本也已经生成了，并且是当前的最新版本。这时候“两阶段锁协议”就要上场了。事务C’没提交，也就是说(1,2)这个版本上的写锁还没释放。而事务B是当前读，必须要读最新版本，而且必须加锁，因此就被锁住了，必须等到事务C’释放这个锁，才能继续它的当前读。

可重复读的核心就是一致性读（ consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

 在这里，“start transaction with consistent snapshot; ”的意思是从这个语句开始，创建一个持续整个事务的一致性快照。所以，在读提交隔离级别下，这个用法就没意义了，等效于普通的start transaction。这时，事务A的查询语句的视图数组是在执行这个语句的时候创建的，时序上(1,2)、 (1,3)的生成时间都在创建这个视图数组的时刻之前。但是，在这个时刻：(1,3)还没提交，属于情况1，不可见；(1,2)提交了，属于情况3，可见。所以，这时候事务A查询语句返回的是k=2。显然地，事务B查询结果k=3。

InnoDB的行数据有多个版本，每个数据版本有自己的row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据row trx_id和一致性视图确定数据版本的可见性。对于可重复读，查询只承认在事务启动前就已经提交完成的数据；对于读提交，查询只承认在语句启动前就已经提交完成的数据；而当前读，总是读取已经提交完成的最新版本。