java - 尝试在 REPEATABLE-READ 隔离级别下在 MySQL 中创建幻像

Question

我试图通过使用 JDBC 来演示 MySQL 中的幻读。我明白在REPEATABLE-READ隔离级别下，幻像应该是可以的。但我不能让一个发生。我的交易设置如下:

交易 1:

querySetOne[0] = "use adventureworks";
querySetOne[1] = "select * from vendorcontact where ContactTypeID between 10 and 30";
querySetOne[2] = "select sleep(20)";
querySetOne[3] = "select * from vendorcontact where ContactTypeID between 10 and 30";
querySetOne[4] = "COMMIT";

交易 2:

querySetTwo[0] = "use adventureworks";
querySetTwo[1] = "select sleep(2)";
querySetTwo[2] = "insert into vendorcontact values (105, 700, 20, NULL)";
querySetTwo[3] = "COMMIT";

我将它们放在数组 b/c 中，我使用 Statement.execute() 方法来执行每一行，并将自动提交设置为 false。

为什么querySetOne[1]和querySetOne[3]在可重复读隔离级别下返回相同的结果？？

Answer 1

SQL 标准似乎表明幻读在可重复读隔离中是可能的，但并没有说它们是必需的。

特别是，MySQL 的 InnoDB 引擎支持 consistent reads在可重复读取隔离下，这意味着事务中的第一次读取创建一个快照，稍后在事务中再次读取使用相同的快照。

MySQL documentation这是关于可重复读取隔离的:

All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.