1 查看当前数据库的版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27 |
+-----------+
1 row in set (0.00 sec)
2 查看数据库的当前隔离级别
mysql> show variables like '%isol%';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)
3 检查是否关闭自动提交，如自动提交打开，需要关闭。
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.01 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.01 sec)
4 开启一个新事务，运行一个范围查询
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from employees where id between 29 and 198;
+-----+--------+------------+
| id | name | manager_id |
+-----+--------+------------+
| 29 | Pedro | 198 |
| 72 | Pierre | 29 |
| 123 | Adil | 692 |
| 198 | John | 333 |
+-----+--------+------------+
4 rows in set (0.00 sec)
5 打开另一个会话，插入一条数据
mysql> insert into employees values (30,'TOM', 198);
Query OK, 1 row affected (0.01 sec)
6 在原来的会话中查询
mysql> select * from employees where id between 29 and 198;
+-----+--------+------------+
| id | name | manager_id |
+-----+--------+------------+
| 29 | Pedro | 198 |
| 72 | Pierre | 29 |
| 123 | Adil | 692 |
| 198 | John | 333 |
+-----+--------+------------+
4 rows in set (0.00 sec)
可以看到，查询的数据同之前相同，MySQL数据库在默认隔离级别下可以防止幻读。
7 提交后再查询
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from employees where id between 29 and 198;
+-----+--------+------------+
| id | name | manager_id |
+-----+--------+------------+
| 29 | Pedro | 198 |
| 30 | TOM | 198 |
| 72 | Pierre | 29 |
| 123 | Adil | 692 |
| 198 | John | 333 |
+-----+--------+------------+
5 rows in set (0.00 sec)
看到了另一个会话中插入的数据。
8 重新开一个事务，仍然运行一个范围查询
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from employees where id between 29 and 198;
+-----+--------+------------+
| id | name | manager_id |
+-----+--------+------------+
| 29 | Pedro | 198 |
| 30 | TOM | 198 |
| 72 | Pierre | 29 |
| 123 | Adil | 692 |
| 198 | John | 333 |
+-----+--------+------------+
5 rows in set (0.00 sec)
9 在另一个会话中更新一条数据
mysql> update employees set name='JACK' where id=30;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
10 在原来会话中重新运行查询
mysql> select * from employees where id between 29 and 198;
+-----+--------+------------+
| id | name | manager_id |
+-----+--------+------------+
| 29 | Pedro | 198 |
| 30 | TOM | 198 |
| 72 | Pierre | 29 |
| 123 | Adil | 692 |
| 198 | John | 333 |
+-----+--------+------------+
5 rows in set (0.00 sec)
看到的还是原来的数据
11 提交后查询
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from employees where id between 29 and 198;
+-----+--------+------------+
| id | name | manager_id |
+-----+--------+------------+
| 29 | Pedro | 198 |
| 30 | JACK | 198 |
| 72 | Pierre | 29 |
| 123 | Adil | 692 |
| 198 | John | 333 |
+-----+--------+------------+
5 rows in set (0.00 sec)
12 结论
MySQL8.0.27 在默认隔离级别下，可以防止幻读和不可重复读，但是读到的不是最新数据。
