MySQL Bugs: #104758: UPDATE gets unnecessary locks in Repeatable-read isolation level

Bug #104758	UPDATE gets unnecessary locks in Repeatable-read isolation level
Submitted:	29 Aug 2021 12:26	Modified:	30 Aug 2021 13:16
Reporter:	Dai Dinary	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S5 (Performance)
Version:	Ver 8.0.25 for Linux on x86_64	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	locks, performance, REPEATABLE-READ

Description:
MySQL cannot reduce lock granularity when operating on columns without index in Repeatable Read isolation. If a transaction has an UPDATE statement and there is no index on the column to be updated, the lock will be table-level. So, even if the WHERE clause is "false" in UPDATE, it will also block all after transactions.

How to repeat:
/* init */ drop table t if exists;
/* init */ create table t(a int, b int);
/* init */ insert into t values(1, 1), (2, 2);

/* s1 */ begin;
/* s1 */ update t set a = 10 where false;
/* s2 */ begin;
/* s2 */ insert into t values(3, 3); -- blocked

The WHERE clause in transaction1 'where false' does not conflict with the INSERT statement in transaction2 (a=3, b=3), which means the latter will not be locked by the former anymore.

Suggested fix:
Analyze statements' WHERE clause further to get more information about conflicting of two transactions to reduce the granularity as much as possible.

Hi Mr. Dinary,

Thank you for your bug report.

However, it is not a bug.

InnoDB does not lock the table when there is no covering index that can be used for the DML. Instead, InnoDB locks all rows that it has to read in order to locate the rows that can be changed. This is easily changed by the introduction of the suitable index, provided the table is large enough.

This is described in our Manual.

Not a bug.

Hi,

Thanks for your reply.

Do you mean the following paragraph in https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html ?

"UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row."

But in my opinion, the "WHERE false" clause need not do any search, because it is obvious that there is no record match this search condition. So this UPDATE statement should not set any lock. 

Do I miss something or make a mistake?

Thank you for your reply again.