SELECT FOR UPDATE transaction aborts at COMMIT with PessimisticLockNotFound after waiting for concurrent UPDATE
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
### 1. Minimal reproduce step (Required)
This issue can be reproduced on TiDB v8.5.6 under both READ COMMITTED and REPEATABLE READ.
Step1: Create the inital DBMS state:
```sql
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 NUMERIC UNSIGNED);
INSERT IGNORE INTO t0(c0) VALUES (0.123424238);
```
Use two transactions:
-- Transaction 404
```sql
[404-0] BEGIN;
[404-1] INSERT INTO t0(c0) VALUES (1.0790), (6996925);
[404-2] UPDATE t0 SET c0='0.37594' WHERE TRUE;
[404-3] COMMIT;
```
-- Transaction 405
```sql
[405-0] BEGIN;
[405-1] SELECT t0.c0 FROM t0 WHERE TRUE FOR UPDATE;
[405-2] COMMIT;
```
Execute the statements in the following order under READ COMMITTED:
```sql
[404-0] BEGIN;
[405-0] BEGIN;
[404-1] INSERT INTO t0(c0) VALUES (1.0790), (6996925);
[404-2] UPDATE t0 SET c0='0.37594' WHERE TRUE;
[405-1] SELECT t0.c0 FROM t0 WHERE TRUE FOR UPDATE;
-- This statement waits for Transaction 404.
[404-3] COMMIT;
[405-2] COMMIT;
```
Repeat the same schedule under REPEATABLE READ:
```sql
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
```
### 2. What did you expect to see? (Required)
[405-1] SELECT ... FOR UPDATE should wait for Transaction 404 to commit.
After [404-3] COMMIT, [405-1] should be unblocked, return the latest committed rows, and then [405-2] COMMIT should succeed.
Expected behavior:
[405-1] waits for [404-3] COMMIT.
[405-1] returns 3 rows after the wait is resolved.
[405-2] COMMIT succeeds.
It should not expose an internal TiKV MVCC error.
### 3. What did you see instead (Required)
Under both READ COMMITTED and REPEATABLE READ, [405-2] COMMIT stably fails with the following error:
```sql
ERROR 1105 (HY000): tikv aborts txn: Error(Txn(Error(Mvcc(Error(PessimisticLockNotFound {
start_ts: TimeStamp(...),
key: [...],
reason: LockMissingAmendFail
})))))
```
The transaction only performs SELECT ... FOR UPDATE and then COMMIT. If the SELECT ... FOR UPDATE has waited for the concurrent UPDATE transaction and then returned normally, the subsequent COMMIT should not fail with PessimisticLockNotFound.
This looks like an internal pessimistic-lock handling issue.
### 4. What is your TiDB version? (Required)
```sql
mysql> select version();
+--------------------+
| version() |
+--------------------+
| 8.0.11-TiDB-v8.5.6 |
+--------------------+
1 row in set (0.00 sec)
```
Contributor guide
Assessment
This issue has not been assessed yet.