MySQL如何解决幻读？
目录
一、什么是幻读？
1.1 幻读的定义
幻读（Phantom Read） 是数据库事务中的一种并发问题，指的是在一个事务执行过程中，另一个事务插入、删除或修改了符合查询条件的记录，导致前一个事务读取到不同的数据集，从而产生“幻觉”般的读操作。
1.2 幻读的示例
具体来说，事务A执行过程中，事务B插入或者修改了记录，导致事务A在相同查询条件下读取到的结果发生了改变。
假设有用户表users，包含列username、password、state。
1.事务A，查询所有状态启用的用户
select count(id) from users where state=1;2.事务B，插入新用户，state=1
insert into users(username, password, state) values('test11', 123456, 1);3.事务A，再次执行相同的sql
select count(id) from users where state=1;事务A两次查询，得到了不同的结果。
1.3 幻读产生的原因？
1.对表进行了插入或删除
2.事务隔离等级
幻读（Phantom Read）通常在较低的事务隔离等级中产生（如 读已提交（READ COMMITTED）、可重复读（REPEATABLE READ））。
读已提交（Read Committed）：可以读取到已提交的记录，但无法保证查询过程中数据不被改变，因此会发生幻读；
可重复读（Repeatable Read）：可以保证事务中多次查询的结果一致，但仍然可能出现幻读，因为在非锁定读取时，允许新记录的插入（即事务 A 查询的范围可能会变化）。
两种隔离等级下，产生幻读的情况看下文具体示例。
1.4 读已提交（Read Committed）
1.4.1 确定事务等级
查看当前事务隔离等级
SELECT @@transaction_isolation;
将事务隔离等级设为【读已提交】
#设置事务隔离等级
SET SESSION TRANSACTION ISOLATION LEVEL {level};
#{level}可选项:：
#读未提交: READ UNCOMMITTED·
#读已提交: READ COMMITTED
#可重复读: REPEATABLE READ
#可串行化: SERIALIZABLE
1.4.2 非锁定读取
准备
准备一组数据，进行以下测试：事务A执行查询，事务B插入一条记录，事务A再次执行查询。
示例
1.事务A执行查询
select * from tmp where id>=2;2.事务B插入数据，并提交
insert into `tmp`(`id`,`value`) values (4, 'dd');
3.事务A再次执行查询
结论
事务A两次查询结果不一致，可以读取到事务B已提交的记录，出现【幻读】
1.4.3 锁定读取
准备
准备一组数据，进行以下测试：事务A执行查询，事务B插入一条记录，事务A再次执行查询。
示例
1.事务A进行锁定读取
select * from tmp where id >= 4 for update;2.事务B，在间隙插入数据，并提交
insert into `tmp`(`id`,`value`) values (5, 'dd');
3.事务A再次执行查询，两次读取结果不一致，发现间隙并没有锁住
并且事务B插入的行已经落库。
分析
然后我们看MySQL文档解释
意思就是，在【读已提交】隔离等级下，针对锁定读取（共享锁or排它锁）、UPDATE语句和DELETE语句，innodb只锁定索引到的记录，锁定记录的前后都能插入新记录。也就说“间隙”并没有被锁住。
再看【间隙锁】这边的解释，间隙锁在读已提交隔离等级下，在查询和索引扫描被禁用了。
结论
虽然使用了锁定读取，但读已提交（Read Committed）下，幻读依然存在。
1.5 可重复读（Repeatable Read）
1.5.1 确定事务等级
查看当前事务隔离等级
SELECT @@transaction_isolation;
1.5.2 非锁定读取
准备
准备一组数据，进行以下测试：事务A执行查询，事务B插入一条记录，事务A再次执行查询。
示例
1.事务A执行查询
select * from tmp where id>=4;2.事务B插入一条记录, 并commit
insert into `tmp`( `id`, `value` ) values (5, 'dd');3.事务A再次执行查询
select * from tmp where id>=4;
事务B执行commit后，由于事务隔离等级缘故，事务A再次查询，结果不影响，具备可重复读特性；事务A结束后，再次查询多了一条数据（5, ‘dd’）。
结论
在可重复读（Repeatable Read）隔离等级下，事务内部具备可重复读特性； 但由于“间隙”并未锁住，非锁定读取会出现“幻读”。
1.5.3 锁定读取
准备
准备一组数据，进行以下测试：事务A执行查询，事务B插入一条记录，事务A再次执行查询。
示例
事务A执行查询
select * from tmp where id>=4 for update;
事务B插入一条记录
insert into `tmp`( `id`, `value` ) values (5, 'dd');
这时，发现插入操作被阻塞了。
分析
我们看一下执行的查询语句
select * from tmp where id>=4 for update;
- Id=4 所在的行
- Id=4 与 id=6 之间的间隙
- Id=6 所在的行
- Id=6 之后的间隙
这就意味着，在间隙插入记录会被阻止，直到事务A结束。
共享锁与示例的排它锁相同，都是通过阻止其他会话变更，来避免对当前会话的影响。
UPDATE语句、DELETE语句与锁定读取（Locking Reads）同理，在执行时，会根据使用的查询条件进行加锁：
- 相等（=、in）：InnoDB只锁定找到的索引记录
- 范围（BETWEEN、>、<）：InnoDB锁定扫描的索引范围
要注意的是，对于使用唯一索引的语句，完全没必要进行加锁。
注：
我们知道，默认情况下，MySQL是在autocommit开启的状态下运行。开启事务（START TRANSACTION）后，自动提交模式(autocommit)会隐式禁用，所有的纯SELECT语句会被转化成SELECT.....LOCK IN SHARE MODE, 但是如果想要锁住间隙或是锁住索引记录的话，需要显示触发锁定行为。
结论
在可重复读（Repeatable Read）隔离等级下，针对锁定读取（共享锁or排它锁）、UPDATE语句和DELETE语句，不会出现“幻读”。
二、怎么解决幻读？
2.1 提高事务隔离等级
使用可串行化（Serializable），提事务隔离等级来避免。在可串行化隔离等级下,数据库会对查询和写进行加锁，确保事务的完全顺序执行，但是并发效率低下。
2.2 间隙锁（Gap Locking）
间隙锁是加在索引记录之间间隙的锁，又或者是在索引区间第一条记录之前、或最后一条记录之后。
示例：
select * from users where age between 10 and 30 FOR UPDATE;
age（10-30）范围内所有现有值之间的间隙都被锁定，防止其他事务将值等于15插入列age中，无论该列中是否已有该值。
三、间隙锁
3.1 定义
间隙锁（Gap Lock）是一种数据库锁定机制，常见于支持行级锁的数据库（如 MySQL 的 InnoDB 存储引擎）中，主要用于解决并发事务中的“幻读”问题。
3.2 工作原理
假定有数据表
+----+-------+
| id | title |
+----+-------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
+----+-------+
无间隙锁
事务A查询id > 2 的记录并锁定这条记录，然后事务B插入一条id=4的新纪录，事务A再次查询时会得到不同的结果集。
有间隙锁
事务A执行查询 id > 2时，这条查询会锁定 id > 2 的记录、所有行之间的间隙。
3.3 间隙锁的类型
范围查询
当事务执行范围查询（如BETWEEN、>、<等）时，数据库会在查询范围内加上间隙锁。
例如：
select * from `tmp` where `id` > 5 for update;
可重复读（Repeatable Read）隔离等级下，可能会锁定 id >5 的所有间隙.
相等查询
当事务执行相等查询（如 = ）时，数据库会锁定对应的索引记录。如果索引记录不存在，则会锁定对应的间隙，防止其他事务插入重复的记录。
示例：
+----+-------+
| id | value |
+----+-------+
| 2 | aa |
| 4 | bb |
| 6 | cc |
+----+-------+
事务A, 执行查询, 锁住id=5的间隙
select * from tmp where id=5 for update;事务B, 尝试在id=5插入数据
insert into `tmp`( `id`, `value` ) values ( 5, 'bb' );发现事务B，在事务A未结束的情况下，一直被阻塞直至超时
参考资料
MySQL数据库事务隔离等级：The InnoDB Engine: Transaction Isolation Levels
InnoDB一致性非锁定读取：The InnoDB Engine | Consistent Nonlocking Reads
InnoDB幻影行（幻读）：The InnoDB Engine | Phantom Rows
InnoDB间隙锁：The InnoDB Engine | Gap locking
原文地址：https://blog.csdn.net/achandy/article/details/143801977
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！