I'm using MySQL InnoDB tables and trying to understand the reasons for some row-level locking in the case of an index range scan. I found that an extra index record (out of range) may be locked depending on the uniqueness of the index used. See the example below (verified in version 8.0.18).
CREATE TABLE foo (
a INT NOT NULL,
b INT NOT NULL,
c CHAR(1) NOT NULL,
PRIMARY KEY (a),
UNIQUE KEY (b)
) ENGINE=InnoDB;
INSERT INTO foo VALUES (1,1,'A'), (3,3,'B'), (5,5,'C'), (7,7,'D'), (9,9,'E');
Session 1:
START TRANSACTION;
SELECT * FROM foo WHERE a < 2 FOR UPDATE;
Session 2:
DELETE FROM foo WHERE a = 3; -- Success
This uses the original rows of the table with the deleted record returned.
Session 1:
START TRANSACTION;
SELECT * FROM foo WHERE b < 2 FOR UPDATE;
Session 2:
DELETE FROM foo WHERE b = 3; -- Blocks
Locking the secondary index record with b = 3 in the second test case looks unnecessary.
Why does InnoDB block the next index entry to the right of the scanned range in case of a secondary index? Is there any practical reason for this? Can someone give an example of a problem that could happen if the record with b = 3 is not blocked in the second test case?
Finally I found the answer. In short, there are no significant reasons for such additional locking in the second test case. When a locking read of a secondary index range is performed, sufficient locks are set, but not a necessary minimum. Thus, extra locks are set only because it was easier for some InnoDB programmers to write code. Who cares about extra locks if everything works for the most part?
I posted a bug report about this issue: https://bugs.mysql.com/bug.php?id=98639 Unfortunately, their employee does not want to register this bug. He does not understand my arguments and comes up with erroneous explanations. He made my last arguments private and stopped responding.
I also asked about this issue in the official forum and received the following answer: https://forums.mysql.com/read.php?22,684356,684482 In short, significant efforts are required to fix this bug. But since this is a very small and insignificant bug (more precisely, a performance issue), they do not want to fix it yet. However, in version 8.0.18, they fixed a similar problem for the clustered index, and it took them more than a month.
I'm very surprised that optimizing such a simple single-level scanning algorithm takes so much time and is so difficult for the MySQL team.