事务隔离级别为Repeatable read的情况下

事务2不执行commit，读取不到事务1新添加的记录。

事务2执行了commit，可以读取到事务1新添加的记录。 


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

mysql> select @@tx_isolation;

+-----------------+

| @@tx_isolation  |

+-----------------+

| REPEATABLE-READ |

+-----------------+

1 row in set (0.00 sec)


mysql> 

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)


mysql> select * from lisa;

+------------+------+

| name       | age  |

+------------+------+

| lisa       |   99 |

| sunlongwei |   27 |

| shabala    |  999 |

| balabala   | 9999 |

| balabala   | 9999 |

| hehe       | 9999 |

| hehe       | 9999 |

| bb         |    1 |

| bb         |    2 |

| bb         |    3 |

+------------+------+

10 rows in set (0.00 sec)


mysql> insert into lisa values('aa','9999');

Query OK, 1 row affected (0.00 sec)


mysql> insert into lisa values('cc','9999');

Query OK, 1 row affected (0.00 sec)


mysql> commit;

Query OK, 0 rows affected (0.00 sec)


mysql> select * from lisa;

+------------+------+

| name       | age  |

+------------+------+

| lisa       |   99 |

| sunlongwei |   27 |

| shabala    |  999 |

| balabala   | 9999 |

| balabala   | 9999 |

| hehe       | 9999 |

| hehe       | 9999 |

| bb         |    1 |

| bb         |    2 |

| bb         |    3 |

| aa         | 9999 |

| cc         | 9999 |

+------------+------+

12 rows in set (0.00 sec)


session2执行事务2：只查询和提交

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)


mysql> select * from lisa;

+------------+------+

| name       | age  |

+------------+------+

| lisa       |   99 |

| sunlongwei |   27 |

| shabala    |  999 |

| balabala   | 9999 |

| balabala   | 9999 |

| hehe       | 9999 |

| hehe       | 9999 |

| bb         |    1 |

| bb         |    2 |

| bb         |    3 |

+------------+------+

10 rows in set (0.00 sec)

//事务2如果不提交则无法查询到事务1中插入的记录


mysql> commit;

Query OK, 0 rows affected (0.00 sec)


mysql> select * from lisa;

+------------+------+

| name       | age  |

+------------+------+

| lisa       |   99 |

| sunlongwei |   27 |

| shabala    |  999 |

| balabala   | 9999 |

| balabala   | 9999 |

| hehe       | 9999 |

| hehe       | 9999 |

| bb         |    1 |

| bb         |    2 |

| bb         |    3 |

| aa         | 9999 |

| cc         | 9999 |

+------------+------+

//事务2提交后可以查询到事务1中插入的记录