MySQL如何解决幻读?

目录

一、什么是幻读?

1.1 幻读的定义

1.2 幻读的示例

1.3 幻读产生的原因?

1.4?读已提交(Read Committed)

1.4.1 确定事务等级

1.4.2 非锁定读取

准备

示例

结论

1.4.3 锁定读取

准备

示例

分析

结论

1.5?可重复读(Repeatable Read)

1.5.1 确定事务等级

1.5.2 非锁定读取

准备

示例

结论

1.5.3 锁定读取

准备

示例

分析

结论

二、怎么解决幻读?

2.1 提高事务隔离等级

2.2 间隙锁(Gap Locking)

三、间隙锁

3.1 定义

3.2 工作原理

无间隙锁

有间隙锁

3.3?间隙锁的类型

范围查询

相等查询

参考资料


一、什么是幻读?

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;

  1. Id=4所在的行
  2. Id=4与 id=6之间的间隙
  3. Id=6所在的行
  4. Id=6之后的间隙

这就意味着,在间隙插入记录会被阻止,直到事务A结束。

共享锁与示例的排它锁相同,都是通过阻止其他会话变更,来避免对当前会话的影响。

UPDATE语句、DELETE语句与锁定读取(Locking Reads)同理,在执行时,会根据使用的查询条件进行加锁:

  1. 相等(=、in):InnoDB只锁定找到的索引记录
  2. 范围(BETWEEN、>、<):InnoDB锁定扫描的索引范围

要注意的是,对于使用唯一索引的语句,完全没必要进行加锁。

注:

我们知道,默认情况下,MySQL是在autocommit开启的状态下运行。开启事务(START TRANSACTION)后,自动提交模式(autocommit)会隐式禁用,所有的纯SELECT语句会被转化成SELECT…LOCK IN SHARE MODE, 但是如果想要锁住间隙或是锁住索引记录的话,需要显示触发锁定行为。

结论

在可重复读(Repeatable Read)隔离等级下,针对锁定读取(共享锁or排它锁)、UPDATE语句和DELETE语句,不会出现“幻读”。

二、怎么解决幻读?

2.1 提高事务隔离等级

使用可串行化(Serializable),提事务隔离等级来避免。在可串行化隔离等级下,数据库会对查询和写进行加锁,确保事务的完全顺序执行,但是并发效率低下。

2.2 间隙锁(G****ap Locking

间隙锁是加在索引记录之间间隙的锁,又或者是在索引区间第一条记录之前、或最后一条记录之后。

示例:

select * from users where age between 10 and 30FOR 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

### MySQL解决问题的方法及实现机制 #### 的定义与产生原因 是指在一个事务中多次查询同一条件下的数据集合时,由于其他事务的操作导致结果集不一致的现象。这种现象通常发生在插入新记录的情况下[^4]。 #### 解决的主要方法 ##### 方法一:提高隔离级别至串行化 当事务隔离级别被设置为 **Serializable** 时,MySQL 使用表级锁来防止任何并发写入操作,从而彻底杜绝的可能性。然而,这种方式会显著降低系统的并发性能[^2]。 ##### 方法二:利用 MVCC 和 Next-Key Lock 实现解决方案 在 InnoDB 存储引擎中,默认的事务隔离级别是 **Repeatable Read (RR)**。虽然 RR 能够通过多版本并发控制(MVCC)解决和不可重复的问题,但它无法完全避免。为了进一步处理这一问题,InnoDB 引入了 **Next-Key Lock** 技术[^3]。 - **MVCC 工作原理** MVCC 是一种通过保存数据的历史版本来支持高并发的技术。对于 SELECT 查询而言,InnoDB 只会返回那些符合当前事务可见性的历史版本数据,而不会看到未提交或者由其他事务更新后的数据版本[^1]。 - **Next-Key Lock 的作用** Next-Key Lock 是 Record Lock 和 Gap Lock 的组合形式,在执行某些特定类型的查询(如 `SELECT ... FOR UPDATE` 或者索引扫描中的 DML 操作)时会被自动应用。它不仅锁定具体的行记录本身,还会封锁这些行之间以及之前可能存在的间隙区域,阻止其他事务在此范围内插入新的记录,以此达到预防的效果。 - **当前的概念** 当前指的是取到的是最新的实际物理存储上的数据副本,并且会对所访问的数据施加相应的排他锁(X 锁)。这意味着如果某个事务进行了当前,则在同一时刻其他试图修改相同资源的事务将会受到阻碍直到前者完成其工作为止。 #### 总结 综上所述,MySQL 主要依靠提升隔离等级至 Serializable 来绝对消除风险;而在更常见的 Repeatable Read 下则是借助于 MVCC 结合 Next-Key Locks 方案共同协作达成目标——即一方面维持旧有视图供一致性快照阅之需,另一方面则通过对涉及区间实施严格管控以防范新增干扰项出现引发逻辑错误情形发生。 ```sql -- 示例代码展示如何使用 SELECT...FOR UPDATE 进行当前 START TRANSACTION; SELECT * FROM table_name WHERE id = ? FOR UPDATE; -- 加锁并获取最新版本数据 COMMIT; ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值