Phantom rows caused by update statements which changes value of the primary key

[Reverie4u]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

/* 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;
/* 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;

2. What did you expect to see? (Required)

The second consistent read of t2 should return [(1, 3), (3, 3)].

3. What did you see instead (Required)

Actual result is [(1, 3), (2, 2), (3, 3)] , though the final state of table t is [(1, 3), (3, 3)] after COMMIT of t2.

4. What is your TiDB version? (Required)

mysql> SELECT tidb_version();
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.1.2
Edition: Community
Git Commit Hash: aa6ed99ae63191bc98e883fd4c369ae7482cccb7
Git Branch: heads/refs/tags/v7.1.2
UTC Build Time: 2023-10-21 07:46:04
GoVersion: go1.20.10
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[Reverie4u]

This bug can also be reproduced in MySQL 8.2.0 and has been verified by MySQL Verification Team. Please see: https://bugs.mysql.com/bug.php?id=113228

[zyguan]

I think this behaviour has been documented here. In practice, you should always use select for update in a write transaction. The issue is also related to #24195.

[jebter]

The issue has not been updated for too long, so I will close it，if there are any updates, you can reopen it.