optimistic transaction does not respect tidb_constraint_check_in_place

[lcwangchao]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t(id int primary key, u int unique);
insert into t values (1, 1), (2, 2);
set @@tidb_constraint_check_in_place=0;
begin optimistic;
update t set u=2 where id=1;

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

update should not report any error in optimistic transaction because tidb_constraint_check_in_place is off

3. What did you see instead (Required)

> update t set u=2 where id=1;
(1062, "Duplicate entry '2' for key 't.u'")

4. What is your TiDB version? (Required)

[lcwangchao]

As the discussions in #54495 (comment) .

The purpose of tidb_constraint_check_in_place is only to make the lazy unique index check for insert effective. Historically, only inserts had a lazy unique index check. tidb_constraint_check_in_place was actually designed to remove this exclusive behavior for inserts, so that all statements execute consistent in-place checks. Therefore, update statements have never had a lazy check. So even if tidb_constraint_check_in_place is off, updates should still report an error.

As for why the name was designed this way, that was the product manager's decision at the time. However, I don't think it's necessary to make updates lazy check as well. The reason insert needs lazy check is because it performs more efficiently during bulk loading. Bulk loading should rarely involve updates, right?

This is by design and we can close this issue.

[jackysp]

I was just about to comment, haha.