数据世界的“契约精神”:MySQL 事务与并发控制深度解析

1. 为了便于演示，我们将mysql的默认隔离级别设置成读未提交 , 具体操作我们后面专门会讲，现在以使用为主

set global transaction isolation level READ UNCOMMITTED;

2. 需要重启终端，进行查看

quit

SELECT @@transaction_isolation;

3. 创建测试表

mysql> create table if not exists account(
    -> id int primary key,
    -> name varchar(50) not null default '',
    -> blance decimal(10,2) not null default 0.0
    -> )engine=innodb default charset=utf8;
Query OK, 0 rows affected, 1 warning (0.03 sec)

  4. 正常演示 - 证明事务的开始与回滚 

mysql> show variables like 'autocommit';     -- 查看事务是否自动提交
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> start transaction;                     -- 开启一个事务,使用begin命令也可
Query OK, 0 rows affected (0.00 sec)
 
mysql> savepoint save1;                       -- 创建一个保存点save1
Query OK, 0 rows affected (0.00 sec) 

mysql> insert into account values(1,'张三',100);   -- 插入一条记录
Query OK, 1 row affected (0.00 sec)

mysql> savepoint save2;                            -- 创建一个保存点save2
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(2,'李四',1000);   -- 再插入一条记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;                       -- 两条记录都存在了
+----+--------+---------+
| id | name   | blance  |
+----+--------+---------+
|  1 | 张三   |  100.00 |
|  2 | 李四   | 1000.00 |
+----+--------+---------+
2 rows in set (0.00 sec)

mysql> rollback to save2;                          -- 回滚到保存点save2
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;                      -- 一条记录没有了
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

mysql> rollback;                                  -- 直接rollback,回滚到最开始
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;              -- 所有刚刚的记录都没有了
Empty set (0.00 sec)

5. 非正常演示1 - 证明未commit，客户端崩溃，MySQL自动会回滚（隔离级别设置为读未提交）
 

-- 终端1
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

-- 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 插入数据
mysql> insert into account values(1,'张三',100);
Query OK, 1 row affected (0.00 sec)

-- 查询
mysql> select * from account;
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

-- 终端2
mysql> use transaction_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from account;
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

-- 终端1 异常退出 CTRL + \
mysql> Aborted

-- 终端2 查询
mysql> select * from account;
Empty set (0.00 sec)

6. 非正常演示2 - 证明commit了，客户端崩溃，MySQL数据不会在受影响，已经持久化

-- 终端1
mysql> use transaction_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

-- 表示当前表内没数据
Database changed
mysql> select * from account;
Empty set (0.00 sec)

-- 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 插入数据
mysql> insert into account values(1,'张三',100);
Query OK, 1 row affected (0.00 sec)

-- 提交事务
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

-- CTRL + \ 异常退出
mysql> Aborted

-- 终端2 能够查询到,因为已经持久化
mysql> select * from account;
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

7. 非正常演示3 - 对比试验 : 证明begin操作会自动更改提交方式，不会受MySQL是否自动提交影响

-- 终端1 查看目前表内数据
mysql> select *from account;
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

-- 查看提交方式
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 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.00 sec)

-- 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 插入数据
mysql> insert into account values (2, '李四', 10000);
Query OK, 1 row affected (0.00 sec)

-- 查看表内全部数据
mysql> select *from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- 终端2 查看数据
mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- 终端1 再次崩溃退出
mysql> Aborted

-- 终端2 查看数据 : 终端1 崩溃后数据自动回滚 
mysql> select * from account;
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

8. 非正常演示4 - 证明单条 SQL 与 事务的关系 (注意这里没有开启事务)

-----------------------------实验1-----------------------------------------
-- 终端1 查看目前的数据
mysql> select * from account;
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

-- 查看目前的提交状态
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.00 sec)

-- 插入一条数据
mysql> insert into account values (2, '李四', 10000);
Query OK, 1 row affected (0.00 sec)

-- 查询数据
mysql> select *from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- 终端2 查询
mysql> select *from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)
 
-- 终端1 退出 CTRL + D

mysql> ^DBye

-- 终端2 查询数据
mysql> select *from account;
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

