pingcap / pingcap/tidb

finishStmt connection-alive kill (#68481) aborts its own pessimistic-lock rollback, orphaning locks until TTL

Open
#70,974 2 comments 0 reactions 1 assignee Claimed by @King-Dylan View on GitHub
affects-8.5 contribution may-affects-25.10 may-affects-26.3 may-affects-7.5 may-affects-8.1 report/customer severity/major sig/transaction type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

### 1. Minimal reproduce step (Required)
Single-node TiDB v8.5.7 or v8.5.8. Config: [pessimistic-txn] pessimistic-auto-commit = true

```
CREATE TABLE t (id VARBINARY(255), v INT, PRIMARY KEY (id) /*T![clustered_index] NONCLUSTERED */);
INSERT INTO t VALUES ('a',0),('b',0);
SET GLOBAL tidb_txn_mode = 'pessimistic';
```

Session A — hold a conflicting lock >1s, then release:
```
BEGIN PESSIMISTIC;
UPDATE t SET v = v + 1 WHERE id = 'a';
-- wait ~1.5s
COMMIT;
```

Session B — autocommit DML that blocks on A, therefore exceeds 1s, and whose socket dies before the server commits. The socket close is the essential part:
```
import pymysql
c = pymysql.connect(host="127.0.0.1", port=4000, user="root", database="test",
autocommit=True, read_timeout=1.0) # socket dies at ~1s
with c.cursor() as cur:
cur.execute("SET innodb_lock_wait_timeout = 1")
try:
cur.execute("INSERT INTO t VALUES ('a',1),('b',1) "
"ON DUPLICATE KEY UPDATE v = VALUES(v)")
except Exception as e:
print("client gave up:", e) # 2013 Lost connection
c.close()
```
Have A commit at ~1.2s so B's statement acquires its locks and completes successfully, reaching finishStmt past the 1s threshold with a dead socket. Then from a third session SELECT ... FOR UPDATE WHERE id IN ('a','b') and watch it block.

### 2. What did you expect to see? (Required)
The statement is killed (that is #68481's intent, and acceptable), and its pessimistic locks are released by the rollback the kill triggers.

### 3. What did you see instead (Required)
The kill signal aborts the very rollback it caused:
```
[WARN] [sqlkiller.go:66] ["kill initiated"] [reason="[executor:1317]Query execution was interrupted"]
[WARN] [txn.go:1836] ["[kv] pessimisticRollback failed."] [error="query interrupted by signal 1"]
[WARN] [lock_resolver.go:749] ["lock txn not found, lock has expired"]

```
The self-interfering path, verified in source:

1. pkg/session/tidb.go:251 (finishStmt) → SQLKiller.CheckConnectionAlive()
2. SendKillSignal(QueryInterrupted) sets killer.Signal = 1
3. TiDB aliases that same word into client-go: KVVars.Killed = &SQLKiller.Signal (pkg/distsql/context/context.go:117, pkg/executor/select.go:346)
4. meetsErr != nil → StmtRollback → client-go releases locks via asyncPessimisticRollback, whose backoffer is built with the same vars: retry.NewBackofferWithVars(ctx, pessimisticRollbackMaxBackoff, txn.vars) (client-go
txnkv/transaction/txn.go:1834)
5. Backoffer.CheckKilled() (config/retry/backoff.go:385-387) reads atomic.LoadUint32(b.vars.Killed), sees 1, returns the kill error — called from backoff.go:220, so any retry during rollback aborts
6. pessimisticRollbackMutations fails → locks orphaned

Measured (identical workload, 32 clients, 180s, one variable — the client socket):

```
┌───────────────────────────────────────────┬───────────────────┬───────────────────┐
│ │ socket dies at 1s │ socket stays open │
├───────────────────────────────────────────┼───────────────────┼───────────────────┤
│ kill initiated (executor:1317) │ 513 │ 0 │
├───────────────────────────────────────────┼───────────────────┼───────────────────┤
│ pessimisticRollback failed. (signal 1) │ 35 │ 0 │
├───────────────────────────────────────────┼───────────────────┼───────────────────┤
│ orphaned locks, owner already killed │ 213 / 213 │ 0 │
├───────────────────────────────────────────┼───────────────────┼───────────────────┤
│ lock_resolver.go:749 "lock txn not found" │ 213 │ 0 │
└───────────────────────────────────────────┴───────────────────┴───────────────────┘
```

Orphan lifetime: TTL 20.0–21.1s, encountered by other transactions a median 50s and up to 131s after the kill. Amplified by the defaults in-memory = true and pipelined = true, under which abandoned locks sit in the leader's in-memory
lock table and block unrelated transactions. Also reproduced by cherry-picking #68481 alone onto v8.5.6 (6 files, +228/−1) — so it is necessary and sufficient.

### 4. What is your TiDB version? (Required)
Introduced in v8.5.7 by #68481 (issue #68236); present in v8.5.8; absent in v8.5.6 (shouldCheckConnectionAliveBeforeCommit: 0 occurrences at v8.5.6, 2 at v8.5.7/8).

Suggested fix: build the rollback's backoffer with vars whose Killed pointer is nil so CheckKilled() cannot abort teardown (narrow fix); or mask SQLKiller.Signal during teardown; or skip the check when the transaction holds
pessimistic locks / run it after the commit decision.

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.