Hi,

I didn't use you test data, but i have a strong suspicion the problem is related to the small number of rows. Probably InnoDB is choosing a full table scan for the range condition on the SELECT, as it covers most of the table. You are using repeatable-read, which requires that InnoDB hold locks in innosel for the duration of the transaction. Since InnoDB locks each row it traverses, a full table scan will lock the ENTIRE table, including the gap that represents 6, and the gap after 7.

If you want to lock only the rows that match, you can do so, kind of. Switch to READ-COMMITTED and if you are using binary logging, to ROW based logging. Under these conditions if more rows than necessary are scanned (thus locked) the rows that don't match the filter will be unlocked after the statement completes. Then insertion of 6 and insertions beyond seven can be performed.

Regards,

--Justin

On Thu, Oct 8, 2015 at 6:18 AM, Yuri Karaban <launchpad@dev97.com> wrote:

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.

_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp