Query in transaction may return rows with same primary key column value

[crazycs520]

Bug Report

Please answer these questions before submitting your issue. Thanks!

This is a sub-issue of #24195 (comment)

1. Minimal reproduce step (Required)

/* t1 */ drop table if exists t;
/* t1 */ create table t (c1 varchar(10), c2 int, primary key (c1) nonclustered);
/* t1 */ insert into t values ('a', 1);
/* t1 */ begin;
/* t1 */ update t set c1='b' where c1='a';
/* t2 */ begin;
/* t2 */ select * from t;
/* t2 */ insert into t values ('a', 2); -- Blocked until t1 commited.
/* t1 */ commit;
/* t2 */ select * from t use index(primary) order by c1, c2; -- [('a', 1), ('a', 2)]

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

MySQL 8.0.29 returns:

/* t2 */ select * from t use index(primary) order by c1, c2;
+----+----+
| c1 | c2 |
+----+----+
| a  | 2  |
+----+----+

3. What did you see instead (Required)

Currently TiDB returns:

/* t2 */ select * from t use index(primary) order by c1, c2;
+----+----+
| c1 | c2 |
+----+----+
| a  | 1  |
| a  | 2  |
+----+----+

Since column c1 is a primary key, the query result breaks the primary key unique constraint.

4. What is your TiDB version? (Required)

***************************[ 1. row ]***************************
tidb_version() | Release Version: v7.2.0-alpha-277-geca4e9be28
Edition: Community
Git Commit Hash: eca4e9be284e92aca3d5fbb16e44c6a7655fdf82
Git Branch: master
UTC Build Time: 2023-05-26 02:22:59
GoVersion: go1.20.2
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore

[crazycs520]

For unique index, currently, both TiDB and MySQL8.0.29 query in transaction may return rows with same unique index column value. Here is a case:

/* t1 */ drop table if exists t;
/* t1 */ create table t (c1 varchar(10), c2 int, unique key idx(c1));
/* t1 */ insert into t values ('a', 1);
/* t1 */ begin;
/* t1 */ update t set c1='b' where c1='a';
/* t2 */ begin;
/* t2 */ select * from t;
/* t2 */ insert into t values ('a', 2);
/* t1 */ commit;
/* t2 */ select * from t;

For the last query, both TiDB and MySQL8.0.29 return:

+----+----+
| c1 | c2 |
+----+----+
| a  |  1 |
| a  |  2 |
+----+----+

Since column c1 is a unique index, the query result breaks the unique index constraint.

Look like MySQL only check the primary key constrain when query in transaction, without check unique index constraint. I have reported this bug to MySQL. https://bugs.mysql.com/bug.php?id=111215