pingcap / pingcap/tidb

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

Open
#42,487 1 comment 0 reactions 0 assignees View on GitHub
sig/transaction type/bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.