MySQL Bugs: #114389: A non-existent row has appeared which happens in Repeatable Read Level

Bug #114389	A non-existent row has appeared which happens in Repeatable Read Level
Submitted:	18 Mar 2024 11:48	Modified:	19 Mar 2024 12:54
Reporter:	Hongyu Yang	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0, 8.2	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Isolation Level: REPEATABLE READ
During the concurrent execution of three transactions, one of the transactions updated a data item, but read a row that does not exist.

How to repeat:
/* init */ CREATE TABLE t(pkId integer, a INT PRIMARY KEY, b INT, c INT);
/* init */ ALTER TABLE t ADD INDEX t_index(a);
/* init */ INSERT INTO t (pkId,a,b,c) values (8,44,1,2);
/* t1 */ SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
/* t2 */ SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
/* t3 */ SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

/* t1 */ BEGIN;
/* t2 */ BEGIN;
/* t2 */ UPDATE t SET b = 222, c = 333;
/* t2 */ COMMIT;
/* t3 */ BEGIN;
/* t3 */ SELECT pkId, b, c FROM t;
/* t1 */ UPDATE t SET a = 40 WHERE a = 44;
/* t1 */ COMMIT;
/* t3 */ UPDATE t SET b = 888, c = 999;
/* t3 */ SELECT pkId, b, c FROM t where b = 854 or c = 333 order by b;	-- [(8, 222, 333)] 
/* t3 */ COMMIT;

It appears that a non-existent row has appeared.

I'm not sure whether this is a new bug or a duplicate one. From the user's perspective in the third transaction, I should have read that the result is empty, but one was found.

HI Mr. Yang,

Thank you for your bug report.

This is truly a serious bug. However, it has been reported in dozen of bug reports. The original bug is the following one:

https://bugs.mysql.com/bug.php?id=100328

It has many, many duplicates, which you can find in that bug report.

Since this bug is very hard to fix, it has been properly documented:

 "15.7.2.3 Consistent Nonlocking Reads”

              https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

Duplicate.