------------------------------实验2----------------------------------------------

-- 终端1 自动开启自动提交
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

-- 查询初始数据
mysql> select * from account;
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

-- 插入数据
mysql> insert into account values (2, '李四', 10000);
Query OK, 1 row affected (0.01 sec)


-- 已经插入
mysql> select *from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)


-- 终端2 查询数据
mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- 终端1 异常退出
mysql> ^DBye

-- 终端2 还是能查到两条数据: 终端1 退出后数据持久化
mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

2.4 结论

• 一旦执行 BEGIN 或 START TRANSACTION，就必须通过 COMMIT 来持久化数据，此时autocommit 设置被忽略。

• 如果客户端崩溃，MySQL会自动回滚未提交的事务（原子性与持久性）。

• 没有设置保存点，也可以直接使用  ROLLBACK 回滚整个事务。

• 如果事务已经 COMMIT，则无法再 ROLLBACK。

三、事务隔离级别

隔离性是事务最核心的特性之一。当多个事务并发操作同一张表甚至同一行数据时，就可能出现各种并发问题。

3.1 并发读的三大问题

• 脏读（Dirty Read）：一个事务读到了另一个未提交事务修改过的数据。

• 不可重复读（Non-repeatable Read）：在同一个事务内，多次读取同一数据，结果不同。重点在于修改和删除操作。

• 幻读（Phantom Read）：在同一个事务内，多次执行同条件查询，结果集的行数不同，仿佛出现了幻觉。重点在于新增操作。

3.2 四种隔离级别

隔离级别	脏读	不可重复读	幻读	实现特点
读未提交 (Read Uncommitted)	✓	✓	✓	几乎不加锁，并发最高，但问题最多，生产绝对不用。
读已提交 (Read Committed)	×	✓	✓	大多数据库默认级别（非MySQL），存在不可重复读问题。
可重复读 (Repeatable Read)	×	×	×(注1)	MySQL InnoDB默认级别。通过MVCC和锁机制解决了幻读。
串行化 (Serializable)	×	×	×	强制事务排序，锁竞争激烈，效率极低，基本不用。

注1：在标准的SQL规范中，可重复读隔离级别无法完全解决幻读，但InnoDB引擎通过Next-Key Lock（间隙锁+行锁） 机制，基本解决了幻读问题。

3.3 查看与设置隔离级别

-- 查看全局隔离级别
SELECT @@global.transaction_isolation;

-- 查看当前会话隔离级别
SELECT @@session.transaction_isolation;
-- 或
SELECT @@transaction_isolation;

-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置全局隔离级别（对后续新会话生效）
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

可选级别：READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE。

-- 终端1 设置全局隔离级别
mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

-- 退出客户端重新进入
mysql> quit
Bye
root@VM-8-17-ubuntu:~# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 8.0.43-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

-- 查看当前隔离级别
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

mysql> use transaction_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

-- 查看目前表中数据
mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 更新数据
mysql> update account set blance = 123.0 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

---- 注意: 未commit

-- 终端2 
mysql> use transaction_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

-- 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


-- 查询数据 : 终端2查到了终端1没有commit的数据

--一个事务在执行中，读到另一个执行中事务的更新(或其他操作)但是未commit的数据，这种现象叫做脏读
(dirty read)


mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   123.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

3.3.2 读已提交【Read Committed】测试

-- 终端1 设置全局隔离级别
mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

-- 退出重启客户端
mysql> quit
Bye
root@VM-8-17-ubuntu:~# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 8.0.43-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use transaction_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

-- 查看当前数据
mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   123.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 更新数据
mysql> update account set blance = 321.0 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 终端1 目前的数据
mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   321.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- 开启终端2
root@VM-8-17-ubuntu:~# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 8.0.43-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use transaction_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

-- 终端2开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 终端2查看数据 -- 老的数据 
mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   123.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- 终端1 commit !
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

-- 终端2 查看数据 新的值
mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   321.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- but，此时还在当前事务中，并未commit，那么就造成了，同一个事务内，同样的读取，在不同的时间段
(依旧还在事务操作中！) 读取到了不同的值，这种现象叫做不可重复读(non reapeatable read)！！

