Bug #117421 Redundant row-level locking for using order DESC

• Submitted: 10 Feb 8:57
• Modified: 12 Feb 0:29
• Reporter: Link li
• Status: Not a Bug
• Category: MySQL Server
• Severity: S5 (Performance)
• Version: 8.0.32
• OS: Any
• CPU Architecture: Any

[10 Feb 8:57] Link li

Description:
when using order DESC in non-unique index, mysql will have some redundant locking.

How to repeat:
Table info:
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;
 
insert into test values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

session A:
  begin;
  select * from test where c>=15 and c<=20 for update;
session B:
  insert into test values(7,7,7); ——blocked;

---------------------------------------
when using "select * from performance_schema.data_locks\G;" to check the lock status, it will show a next-key lock of c index 10.

actually i know the reason why scan, lock and read c index 10, but keep the next-key lock in c index 10 will call redundant locking.

[10 Feb 12:01] MySQL Verification Team

Hi Mr. li,

Thank you for your bug report.

However, this is not a bug.

When index is not unique, InnoDB also has to lock space between the adjacent records. 

This is all explained in our Reference Manual, which you can find on:

https://dev.mysql.com/doc/refman/8.0/en/

Not a bug !!!!

[10 Feb 12:31] Link li

so it means record lock won't happen in non-unique index ,right?

[10 Feb 12:31] Link li

so it means record lock won't happen in non-unique index ,right?

[10 Feb 13:36] MySQL Verification Team

Hi Mr. li,

Of course that locks are also taken on unique index, but not the gap locks.

With unique index you can get only two types of gap locks. Infimum and supremum locks.

Please, read the Reference Manual.

[11 Feb 12:01] MySQL Verification Team

Hi,

I have one more addition to my previous answer.

There are cases where gap locks can be taken for unique index.

Imagine unique index on the INT column, with valued 3, 22, 143, 1055. 

These are all unique values, but there are still gaps !!!!!

[12 Feb 0:29] Link li

OK, tks, I get it.