该实验中:session A 的第一条语句,发生在session B的 insert语句提交之前,所以session A中的第二条select还是不能读取到数据。因为RR中的一致性读是以事务中第一个select语句执行的时间点作为snapshot建立的时间点的。而此时,session B的insert语句还没有执行,所以读取不到数据。
实验4:
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> select * from t1;
Empty set (0.00 sec)
mysql> insert into t1(c1,c2) values(1,1),(2,2);
mysql> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | 2 |
+----+------+
2 rows in set (0.01 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)
该实验说明:本事务中进行修改的数据,即使没有提交,在本事务中的后面也可以读取到。update 语句因为进行的是“当前读”,所以它可以修改成功。
2)对于RC隔离就简单多了:
With isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.
事务中每一次读取都是以当前的时间点作为判断是否提交的实际点,也即是 reads its own fresh snapshot.
RC是语句级多版本(事务的多条只读语句,创建不同的ReadView,代价更高),RR是事务级多版本(一个ReadView);
3. MySQL ��事务开始的时间
一般我们会认为 begin/start transaction 是事务开始的时间点,也就是一旦我们执行了 start transaction,就认为事务已经开始了,其实这是错误的。上面的实验也说明了这一点。事务开始的真正的时间点(LSN),是 start transaction 之后执行的第一条语句,不管是什么语句,不管成功与否。
但是如果你想要达到将 start transaction 作为事务开始的时间点,那么我们必须使用:
START TRANSACTION WITH consistent snapshot
它的含义是:执行 start transaction 同时建立本事务一致性读的 snapshot . 而不是等到执行第一条语句时,才开始事务,并且建立一致性读的 snapshot .
The WITH CONSISTENT SNAPSHOT modifier starts a for storage engines that are capable of it. This applies only to InnoDB. The effect is the same as issuing a START TRANSACTION followed by a SELECT from any InnoDB table. See Section 14.2.2.2, “Consistent Nonlocking Reads”. The WITH CONSISTENT SNAPSHOT modifier does not change the current transaction , so it provides a consistent snapshot only if the current isolation level is one that permits a consistent read. The only isolation level that permits a consistent read is . For all other isolation levels, the WITH CONSISTENT SNAPSHOT clause is ignored. As of MySQL 5.7.2, a warning is generated when the WITH CONSISTENT SNAPSHOT clause is ignored.