Transaction can't read its own update in repeatable read.
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## 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: 706c3fa3c526cdba5b3e9f066b1a568fb96c56e3
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 |
Contributor guide
Assessment
This issue has not been assessed yet.