Transaction can't read its own update in repeatable read.

[wengsy150943]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

The original schema is shown below:

create table table1 (pkId integer, pkAttr0 integer, pkAttr1 integer, pkAttr2 integer, coAttr0_0 integer, primary key(pkAttr0, pkAttr1, pkAttr2) NONCLUSTERED);
insert into table1 values (1,1,1,1,1);

The following transactions are executed in order in two sessions of repeatable read.

session1 > set session transaction isolation level repeatable read;

session2 > set session transaction isolation level repeatable read;

session2 > start transaction;

session1 > start transaction;

session2 > update table1 set coAttr0_0 = 2 where pkAttr0=1 and pkAttr1=1 and pkAttr2=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
session2 > commit;

session1 > update table1 set coAttr0_0 = 2 where pkAttr0=1 and pkAttr1=1 and pkAttr2=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

session1 > select * from table1 where pkAttr0=1 and pkAttr1=1 and pkAttr2=1;

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

The last query returns:

+------+---------+---------+---------+-----------+
| pkId | pkAttr0 | pkAttr1 | pkAttr2 | coAttr0_0 |
+------+---------+---------+---------+-----------+
|    1 |       1 |       1 |       1 |         2 |
+------+---------+---------+---------+-----------+

while coAttr0_0 should be updated by its own update operation, which set coAttr0_0 as 2.

3. What did you see instead (Required)

+------+---------+---------+---------+-----------+
| pkId | pkAttr0 | pkAttr1 | pkAttr2 | coAttr0_0 |
+------+---------+---------+---------+-----------+
|    1 |       1 |       1 |       1 |         1 |
+------+---------+---------+---------+-----------+

When we update a different value in session2, the last query can get correct value, i.e., when we execute the transaction below:

session1 > set session transaction isolation level repeatable read;

session2 > set session transaction isolation level repeatable read;

session2 > start transaction;

session1 > start transaction;

session2 > update table1 set coAttr0_0 = 3 where pkAttr0=1 and pkAttr1=1 and pkAttr2=1; -- update a differenet value!
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
session2 > commit;

session1 > update table1 set coAttr0_0 = 2 where pkAttr0=1 and pkAttr1=1 and pkAttr2=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session1 > select * from table1 where pkAttr0=1 and pkAttr1=1 and pkAttr2=1;

The last query will return :

+------+---------+---------+---------+-----------+
| pkId | pkAttr0 | pkAttr1 | pkAttr2 | coAttr0_0 |
+------+---------+---------+---------+-----------+
|    1 |       1 |       1 |       1 |         2 |
+------+---------+---------+---------+-----------+
1 row in set (0.00 sec)

It seems that some opt when two transactions update a same value may go wrong.

4. What is your TiDB version? (Required)

| Release Version: v6.5.0
Edition: Community
Git Commit Hash: 706c3fa
Git Branch: heads/refs/tags/v6.5.0
UTC Build Time: 2022-12-27 03:50:44
GoVersion: go1.19.3
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |

[wengsy150943]

We find that this issue is similar to #28011, which is fixed by #42488 .

However, this bug still occurs in v7.4.0.
We explain the UPDATE statement and find that it uses point_get instead of IndexLookUp, which may be the root cause.

explain update table1 set coAttr0_0 = 2 where pkAttr0=1 and pkAttr1=1 and pkAttr2=1;
+-------------------+---------+------+--------------------------------------------------------+---------------+
| id                | estRows | task | access object
            | operator info |
+-------------------+---------+------+--------------------------------------------------------+---------------+
| Update_3          | N/A     | root |
            | N/A           |
| └─Point_Get_1     | 1.00    | root | table:table1, index:PRIMARY(pkAttr0, pkAttr1, pkAttr2) |               |
+-------------------+---------+------+--------------------------------------------------------+---------------+

The TiDB version is:

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.4.0
Edition: Community
Git Commit Hash: 38cb4f3312be9199a983c0ef282d2ea2e28a7824
Git Branch: heads/refs/tags/v7.4.0
UTC Build Time: 2023-10-10 14:18:50
GoVersion: go1.21.1
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+