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; |
mysql>begin ; |
|
mysql> insert into t1(c1,c2) values(1,1); |
|
mysql> select * from t1;
| |
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; |
mysql>begin ; |
|
mysql>insert into t1(c1,c2) values(1,1); |
|
mysql> select * from t1;
| |
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; |
mysql> select * from t1; +----+------+------+ |
|
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; |
mysql> select * from t1; +----+------+------+ |
|
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; |
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; (当前读) |
|
更新测试
mysql> set tx_isolation='read-committed'; | mysql> set tx_isolation='read-committed'; |
| mysql> select * from t1; |
mysql>begin ; |
|
mysql> select * from t1; |
|
mysql> select * from t1; | |
mysql>update t1 set c2=200 where c1=1; select * from t1; | |
mysql> select * from t1; mysql>update t1 set c2=100 where c1=1; 等待锁 | |
参考:
https://www.jianshu.com/p/47e6b959a66e