The transaction isolation level is repeatable read in case
Transaction 2 does not execute a commit and cannot read the newly added record for transaction 1.
Transaction 2 executes a commit and can read the newly added record to transaction 1.
Session1 Execution Transaction 1: Insert and commit only
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 |
+------------+------+
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 |
+------------+------+
Rows in Set (0.00 sec)
Session2 Execution Transaction 2: Query and commit only
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 |
+------------+------+
Rows in Set (0.00 sec)
Transaction 2 cannot query to a record inserted in transaction 1 if not committed
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 |
+------------+------+
Transaction 2 can be queried for records inserted in transaction 1 after commit
MySQL5.7 REPEATABLE READ TRANSACTION ISOLATION level study.