为什么MYSQL在REPEATABLE READ下仍然返回未提交的记录？

Question

对于上面的查询，为什么会话2仍然可以返回记录2？由于会话1尚未提交值为2.btw的记录，因此事务隔离级别是可重复读取，工具是MYSQL工作台。

CREATE TABLE `new_table` (
`C1` int(11) NOT NULL,
PRIMARY KEY (`C1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


session 1
START TRANSACTION;
INSERT INTO new_table VALUES (2)

Session 2
SELECT * FROM new_table;

Answer 1

我无法重现这个问题：

-- SESSION 1

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.19    |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT
    ->   @@GLOBAL.tx_isolation,
    ->   @@SESSION.tx_isolation,
    ->   @@GLOBAL.autocommit,
    ->   @@SESSION.autocommit;
+-----------------------+------------------------+---------------------+----------------------+
| @@GLOBAL.tx_isolation | @@SESSION.tx_isolation | @@GLOBAL.autocommit | @@SESSION.autocommit |
+-----------------------+------------------------+---------------------+----------------------+
| REPEATABLE-READ       | REPEATABLE-READ        |                   1 |                    1 |
+-----------------------+------------------------+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `new_table`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `new_table` (
    ->   `C1` INT NOT NULL,
    ->   PRIMARY KEY (`C1`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `new_table`
    -> VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT `C1`
    -> FROM `new_table`;
+----+
| C1 |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

-- SESSION 2

mysql> SELECT
    ->   @@GLOBAL.tx_isolation,
    ->   @@SESSION.tx_isolation,
    ->   @@GLOBAL.autocommit,
    ->   @@SESSION.autocommit;
+-----------------------+------------------------+---------------------+----------------------+
| @@GLOBAL.tx_isolation | @@SESSION.tx_isolation | @@GLOBAL.autocommit | @@SESSION.autocommit |
+-----------------------+------------------------+---------------------+----------------------+
| REPEATABLE-READ       | REPEATABLE-READ        |                   1 |                    1 |
+-----------------------+------------------------+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> SELECT `C1`
    -> FROM `new_table`;
Empty set (0.00 sec)

使用MySQL Workbench 6.3 (版本6.3.6 build 511 CE (64位)社区)：

​

​

Answer 2

当我们在MYSQL工作台中打开两个查询窗口时，这两个查询窗口属于同一个会话，所以问题就发生了。