Instance level plan cache may cause "ERROR 8141 assertion failed" in UPDATE stmt under high concurrency

[time-and-fate]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Code changes

To make it reproducible without high concurrency, we need to introduce latency in the code.

tidb/pkg/executor/select.go

Lines 891 to 899 in 7c0c2b1

	var sc *stmtctx.StatementContext
	if vars.TxnCtx.CouldRetry || vars.HasStatusFlag(mysql.ServerStatusCursorExists) {
	// Must construct new statement context object, the retry history need context for every statement.
	// TODO: Maybe one day we can get rid of transaction retry, then this logic can be deleted.
	sc = stmtctx.NewStmtCtx()
	} else {
	sc = vars.InitStatementContext()
	}
	sc.SetTimeZone(vars.Location())

Modify the code here as below:

	var sc *stmtctx.StatementContext
	if vars.TxnCtx.CouldRetry || vars.HasStatusFlag(mysql.ServerStatusCursorExists) {
		// Must construct new statement context object, the retry history need context for every statement.
		// TODO: Maybe one day we can get rid of transaction retry, then this logic can be deleted.
		sc = stmtctx.NewStmtCtx()
	} else {
		sc = vars.InitStatementContext()
	}
+	if !vars.InRestrictedSQL {
+		time.Sleep(1 * time.Second)
+	}
	sc.SetTimeZone(vars.Location())

The patch would make the SQL execution slow, be patient~

Reproduction Steps

1. Preparation

set global tidb_enable_instance_plan_cache = 1;
create table t(a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, b int, c int, primary key(a), unique key(b,c));
insert into t(b,c) value (1,1);
insert into t(b,c) value (2,2);

2. Trigger

We need prepare two sessions (connections) here.

In session 1:

prepare stmt from 'update t set a = NOW() where b = ? and c = ?';
set @a = 1;
execute stmt using @a, @a;
execute stmt using @a, @a;

In session 2:

prepare stmt from 'update t set a = NOW() where b = ? and c = ?';
set @a = 1;

Then execute the SQL below first in session 2, then immediately in session 1. This needs to be very fast.

execute stmt using @a, @a;

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

No errors.

3. What did you see instead (Required)

ERROR 8141 (HY000): assertion failed: key: 7480000000000000705f720419b69d6e07000000, assertion: Exist, start_ts: 458027064388747265, existing start ts: 0, existing commit ts: 0

4. What is your TiDB version? (Required)

v8.5.0, v9.0.0-beta.1, and the latest nightly