3.3.3 可重复读【Repeatable Read】测试

-- 终端1 设置隔离级别为可重复读
mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

-- 退出重启
mysql> quit
Bye
root@VM-8-17-ubuntu:~# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 44
Server version: 8.0.43-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use transaction_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

-- 查看目前隔离级别
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

-- 查看当前数据
mysql> select *from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   321.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- 终端1 开启事务
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

-- 打开终端2 同时开启事务
root@VM-8-17-ubuntu:~# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 45
Server version: 8.0.43-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use transaction_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   321.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- 终端1 修改数据
mysql> update account set blance=4321.0 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 终端2 查看 数据未改变
mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   321.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- 终端1 commit
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

-- 终端2 再次查看 数据还是没有发生改变
mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   321.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- 终端2 commit
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

-- 终端2 再次查看数据 发现数据已经更新!
mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |  4321.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- 可以看到，在终端2中，事务无论什么时候进行查找，看到的结果都是一致的，这叫做可重复读！

-- 如果将上面的终端A中的update操作，改成insert操作，会有什么问题？
-- 终端1 查看数据
mysql> select *from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |  4321.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- 终端1 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 终端2 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 终端1 插入新数据并查看
mysql> insert into account (id,name,blance) values(3, '王五', 5432.0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |  4321.00 |
|  2 | 李四   | 10000.00 |
|  3 | 王五   |  5432.00 |
+----+--------+----------+
3 rows in set (0.00 sec)

-- 终端2 查看数据
mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |  4321.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- 终端2 commit 之后查看数据
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |  4321.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- 终端2 再次开启事务 然后查看
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |  4321.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- 终端1 commit
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

-- 终端2 查询 还是旧数据
mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |  4321.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

多次查看，发现终端1在对应事务中insert的数据，
在终端2的事务周期中，也没有什么影响，也符合可重复的特点。
但是一般的数据库在可重复读情况的时候，无法屏蔽其他事务insert的数据
(为什么？因为隔离性实现是对数据加锁完成的，而insert待插入的数据因为并不存在，
那么一般加锁无法屏蔽这类问题),会造成虽然大部分内容是可重复读的，
但是insert的数据在可重复读情况被读取出来，
导致多次查找时，会多查找出来新的记录，就如同产生了幻觉。
这种现象，叫做幻读(phantom read)。

很明显，MySQL在RR级别的时候，是解决了幻读问题的,没有查到其他事务新插入的数据!!!
它是如何打破“RR级别存在幻读”这个传统数据库理论的？答案是：Next-Key Lock（临键锁）。
Next-Key Lock = 行锁 (Record Lock) + 间隙锁 (Gap Lock)

-- 终端2 commit
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

-- 终端2 中才查到了最新的数据
mysql> select * from account;
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |  4321.00 |
|  2 | 李四   | 10000.00 |
|  3 | 王五   |  5432.00 |
+----+--------+----------+
3 rows in set (0.00 sec)

3.3.3.1 幻读 和 不可重复读的区别

事务隔离级别是并发性能与数据安全性之间的权衡艺术。

• 安全性：隔离级别越严格（如串行化），数据越安全，但并发性能越低。

• 并发性：隔离级别越宽松（如读未提交），并发性能越高，但数据问题越多。

• 最佳实践：工程中需在两者间找到平衡点。MySQL默认的可重复读（REPEATABLE READ） 是一个兼顾性能与安全的成熟选择，一般不建议修改。

最后，牢记两种易混淆的并发读现象核心区别：

• 不可重复读：重点是 UPDATE/DELETE。同一行数据，前后读到的内容（值）不一样。

• 幻读：重点是 INSERT。同一条件查询，前后读到的行数（记录数）不一样。

假设测试表结构是：

mysql> create table if not exists student(
name varchar(11) not null,
age int not null
);
mysql> insert into student (name, age) values ('张三', 28);
Query OK, 1 row affected (0.05 sec)
mysql> select * from student;
+--------+-----+
| name | age |
+--------+-----+
| 张三 | 28 |
+--------+-----+
1 row in set (0.00 sec)

4.2.2 一些思考

4.2.3 总结