InnoDB解决幻读了吗?_innodb 幻读-优快云博客

CREATE TABLE `user_info` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`age` int NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `user_info` VALUES (1, '测试1', 10);
INSERT INTO `user_info` VALUES (2, '测试2', 15);
INSERT INTO `user_info` VALUES (3, '测试3', 18);
INSERT INTO `user_info` VALUES (4, '测试4', 24);

数据如下：

当前读

场景

开启事务A
在事务A中通过select for update进行当前读查询（age > 15）
开启事务B
在事务B中插入一条新数据
提交事务B
在事务A中再次通过select for update进行当前读查询（age > 15）
提交事务A

验证结果

SELECT OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA FROM `performance_schema`.data_locks;

结论

由场景验证结果可以看到，由于Next-Key Lock的存在，会阻塞其他事务对加锁间隙的数据插入，所以当前读场景下通过LBCC是可以完全解决幻读的问题。

快照读

场景1

开启事务A
在事务A中通过select进行快照读查询（age > 15）
开启事务B
在事务B中插入一条新数据
提交事务B
在事务A中再次通过select进行快照读查询（age > 15）
提交事务A

验证结果

由场景1验证结果来看，RR隔离级别是解决了幻读问题，但是并没有完全解决，接着看场景2。

场景2

开启事务A
在事务A中通过select进行快照读查询（age > 15）
开启事务B
在事务B中插入一条新数据
提交事务B
在事务A中将age=18的所有数据的name改成测试99
在事务A中再次通过select进行快照读查询（age > 15）
提交事务A

验证结果

由场景2来看，在第二次快照读查询的时候只是age=18的数据产生了变化，并没有查询到事务B中插入的数据，由此可见该场景依然解决了幻读问题（事务A中的UPDATE没修改到事务B中新插入的数据）。

场景3（产生幻读）

开启事务A
在事务A中通过select进行快照读查询（age > 15）
开启事务B
在事务B中插入一条新数据
提交事务B
在事务A中将age=16的所有数据的name改成测试99
在事务A中再次通过select进行快照读查询（age > 15）
提交事务A

验证结果

由场景3中可以看到，在第二次快照读查询中，查询到了事务B所新插入的数据，产生幻读（事务A中的UPDATE修改到事务B中新插入的数据）。

结论

从以上3个场景可以看出，在某些特殊场景下会产生幻读，因此MVCC并没有完全解决幻读的问题。

下面我们就来一探究竟产生幻读的原因。

幻读分析

从验证结果来看，当前读是可以完全避免幻读问题，而对于快照读如果在两次读取之间进行了当前读，在某些情况是会触发幻读的问题（场景3），那么我们基于场景3结合MVCC一步步进行分析幻读产生的原因。

步骤1(初始化数据)

将测试数据的插入语句放在同一个事务中，让数据的DB_TRX_ID保持一致（方便后续分析）。

BEGIN;
INSERT INTO `user_info` VALUES (1, '测试1', 10);
INSERT INTO `user_info` VALUES (2, '测试2', 15);
INSERT INTO `user_info` VALUES (3, '测试3', 18);
INSERT INTO `user_info` VALUES (4, '测试4', 24);
COMMIT;

在事务COMMIT之前查询下当前事务id

测试数据DB_TRX_ID都为802963，数据结构如下：

步骤2（开启事务A，进行第一次快照读）

开启事务A

BEGIN;

进行第一次快照读

#不会分配事务id，文章末尾进行讲解
select * from user_info where age > 15;

此时会生成一张ReadView读视图，m_ids，m_up_limit_id，m_low_limit_id，m_creator_trx_id的值分别为：

m_ids：无

m_up_limit_id：无

m_low_limit_id：生成 ReadView 时系统将要分配给下一个事务的 id 值，这里具体也不知道分配的id值是多少，但是值肯定是大于802963的（这里假设值为802964）。

m_creator_trx_id：无

根据ReadView可见性判断规则可以得出此时查询出的数据为id=3和id=4的数据。

注：可见性判断规则

被访问版本的 trx_id == m_creator_trx_id，说明当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。

被访问版本的 trx_id < m_up_limit_id，说明被访问版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问。

被访问版本的 trx_id ≥ m_low_limit_id，说明被访问版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。

访问版本的 trx_id是否在 m_ids 列表中。如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。

步骤3（开始事务B，插入一条新数据，提交事务）

BEGIN;
INSERT INTO user_info(name, age) VALUES('测试5', 16);
COMMIT;

在事务COMMIT之前，我们先查询下当前的事务id

那么在事务B COMMIT之后，当前表里的数据应当如下：

步骤4（事务A执行更新语句）

#这里才真正分配了事务id
UPDATE user_info SET name = '测试99' WHERE age = 16;

更新时，我们查询下当前的事务id（这里事务A的事务id在执行此句更新语句后才真正分配，并且由于事务B先分配了事务id，因此这里trx_id比事务B的trx_id大是正确的）。

执行完更新后，此时表里的数据发生了变化，具体数据如下图：

步骤5（事务A进行第二次快照读，提交事务A）

SELECT * FROM user_info WHERE age > 15;
COMMIT;

此时，在进行第二次快照读的时候发生了幻读。由于我设置的事务隔离级别为RR，那么在进行第二次快照读的时候会复用第一次的ReadView，那么id=5的数据undolog日志如下

根据ReadView可见性规则判断

被访问版本的 trx_id（804618） == m_creator_trx_id（804618），说明当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。

因此该条数据会展现出来，产生幻读。

Q&A

Q：快照读的场景2和场景3都执行了当前读的更新操作，那么为什么场景2没有产生幻读？

A：因为场景2的当前读更新操作更新的是age=18的这条数据，age=16的这条数据DB_TRX_ID还是为803552，那么根据ReadView可见性规则判断

被访问版本的 trx_id（803552） ≥ m_low_limit_id（802964），说明被访问版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问

Q：为什么事务A在进行第一次快照读的时候没有生成事务id？

A：在执行第一次快照读的时候，会以读事务的身份启动新事务（读事务不会分配事务id）

可以看出，是个非常大的数字（大于等于 2*48，说明该事务没有分配 ID）。

读写事务在创建时并不会立马分配事务id，而是等到事务执行第一条更改语句（INSERT、UPDATE、DELETE）时才分配事务id。

Q：假设当前表的测试数据如下，如果在事务A执行了select * from user_info where age = 16 FOR UPDATE，那么如果我在事务B插入age为15和age为18的数据能插入吗？

A：事务A执行当前读后，首先看下当前的Next-Key Lock锁住的数据范围

由上图可以看出Next-Key Lock退化成了间隙锁，锁住的范围为(15,18)，那么理论上插入age=15和age=18是能插入的，接下来我们做下测试。

age=15

age=18

可以看到age=15的数据插入失败了，而age=18却插入成功了，这是为什么呢？

这个涉及到了B+树的原理。由于age为非聚簇索引，如果出现索引值相同的情况下，会按聚簇索引值进行有序排列。由于我们主键设置为自增，因此插入的age=15在锁住的范围内，而age=18不在范围内。