MySQL5.7 Read Committed事务隔离级别的研究-出现幻读

iyufygfd

事务隔离级别为Read Committed的情况下：

事务2不执行commit，可以读取到事务1新添加的记录。此处说明出现了幻读。

当事务隔离级别改为REPEATABLE-READ后，能够解决该问题即解决幻读。

幻读定义：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据。

session1执行事务1：只插入和提交

mysql> select @@global.tx_isolation;select @@tx_isolation;

+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED        |
+-----------------------+
1 row in set (0.01 sec)

+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from sun;
+--------+------+
| name   | age  |
+--------+------+
| summer |   26 |
+--------+------+
1 row in set (0.00 sec)

mysql> insert into sun values('aa','88');
Query OK, 1 row affected (0.00 sec)

mysql> commit;

session2执行事务2：只查询

mysql> select @@global.tx_isolation;select @@tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED        |
+-----------------------+
1 row in set (0.00 sec)

+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from sun;
+--------+------+
| name   | age  |
+--------+------+
| summer |   26 |
| aa     |   88 |
+--------+------+