MySQL Bugs: #113228: Phantom rows caused by update statements which changes value of the primary key

Bug #113228	Phantom rows caused by update statements which changes value of the primary key
Submitted:	27 Nov 2023 5:51	Modified:	27 Nov 2023 12:29
Reporter:	zhuang liu	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:
Isolation Level: REPEATABLE READ & REPEATABLE READ.
An UPDATE statement which update the value of primary key caused phantom rows in another transaction.

How to repeat:
/* init */ CREATE TABLE t(a INT PRIMARY KEY, b INT);
/* init */ INSERT INTO t VALUES (1, 1);
/* init */ INSERT INTO t VALUES (2, 2);
/* t1 */ SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
/* t2 */ SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

/* t1 */ BEGIN;
/* t1 */ SELECT * FROM t LOCK IN SHARE MODE;
/* t2 */ BEGIN;
/* t2 */ SELECT * FROM t;  -- [(1, 1), (2, 2)]
/* t1 */ UPDATE t SET a=3 WHERE b = 2;
/* t1 */ COMMIT;
/* t2 */ UPDATE t SET b=3;
/* t2 */ SELECT * FROM t; -- [(1, 3), (2, 2), (3, 3)] 
/* t2 */ COMMIT;

It appears that a phantom row (2, 2) showed up in the second consistent read of T2. I'm not sure whether this is a new bug or a duplicate one. From the user's perspective, I haven't inserted a new row. I've only updated existing rows. Phantom rows shouldn't occur.

This test case can also be repeated in MySQL 8.2.0.

Hi Mr. Liu,

Thank you very much for your bug report.

We have managed to repeat it with latest 8.0 and 8.2.

However, if you commit changes in the second connection, the phantom rows will disappear !!!!!

This looks like a duplicate of the bug  https://bugs.mysql.com/bug.php?id=100328, which was documented here in the Note here:

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

However, your report with shadow bugs is something new.

Verified as reported for latest 8.0 and 8.2 releases.