maria-discuss team mailing list archive

[Yuri Karaban <launchpad@xxxxxxxxx>]

Thank you Justin for the answer!

I don't think that full table scan is used (because I was able to insert
rows after 7, when I have conditions n <= 4, n <= 3, ...)

But to be on the safe side I checked my query with EXPLAIN and it shows
that index scan on PRIMARY is used. Also to be absolutely sure I tried
with FORCE INDEX (PRIMARY), but results are the same.

Switching to READ-COMMITTED with RBR was the first thing which I did
after I noticed locking issues. But later I was thinking and I realized
that READ-COMMITTED is not really necessary for that kind of query, and
REPEATABLE-READ should be fine (if it locks rows from negative infinity
up to the row which I used in condition it's ok, I can insert more rows
without locking).

Then I started to play with synthetic tests, and I realized that
REPEATABLE-READ does not behave as documented (for InnoDB it locks not
just scanned rows, but two next-key intervals above the maximum scanned
row)

My question is more academic than practical. Before I felt I understand
how transaction isolation levels work, because theoretical knowledge
obtained from documentation was confirmed with practice. But now I hit
the edge case which is not documented (or I'm looking at wrong place)
and I'm desperately looking for explanation.

>>>>> "JS" == Justin Swanhart <greenlion@xxxxxxxxx> writes:

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

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

    JS>> Regards,

    JS>> --Justin


    JS>> 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
    >> 
    JS>> _______________________________________________ Mailing list:
    JS>> https://launchpad.net/~maria-discuss Post to :
    JS>> maria-discuss@xxxxxxxxxxxxxxxxxxx Unsubscribe :
    JS>> https://launchpad.net/~maria-discuss More help :
    JS>> https://help.launchpad.net/ListHelp

-- 
Quidquid latine dictum sit, altum videtur.