Unrepeatable read occurs when two transaction are executed

[JZuming]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Setup the environment:

tidb/bin/tidb-server &
mysql -h "127.0.0.1" -u root -P 4000 -D testdb < mysql_bk.sql

mysql_bk.sql:
mysql_bk.sql.txt

Testcase

connect command: mysql -h "127.0.0.1" -u root -P 4000 -D testdb

Two transaction T1 and T2

T1> start transaction;
T2> start transaction;
T2> select ref_14.c_qpjzzd as c1 from t_q_zw9c as ref_14 order by c1 desc;
T1> delete from t_q_zw9c;
T1> commit;
T2> select ref_14.c_qpjzzd as c1 from t_q_zw9c as ref_14 order by c1 desc;
T2> commit; 

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

Two select statement in transaction T2 output the same result.

3. What did you see instead (Required)

The first select statement in transaction T2 outputs:

+----+
| c1 |
+----+
|  0 |
+----+
1 row in set (0.00 sec)

The second select statement in transaction T2 outputs:

Empty set (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v5.4.0-alpha-133-g20b9a4d8c
Edition: Community
Git Commit Hash: 20b9a4d8ca32449c5003963eb7d382633c3ed25e
Git Branch: master
UTC Build Time: 2021-11-17 08:53:20
GoVersion: go1.16
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[JZuming]

After deleting the "order by" clause of the two select statements, they output the same result.
i.e.

T1> start transaction;
T2> start transaction;
T2> select ref_14.c_qpjzzd as c1 from t_q_zw9c as ref_14;
T1> delete from t_q_zw9c;
T1> commit;
T2> select ref_14.c_qpjzzd as c1 from t_q_zw9c as ref_14;
T2> commit;

The two select statements in transaction T2 output the same result:

+----+
| c1 |
+----+
|  0 |
+----+
1 row in set (0.00 sec)

So the bug may hide in the code related to "order by" clause.

[zyguan]

Thanks for your reporting @JZuming . The issue only occurs when using unistore, you can use tikv to avoid it.

@cfzjywxk PTAL.