MySQL Forums
Forum List  »  Docs

MySQL5.7、8.0 InnoDB在 REPEATABLE-READ隔离级别下幻读问题
Posted by: lk lk
Date: December 29, 2023 01:52AM

前提条件:
mysql5.7 Or 8.0 版本都存在此问题。存储引擎是:InnoDb,事务隔离级别是:REPEATABLE-READ

表结构、数据:
CREATE TABLE `test` (
`a` int(4) NOT NULL,
`b` int(10) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `test_b_index` (`b`)
);
insert into test values (1, 1), (2, 3), (3, 6), (4, 8);


事务A: start transaction; select * from test order by a; # 先读取整个表的数据(生成MVCC快照),只能读取到 a=1、2、3、4的数据
事务B: start transaction; insert into test values (5, 7); commit;
事务A: select * from test order by a; # 由于快照已生成,所以查询不到新插入的a=5的数据
事务A: update test set b = 6 where b > 6; # 由于快照已生成,所以查询不到新插入的a=5的数据
事务A: select * from test order by a; rollback; # 能够正常读取到数据 a=5

猜测原因:事务A的更新语句,更改了数据 a=5 的隐藏字段 DB_TRX_ID,上一次修改改数据的事务id,改成了事务A自己的事务id,所以事务A能够查询到新增数据了。
如果是以上原因,我存在疑问的是:事务A的事务id应该比事务B的事务id小,所以不应该让事务A更新到事务B新增的数据

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL5.7、8.0 InnoDB在 REPEATABLE-READ隔离级别下幻读问题
696
December 29, 2023 01:52AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.