[Maria-discuss] Can someone explain odd InnoDB locking behavior
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.
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
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.
participants (2)
-
Justin Swanhart -
Yuri Karaban