MySQL Forums
Forum List  »  InnoDB

Redundant InnoDB lock
Posted by: Sergei Muraviev
Date: February 10, 2020 06:04AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Redundant InnoDB lock
153
February 10, 2020 06:04AM
72
February 17, 2020 06:06AM
44
February 17, 2020 10:59AM
43
February 18, 2020 06:42PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.