mysql幻读有哪些场景 mysql幻读如何解决

转载

coolfengsy 2023-08-17 16:15:02

文章标签 mysql幻读有哪些场景数据库 python markdown 版本号 文章分类 MySQL 数据库

2019独角兽企业重金招聘Python工程师标准>>>

mysql幻读有哪些场景 mysql幻读如何解决_数据库

mysql REPEATABLE READ对幻读的解决

环境说明： mysql 5.7 + innodb引擎

幻读定义

幻读指在同一个事务中，两次相同的查询结果集不同，当然要排除select->delete->select的情况。REPEATABLE READ解决幻读的方式

REPEATABLE READ 隔离级别通过MVCC和next-key lock实现解决幻读

MVCC 多版本并发控制

概念

MVCC允许数据具有多个版本，版本可以是时间戳或者是全局递增的事务ID。在MVCC协议下，每个读操作会看到一个一致的数据快照，这意味着，在同一个时间点，不同的事务看到的数据可能是不同的。

在 REPEATABLE READ下的实现

innodb会为每一行记录添加两个隐藏的列，分别表示该行记录创建的版本（数据何时被创建）和删除的版本（数据何时被删除），填入的是事务的版本号，这个版本号随着事务的创建不断递增。

在REPEATABLE READ的隔离级别下，具体各种数据库操作的实现如下：

select select返回的记录必须符合下面两个条件：

该行的创建版本号小于等于当前版本号，用于保证在事务创建时或事务开始之前这行数据是存在的。
该行的删除版本号大于当前版本或者为空。用于保证在事务开始之前这行数据没有被删除。

insert 将新插入的行的创建版本号设置为当前系统的版本号，删除版本号为空
delete 将该行的删除版本号设置为当前系统的版本号，并不把数据实际删除
update 转换成insert + delete。新拷贝这行数据，将新行insert同时设置创建版本号为当前版本号，将旧行的删除版本号设置为当前版本号

这种额外的记录优点是对于大多数查询来说根本就不需要获得一个锁，以最快的速度来读取数据，确保只选择符合条件的行。缺点在于存储引擎必须为每一行存储更多的数据，做更多的检查工作，处理更多的善后操作。 innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除，这个过程叫做purge。

next-key lock

概念

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

即行记录锁+记录前的gap锁。

为了防止幻读，next-key lock阻止特定条件的新记录的插入，因为插入时要获取Insert Intention Locks，与已持有的next-key lock冲突。Insert Intention Locks，插入意向锁并非意向锁，而是一种特殊的间隙锁。（下面会举例说明）

何时获取next-key lock

官方文档为：

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition.
For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.
For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks, see Section 15.5.1, “InnoDB Locking”.

也就是locking reads，UPDATE和DELETE时，除了对唯一索引的条件外都会获取gap锁或next-key锁。当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围

加锁范围

示例表： create table t(a int,key idx_a(a))engine =innodb;

insert into t values(1),(3),(5),(8),(11);

T1	T2
begin；
select * from t where a = 8 for update;
	begin；
	insert into t values(2); // Query OK, 1 row affected (0.00 sec)
	insert into t values(4); //Query OK, 1 row affected (0.00 sec)
	insert into t values(5); // 阻塞
	insert into t values(6); // 阻塞
	insert into t values(9); // 阻塞
	insert into t values(11); //Query OK, 1 row affected (0.00 sec)

Markdown未解析出来，贴图一张

mysql幻读有哪些场景 mysql幻读如何解决_数据库_02

根据上一小节中何时获取next-key lock可知，上述情况T1获取了next-key lock。那其范围是什么呢，为何insert 9也会阻塞？对于上表，其gap区间有：(-∞,1)，(1,3)，(3,5)，(5,8)，(8,11)，(11,+∞）根据概念，事务T1加锁区间为(5, 8], 但是insert 9也会阻塞，原因是：除了行记录锁+记录前的gap锁外，还会对非唯一索引的下一个键值加上gap lock，即T1锁住的范围是(5, 11)。能insert 11是因为隐藏的自增主键，假设表中记录11对应的主键为5，再 insert into t values(11)对应主键为6，不在T1锁住的范围内。

所以，如果不对非唯一索引的下一个键值也加锁，仍然会出现幻读。因为T2可以insert 一条a=8的数据，T1再次select * from t where a = 8 for update;幻读就出现了。