MySQL 读提交和重复读隔离级别测试实验

Mysql默认隔离级别可重复读，我们会实验读提交和可重复读。
set tx_isolation='read-committed';
set tx_isolation='repeatable-read';
纸上得来终觉浅，要理解它们必须实践一把，当前读、快照一致性读、MVVC等等这些概念必须在实践中理解。

读提交测试

实验一：未提交读

mysql> set tx_isolation='read-committed';	mysql> set tx_isolation='read-committed';
	mysql> select * from t1; 0 rows in set (0.00 sec)
mysql>begin ;
mysql> insert into t1(c1,c2) values(1,1);
	mysql> select * from t1; 0 rows in set (0.00 sec)
	mysql>update t1 set c2=100 where c1=1; select * from t1; Empty set (0.00 sec)
mysql> select * from t1; +----+------+------+ | id | c2   | c1   | +----+------+------+ | 29 |    1 |    1 | +----+------+------+ 1 row in set (0.00 sec)

mysql> set tx_isolation='repeatable-read';	mysql> set tx_isolation='repeatable-read';
	mysql> select * from t1; 0 rows in set (0.00 sec)
mysql>begin ;
mysql>insert into t1(c1,c2) values(1,1);
	mysql> select * from t1; 0 rows in set (0.00 sec)
	mysql>update t1 set c2=100 where c1=1; 等待事务结束释放行锁
mysql> select * from t1; +----+------+------+ | id | c2   | c1   | +----+------+------+ | 29 |    1 |    1 | +----+------+------+ 1 row in set (0.00 sec)

实验说明：未提交数据不可见，同时不会锁数据。 如果重复读级别，update锁住该行，等待事务提交。

 实验二：快照读

mysql> set tx_isolation='read-committed';	mysql> set tx_isolation='read-committed';
mysql> begin;
	mysql> select * from t1; Empty set (0.00 sec)   mysql> insert into t1(c1,c2) values(1,1);
mysql> select * from t1; +----+------+------+ | id | c2   | c1   | +----+------+------+ |  4 |    1 |    1 | +----+------+------+ 1 row in set (0.00 sec)
commit;
begin;
mysql> select * from t1; Empty set (0.00 sec)
	mysql> select * from t1; Empty set (0.00 sec)   mysql> insert into t1(c1,c2) values(1,1);
mysql> select * from t1; +----+------+------+ | id | c2   | c1   | +----+------+------+ |  4 |    1 |    1 | +----+------+------+ 1 row in set (0.00 sec)

RC级别中每次select读取最新快照数据，与RR级别不一样。

重复读测试

select 实验

mysql> set tx_isolation='repeatable-read';	mysql> set tx_isolation='repeatable-read';
mysql> begin;
	mysql> select * from t1; Empty set (0.00 sec)   mysql> insert into t1(c1,c2) values(1,1);
mysql> select * from t1; +----+------+------+ | id | c2   | c1   | +----+------+------+ |  4 |    1 |    1 | +----+------+------+ 1 row in set (0.00 sec)
commit;
begin;
mysql> select * from t1; Empty set (0.00 sec)
	mysql> select * from t1; Empty set (0.00 sec)   mysql> insert into t1(c1,c2) values(1,1);
mysql> select * from t1; Empty set (0.00 sec)

RR 级别中 begin  select 或begin XX select  ，select先后不一样导致结果不一样。

第一个begin：不是以begin开始的时间点作为snapshot建立时间点，而是以第一条select语句的时间点作为snapshot建立的时间点。

第二个begin：一致性读（快照读），是以第一条select语句的执行点作为snapshot建立的时间点的，即使是不同表的select语句。这里因为session A在insert之前对 t 表执行了select，所以建立了snapshot，所以后面的select * from t1 不能读取到insert的插入的值。

实验三：当前读

session A	session B
mysql> set tx_isolation='repeatable-read';	mysql> set tx_isolation='repeatable-read'; mysql> select * from t1; Empty set (0.00 sec)
mysql>begin;
mysql> select * from t1; Empty set (0.00 sec)
	mysql> insert into t1(c1,c2) values(1,1),(2,2); mysql> select * from t1; +----+------+------+ | id | c2   | c1   | +----+------+------+ |  6 |    1 |    1 | |  7 |    2 |    2 | +----+------+------+ 2 rows in set (0.00 sec)
