Bug #114389 A non-existent row has appeared which happens in Repeatable Read Level
Submitted: 18 Mar 11:48 Modified: 19 Mar 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

[18 Mar 11:48] Hongyu Yang
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.
[19 Mar 12:54] MySQL Verification Team
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.