"Select For Update" in decorrelated subquery return WriteConflictError in pessimistic mode

[lysu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t(id int primary key, v int);
insert into t values(1, 10), (2, 20);

-- txn1: begin pessimistic;
-- txn1: select * from t;
-- txn2: update t set v = v * 10;
-- txn1:  select (select v from t where id = 2 for update) from dual;

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

last stmt return newest value --- 200, just as normal select v from t where id = 2 for update

3. What did you see instead (Required)

ERROR 9007 (HY000): Write conflict, txnStartTS=422143966855823360, conflictStartTS=422143970145468416, conflictCommitTS=422143970145730560, key={tableID=49, handle=2} primary=[]byte(nil) [try again later]

4. What is your TiDB version? (Required)

master

[lysu]

EvalSubqueryFirstRow

tidb/executor/executor.go

Line 1156 in 960dab5

err = Next(ctx, exec, chk)

maybe also need handlePessimisticLockError like handlePessimisticSelectForUpdate method

[cfzjywxk]

As the usage is not that common, I change the severity to minor and the pr could be merged if @lysu has time.