I'm using 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 (tested on MySQL Community Server 8.0.18).
CREATE TABLE foo (
a INT NOT NULL,
b INT NOT NULL,
c CHAR(1),
PRIMARY KEY (a),
KEY (b)
) ENGINE=InnoDB;
INSERT INTO foo VALUES (1,1,'A'), (3,3,'B'), (5,5,'C'), (7,7,'D'), (9,9,'E');
Test case 1
Session 1:
START TRANSACTION;
SELECT * FROM foo WHERE a < 2 FOR UPDATE;
Session 2:
DELETE FROM foo WHERE a = 3; -- Success
Test case 2
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 index entry with b = 3 depends only on the uniqueness of the index used. The transaction isolation level does not matter.
Why does InnoDB block the next index entry to the right of the scanned range in case of a non-unique 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?
If there is no practical reason in this lock, then such a lock is most likely a bug. I think that if the RR isolation level is used, then it's enough to set a gap lock on the index record with b = 3, not a next-key lock. If a weaker isolation level is used, then this index record does not need to be blocked at all.