Sample Header Ad - 728x90

Why innodb locks index till the end?

4 votes
2 answers
415 views
Why a query BETWEEN two IDs locks index till the end?
UPDATE elem SET c='' WHERE id BETWEEN 2 AND 5;
-- locks id>5 for some reason
The database is MySQL 8.0.33 GA. Here's the full example, taken from the book "Efficient MySQL Performance" by Daniel Nichter.
CREATE TABLE elem (
  id int unsigned NOT NULL PRIMARY KEY,
  a  char(2) NOT NULL,
  b  char(2) NOT NULL,
  c  char(2) NOT NULL,
  KEY idx_a (a)
);

insert into elem values
(2, 'Au', 'Be', 'Co'), 
(5, 'Ar', 'Br', 'C');

BEGIN;
UPDATE elem SET c='' WHERE id BETWEEN 2 AND 5;

select INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA from performance_schema.data_locks;

+------------+-----------+---------------+------------------------+
| INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_DATA              |
+------------+-----------+---------------+------------------------+
| NULL       | TABLE     | IX            | NULL                   |
| PRIMARY    | RECORD    | X,REC_NOT_GAP | 2                      |
| PRIMARY    | RECORD    | X             | supremum pseudo-record |
| PRIMARY    | RECORD    | X             | 5                      |
+------------+-----------+---------------+------------------------+
The isolation level is default (RR). I understand why the range id=2..5 is locked. But what's the point of locking id>5? Does it matter if someone inserts there? P.S. Such locking happens when the upper BETWEEN condition is the last index element. That's perfectly reproducible and was mentioned in many articles, is there a sane reason for it (dire consequences if we don't lock id>5?), or it's an imperfection of INNODB locking mechanism?
Asked by John Smith (41 rep)
Apr 28, 2023, 08:03 PM
Last activity: Mar 6, 2025, 06:06 PM