Pessimistic DML should not lock non-unique index keys

[ekexium]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t(id varchar(40), v int, PRIMARY KEY (`id`) clustered, key i2(v))
insert into t values ('123', 1)
begin pessimistic
update t set v = v + 1 where id = '123'
commit

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

The update statement is not supposed to lock the index key.

3. What did you see instead (Required)

The update statement acquires a pessimistic lock for the index key.

4. What is your TiDB version? (Required)

master (987bdd3)

[cfzjywxk]

@ekexium

After this for such execution plans:

update uk = uk where sk = 1; // The uk value is not changed
The plan:
update
    select lock
        index look up 

The unique key could be locked but #25730 would not work so the LOCK record could accumulate and lead to performance issues.

/cc @you06 @MyonKeminta @sticnarf

[cfzjywxk]

@ekexium

After this for such execution plans:

update uk = uk where sk = 1; // The uk value is not changed
The plan:
update
    select lock
        index look up 

The unique key could be locked but #25730 would not work so the LOCK record could accumulate and lead to performance issues.

/cc @you06 @MyonKeminta @sticnarf

Sorry I've replied to the wrong issue. It should be #36438...