maria-discuss team mailing list archive

[Yuri Karaban <launchpad@xxxxxxxxx>]

Hi

I'm trying to understand why InnoDB locks two next-key intervals higher
than needed.

If you try to run conn-a.sql from one connection, and then run
conn-b.sql from other connection, conn-b.sql would be locked.

I have a feeling that it locks two next-key intervals: (5 to 7 and 7 to
positive infinity). Because I can insert values higher than 7 in conn-b
only if condition is WHERE n <= 4 (or lower than 4).

TokuDB locks just one next-key interval (with WHERE n <= 7, TokuDB locks
from -infinity to +infinity, but with condition WHERE n <= 6, it locks
from -infinity to 6).

Please help me to understand why ever both engines need to lock gap
after the upper end of range scan. And why InnoDB needs to lock even two
next-key gaps.

I tried this on MariaDB 10.0.21

----------------------[conn-a.sql]--------------------------------
DROP TABLES IF EXISTS innosel, innoin;

CREATE TABLE innosel (n INT UNSIGNED PRIMARY KEY) ENGINE InnoDB;
CREATE TABLE innoin (n INT UNSIGNED PRIMARY KEY) ENGINE InnoDB;

INSERT INTO innosel values (1), (3), (5), (7);

SET tx_isolation = 'REPEATABLE-READ';

START TRANSACTION;
  INSERT IGNORE INTO innoin (n)
    SELECT n FROM innosel WHERE n <= 5;

  SELECT SLEEP(100);
ROLLBACK;
----------------------[conn-a.sql]--------------------------------


----------------------[conn-b.sql]--------------------------------
START TRANSACTION;
  INSERT INTO innosel VALUES (9);
ROLLBACK;
----------------------[conn-b.sql]--------------------------------

-- 
Nemo iudex in causa sua.