MySQL5.6 REPEATABLE-READ 的一些有趣现象

MySQL5.6 REPEATABLE-READ 可重复读取的特性不是在事务开启时启动，而是在第一次 select 时启动

session1 和 session2 的事务隔离级别都是 REPEATABLE-READ，表结构如下：

-- 表结构
CREATE TABLE `t` (
  `id` bigint(20) NOT NULL,
  `ver` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

在 session 1 commit 之前，session2 读取记录

session1	session2	备注
begin;	begin;	ver = 0
UPDATE t set ver = 1 where id = 1;
	select * from t where id = 1;	ver = 0
commit;		ver = 1
	select * from t where id = 1;	ver = 0
begin;
UPDATE t set ver = 2 where id = 1;
	select * from t where id = 1;	ver = 0
commit;		ver = 2
	select * from t where id = 1;	ver = 0
	rollback;

在 session1 commit 之后，session2 读取记录

session1	session2	备注
begin;	begin;	ver = 0
UPDATE t set ver = 1 where id = 1;
commit;		ver = 1
	select * from t where id = 1;	ver = 1
begin;
UPDATE t set ver = 2 where id = 1;
commit;		ver = 2
	select * from t where id = 1;	ver = 1
begin;
UPDATE t set ver = 3 where id = 1;
	select * from t where id = 1;	ver = 1
commit;		ver = 3
	rollback;