实验验证：MySQL隔离级别与脏读、不可重复读、幻读
实验验证：MySQL隔离级别与脏读、不可重复读、幻读
查看、设置隔离级别
因为MySQL默认隔离级别是 repeatable read，所以在测试其他隔离级别时，需要手动设置，下面是查看和设置隔离级别的方法。
select @@transaction_isolation; -- 查看当前会话的隔离级别
set session transaction isolation level read uncommitted; -- 设置当前会话的隔离级别为 read uncommitted
select @@global.transaction_isolation; -- 查看系统全局的隔离级别
set global transaction isolation level read uncommitted; -- 设置系统全局的隔离级别为 read uncommitted
示例说明
本文下面的测试示例跑在MySQL5.7中，数据库定义为：
CREATE DATABASE `test` DEFAULT CHARACTER SET utf8mb4;
表定义为：
CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`v` varchar(50) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
插入数据：
mysql> insert into t1(id,v) values(1,'a'),(2,'b');
本文后续的示例，如果没有特别说明，每个会话都是在开始时通过 set session transaction isolation level xxx 的方式设置为对应的隔离级别。
READ-UNCOMMITTED
在 read uncommitted 隔离级别下，会出现脏读的问题，也就是两个同时进行的事务，一个事务对数据的改动，即使没有提交，也可以被另一个事务读到。看下面示例，同时开两个窗口A、B，每个窗口都设置当前会话的隔离级别为 read uncommitted 并且开启事务：
窗口A: use test;
窗口A: set session transaction isolation level read uncommitted; -- 设置会话A的隔离级别为 read uncommitted
窗口A: select @@transaction_isolation; -- 确认会话A的隔离级别，输出 READ-UNCOMMITTED，没问题
窗口B: use test;
窗口B: set session transaction isolation level read uncommitted; -- 设置会话B的隔离级别为 read uncommitted
窗口B: select @@transaction_isolation; -- 确认会话B的隔离级别，输出 READ-UNCOMMITTED，没问题
窗口A: begin; -- A开启事务
窗口B: begin; -- B开启事务
窗口A: select v from t1 where id=1; -- 窗口A中读原有的数据，输出为：a
窗口B: update t1 set v='AAA' where id=1; -- B中更新id=1的数据为v='AAA'，此时B中的事务尚未提交。
窗口A: select v from t1 where id=1; -- 窗口A中读出了还未被提交的数据，输出为：AAA
窗口B: rollback; -- B事务回滚
窗口A: select v from t1 where id=1; -- 窗口A中读原有的数据，输出为：a
由此可以看出，read uncommitted 隔离级别下，事务是可以读到其他事务中未提交的数据，即一个事务的执行的中间态有可能会影响到其他事务，这就是脏读。想象一下银行转账的场景如果用 read uncommitted 隔离级别，甲给乙转账1000块，先把甲的余额扣减1000块，还没有给乙加1000的时候，其他事务中读取到的甲和乙的总金额会莫名的少了1000块，甚至会出现各种不可接受的问题。
READ-COMMITTED
在 read committed 隔离级别下，不会出现上述的脏读问题，对数据的更新，只有事务提交之后才会被其他事务读出。把上面脏读的示例在 read committed 隔离级别下再次执行，结果如下（此示例省略隔离级别的设置）：
窗口A: use test;
窗口A: set session transaction isolation level read committed; -- 设置会话A的隔离级别为 read uncommitted
窗口A: select @@transaction_isolation; -- 确认会话A的隔离级别，输出 READ-COMMITTED，没问题
窗口B: use test;
窗口B: set session transaction isolation level read committed; -- 设置会话B的隔离级别为 read uncommitted
窗口B: select @@transaction_isolation; -- 确认会话B的隔离级别，输出 READ-COMMITTED，没问题
窗口A: begin; -- A开启事务
窗口B: begin; -- B开启事务
窗口A: select v from t1 where id=1; -- 窗口A中读原有的数据，输出为：a
窗口B: update t1 set v='AAA' where id=1; -- B中更新id=1的数据为v='AAA'，此时B中的事务尚未提交。
窗口B: select v from t1 where id=1; -- 窗口B读刚刚更新的数据，输出为：AAA
窗口A: select v from t1 where id=1; -- 窗口A执行相同的查询，输出依旧为原来的 a，因为B事务还没有提交，read committed隔离级别下不能读出未被提交的数据。
窗口B: commit; -- B事务提交
窗口A: select v from t1 where id=1; -- 窗口A中读出了提交后的最新数据，输出为：AAA
可以看到，和第一个示例不同在于，B事务没有提交时，中间态的数据是不会被A读到的，所以 read committed 隔离级别避免了脏读问题，但是会出现不可重复读的问题。上面的示例中，窗口A的前两次读出的结果为a，但是第三次读出的结果为AAA，同一个事务中执行多次相同的查询，结果原来值被更新的情况，这叫做不可重复读。和不可重复读类似，read committed 隔离级别还会出现幻读的问题，即一个事务中多次相同的读语句，第二次会读出第一次没有的新记录。看下面示例：
窗口A: begin; -- A开启事务
窗口A: select * from t1; -- 窗口A中读原有的所有数据，输出为：(1, 'AAA'), (2, 'b')
窗口B: insert into t1(id, v) values (3, 'c'); -- B中插入记录(3, 'c') 并自动提交
窗口B: select * from t1; -- 窗口B读数据，有刚刚插入的数据，输出为：(1, 'AAA'), (2, 'b'), (3, 'c')
窗口B: commit; -- B事务提交
窗口A: select * from t1; -- 窗口A再次执行相同的查询，输出为：(1, 'AAA'), (2, 'b'), (3, 'c')，比A上一次的查询多出了一条新记录。
REPEATABLE-READ
先把数据初始化：
truncate table test.t1
insert into test.t1(id,v) values(1,'a'),(2,'b');
在 repeatable read 隔离级别下可以避免不可重复读和幻读，注意，幻读也是可以被避免（很多书也提到了这一点），不过不是所有情况下都可以避免（大多没有提到这一点），所以这个说法有一些争议。我们在 repeatable read 隔离级别下再次执行之前不可重复读的示例，看会有什么不一样的结果：
窗口A: use test;
窗口A: set session transaction isolation level repeatable read; -- 设置会话A的隔离级别为 repeatable read
窗口A: select @@transaction_isolation; -- 确认会话A的隔离级别，输出 REPEATABLE-READ，没问题
窗口B: use test;
窗口B: set session transaction isolation level repeatable read; -- 设置会话B的隔离级别为 repeatable read
窗口B: select @@transaction_isolation; -- 确认会话B的隔离级别，输出REPEATABLE-READ，没问题
窗口A: begin; -- A开启事务
窗口B: begin; -- B开启事务
窗口A: select v from t1 where id=1; -- 窗口A中读原有的数据，输出为：a
窗口B: update t1 set v='AAA' where id=1; -- B中更新id=1的数据为v='AAA'，此时B中的事务尚未提交。
窗口B: select v from t1 where id=1; -- 窗口B读刚刚更新的数据，输出为：AAA
窗口A: select v from t1 where id=1; -- 窗口A执行相同的查询，输出依旧为原来的 a，因为B事务还没有提交，read committed隔离级别下不能读出未被提交的数据。
窗口B: commit; -- B事务提交
窗口A: select v from t1 where id=1; -- B事务已经提交，窗口A中读出的依旧为：a，避免了不可重复读的问题
窗口A: select v from t1 where id=1 for update; --窗口A中读出的AAA 改用加锁的方式执行和上次相同的查询，就读出了更新后的数据：AAA，依旧有不可重复读的问题
还是原来的『配方』，却有了不一样的『味道』。这里倒数第二个sql及以前的语句，都和前面示例完全相同，但是倒数第二行的查询结果却避免了不可重复读的问题。在最后一行相对倒数第二行只是改成锁定读的方式，结果就又不一样了，锁定读依然会出现不可重复读的情况，也就是前面提的争议点。
再来看之前幻读的示例在 repeatable read 隔离级别下的表现：
先把数据初始化：
truncate table test.t1
insert into test.t1(id,v) values(1,'a'),(2,'b');
窗口A: use test;
窗口A: set session transaction isolation level repeatable read; -- 设置会话A的隔离级别为 repeatable read
窗口A: select @@transaction_isolation; -- 确认会话A的隔离级别，输出 REPEATABLE-READ，没问题
窗口B: use test;
窗口B: set session transaction isolation level repeatable read; -- 设置会话B的隔离级别为 repeatable read
窗口B: select @@transaction_isolation; -- 确认会话B的隔离级别，输出REPEATABLE-READ，没问题
窗口A: begin; -- A开启事务
窗口A: select * from t1; -- 窗口A中读原有的所有数据，输出为：(1, 'a'), (2, 'b')
窗口B: insert into t1(id, v) values (3, 'c'); -- B中插入记录(3, 'c') 并自动提交
窗口B: select * from t1; -- 窗口B读数据，有刚刚插入的数据，输出为：(1, 'a'), (2, 'b'), (3, 'c')
窗口B: commit; -- B事务提交
窗口A: select * from t1; -- 窗口A再次执行相同的查询，输出依旧为：(1, 'a'), (2, 'b')，没有出现之前示例中新记录，避免了幻读问题
窗口A: select * from t1 for update; -- 改用锁定的方式执行和上次相同的查询，输出为：(1, 'a'), (2, 'b'), (3, 'c')，依旧有幻读问题。
从这个示例可以看出，repeatable read 隔离级别可以避免一致性非锁定读的幻读问题，也就是一个事务中多次相同的查询，不会查询新记录的情况。但是对于锁定读来说，依旧会出现幻读。
上面这两个示例都是显示地对读加锁来展示不能避免不可重复读、幻读的情况，像更新等自动加锁的操作也是会有相应的问题的，以幻读为例（不可重复类似，可自行测试）：
先把数据初始化：
truncate table test.t1
insert into test.t1(id,v) values(1,'a'),(2,'b');
窗口A: use test;
窗口A: set session transaction isolation level repeatable read; -- 设置会话A的隔离级别为 repeatable read
窗口A: select @@transaction_isolation; -- 确认会话A的隔离级别，输出 REPEATABLE-READ，没问题
窗口B: use test;
窗口B: set session transaction isolation level repeatable read; -- 设置会话B的隔离级别为 repeatable read
窗口B: select @@transaction_isolation; -- 确认会话B的隔离级别，输出REPEATABLE-READ，没问题
窗口A: begin; -- A开启事务
窗口A: select * from t1; -- 窗口A中读原有的所有数据，输出为：(1, 'a'), (2, 'b')
窗口B: insert into t1(id, v) values (3, 'c'); -- B中插入记录(3, 'c') 并自动提交
窗口B: select * from t1; -- 窗口B读数据，有刚刚插入的数据，输出为：(1, 'a'), (2, 'b'), (3, 'c')
窗口B: commit -- 窗口B提交
窗口A: select * from t1; -- 窗口A通过一致性非锁定读的方式，可重复读，输出为：(1, 'a'), (2, 'b')
窗口A: update t1 set v='CCC' where id=3; -- 之前没有查到id=3的记录，但是这里的更新语句却成功修改了一条记录：Query OK, 1 row affected (0.00 sec)
窗口A: select * from t1; -- 此时在窗口A再次通过一致性非锁定读的方式，就查到了上次查询没有查到的数据，输出为：(1, 'a'), (2, 'b'),(3, 'CCC')
总结一下，repeatable read 隔离级别可以针对一致性非锁定读避免不可重复读和幻读的问题，但是对于锁定读、更新等加锁的操作，依旧无法避免。
这里多次提到一致性非锁定读，MySQL是通过多版本并发控制（MVCC）来实现
SERIALIZABLE
在 serializable 隔离级别下，事务中的每条SQL会自动加读写锁，即使是上面说的『一致性非锁定读』。前面提到的一致性非锁定读只存在于read committed、repeatable read这两个隔离级别， serializable 隔离级别下所有的查询都是加锁的。
先把数据初始化：
truncate table test.t1
insert into test.t1(id,v) values(1,'a'),(2,'b');
窗口A: use test;
窗口A: set session transaction isolation level serializable; -- 设置会话A的隔离级别为 serializable
窗口A: select @@transaction_isolation; -- 确认会话A的隔离级别，输出 SERIALIZABLE，没问题
窗口B: use test;
窗口B: set session transaction isolation level serializable; -- 设置会话B的隔离级别为 serializable
窗口B: select @@transaction_isolation; -- 确认会话B的隔离级别，输出SERIALIZABLE，没问题
窗口A: begin; -- A开启事务
窗口B: begin; -- B开启事务
窗口A: select * from t1 where id=1; -- 窗口A中读id=1这条记录，此时会自动加读锁
窗口B: select * from t1 where id=1; -- 窗口B中也读id=1这条记录，此时也会自动加读锁，读锁与读锁是相关兼容的，因此不会被阻塞。
窗口B: update t1 set v='AAA' where id=1; -- 窗口B此时会被阻塞，因为更新操作加写锁，和窗口A加的读锁互斥。
窗口A: commit; -- A事务提交，释放之前加的读锁，此时B也会阻塞结束，执行完之前的更新操作。
因此，之前说过的不可重复读、幻读问题，在 serializable 隔离级别下都不存在。
仔细想下也不难理解，对于不可重复读，出现的原因在于事务两次相同的读中间，有其他事务更新了数据，因为读加锁，第一次读的时候数据就被锁定，其他事务不可能对此再做更新，因为不会出现不可重复读的问题。
对于幻读的问题，出现的原因是两次查询中间有其他事务新插入的数据，MySQL会通过Next-Key锁来锁定记录和前后区间，因此其他事务在插入时会阻塞，因此幻读问题也不会出现。