mysql> select * from t1; Empty set (0.00 sec) 如果读提交级别，查询有记录
mysql> update t1 set c2=100 where c1=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0   mysql> select * from t1; +----+------+ | c1 | c2   | +----+------+ |  1 |  100 | +----+------+ 1 row in set (0.00 sec) 该情况属于幻读
	mysql> select * from t1; +----+------+------+ | id | c2   | c1   | +----+------+------+ | 27 |    1 |    1 | | 28 |    2 |    2 | +----+------+------+ 2 rows in set (0.00 sec)

实验说明：本事务中进行修改的数据，即使没有提交，在本事务中的后面也可以读取到。update 语句因为进行的是“当前读”，所以它可以修改成功。 

START TRANSACTION测试

实验四：快照读

session A	session B
mysql> set tx_isolation='repeatable-read';	mysql> set tx_isolation='repeatable-read';
	mysql> select * from t1; Empty set (0.01 sec)
mysql> start transaction;
	mysql> insert into t1(c1,c2) values(1,1);
mysql> select * from t1; +----+------+ | c1 | c2   | +----+------+ |  1 |    1 | +----+------+ 1 row in set (0.00 sec)

mysql> set tx_isolation='repeatable-read';	mysql> set tx_isolation='repeatable-read';
	mysql> select * from t1; Empty set (0.01 sec)
mysql> start transaction with consistent snapshot; //一致性快照读
	mysql> insert into t1(c1,c2) values(1,1);
mysql> select * from t1; Empty set (0.00 sec)

start transaction(begin) 和 start tansaction with consistent snapshot的区别。

第一个实验说明，start transaction执行之后，事务并没有开始，所以insert发生在session A的事务开始之前，所以可以读到session B插入的值。

第二个实验说明，start transaction with consistent snapshot已经开始了事务，所以insert语句发生在事务开始之后，所以读不到insert的数据。

当前读(current read) 和 一致性读（快照读）

一致性读是指普通的select语句，不带 for update, in share mode 等等子句。使用的是undo中的提交的数据，不需要使用锁(MDL除外)。而当前读，是指update, delete, select for update, select in share mode等等语句进行的读，它们读取的是数据库中的最新的数据，并且会锁住读取的行和gap(RR隔离时)。如果不能获得锁，则会一直等待，直到获得或者超时。RC隔离级别的当前读没有gap lock，RC的update语句进行的是“半一致性读”，和RR的update语句的当前读不一样。

实验五： 快照读与当前读

mysql> set tx_isolation='repeatable-read';	mysql> set tx_isolation='repeatable-read';
	mysql> select * from t1; Empty set (0.01 sec)
mysql> start transaction with consistent snapshot;
	mysql> insert into t1(c1,c2) values(1,1);
mysql> select * from t1; Empty set (0.00 sec) （快照读）
mysql> select * from t1 for update; +----+------+------+ | id | c2   | c1   | +----+------+------+ | 40 |    1 |    1 | +----+------+------+ 1 row in set (0.00 sec) (当前读)

更新测试

mysql> set tx_isolation='read-committed';	mysql> set tx_isolation='read-committed';
	mysql> select * from t1; 0 rows in set (0.00 sec)
mysql>begin ;
mysql> select * from t1; +----+------+------+ | id | c2   | c1   | +----+------+------+ | 29 |    1 |    1 | +----+------+------+ 1 row in set (0.00 sec)
	mysql> select * from t1; +----+------+------+ | id | c2   | c1   | +----+------+------+ | 29 |    1 |    1 | +----+------+------+ 1 row in set (0.00 sec)
mysql>update t1 set c2=200 where c1=1; select * from t1; +----+------+------+ | id | c2   | c1   | +----+------+------+ | 29 |    1 |    1 | +----+------+------+ 1 row in set (0.00 sec)
	mysql> select * from t1; +----+------+------+ | id | c2   | c1   | +----+------+------+ | 29 |    1 |    1 | +----+------+------+ 1 row in set (0.00 sec) mysql>update t1 set c2=100 where c1=1; 等待锁

 

参考：

https://www.jianshu.com/p/47e6b959a66e 

https://www.cnblogs.com/wdy1184/p/10655180.html

一个线上SQL死锁异常分析：深入了解事务和锁

MySQL半一致性读原理解析-从源码角度解析

MySQL的RR隔离级别与幻读问题

MySQL+InnoDB semi-consitent read原理及实现分析