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@gmail.com> 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@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 >> JS>> _______________________________________________ Mailing list: JS>> https://launchpad.net/~maria-discuss Post to : JS>> maria-discuss@lists.launchpad.net Unsubscribe : JS>> https://launchpad.net/~maria-discuss More help : JS>> https://help.launchpad.net/ListHelp -- Quidquid latine dictum sit, altum videtur.