maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>