InnoDB locks and deadlocks with or without index for different isolation level

Recently, I was working on one of the issue related to locks and deadlocks with InnoDB tables and I found very interesting details about how InnoDB locks and deadlocks works with or without index for different Isolation levels.

Here, I would like to describe a small test case about how SELECT ..FOR UPDATE (with and without limit) behave with INSERT/UPDATE and with READ-COMMITED and REPEATABLE-READ Isolation levels. I’m creating a small table data_col with few records. Initially, this test case was written by Bill Karwin to explain details to customer, but here I have used a bit modified test case.

CREATE TABLE data_col (dataname VARCHAR(10), period INT, expires DATE, host VARCHAR(10));

INSERT INTO data_col VALUES (‘med1′, 1,’2014-01-01 00:00:00’, ‘server1’);
INSERT INTO data_col VALUES (‘med2′, 1,’2014-02-15 00:00:00’, ‘server2’);
INSERT INTO data_col VALUES (‘med3′, 1,’2014-03-20 00:00:00’, ‘server3’);
INSERT INTO data_col VALUES (‘med4′, 1,’2014-04-10 00:00:00’, ‘server4’);
INSERT INTO data_col VALUES (‘med5′, 1,’2014-05-01 00:00:00’, ‘server5’);

Case 1: No index on expires, tx_isolation=READ-COMMITTED.

Session 1: SET tx_isolation=’READ-COMMITTED’; START TRANSACTION;
Session 2: SET tx_isolation=’READ-COMMITTED’; START TRANSACTION;
Session 1: SELECT * FROM data_col WHERE expires < ‘2014-03-01’ ORDER BY expires LIMIT 1 FOR UPDATE;
Session 2: INSERT INTO data_col VALUES (‘med6′, 1,’2014-06-03 00:00:00’, ‘server6’); /* success */
Session 1: SELECT * FROM data_col WHERE expires < ‘2014-07-01’ ORDER BY expires LIMIT 1 FOR UPDATE; /* hangs */
Session 2: SELECT * FROM data_col WHERE expires < ‘2014-07-01’ ORDER BY expires LIMIT 1 FOR UPDATE; /* deadlocks */

With READ-COMMITTED, even If Session 1 locks records with condition “expires < ‘2014-03-01’ “, Session 2 can Insert the record as Session 1 is not using gap lock (lock_mode X locks rec but not gap waiting) and we can insert/update the records outside of set of Session 1 examined. But when Session 1 tried to acquire locks on higher range (“expires < ‘2014-07-01’ “), it will be hanged and if we do the same thing from Session 2, it will turn to deadlock.

Here, When there is no primary key, InnoDB table will create it’s own cluster index, which is GEN_CLUST_INDEX.

Case 2: No index on expires, tx_isolation=REPEATABLE-READ.

Session 1: SET tx_isolation=’REPEATABLE-READ’; START TRANSACTION;
Session 2: SET tx_isolation=’REPEATABLE-READ’; START TRANSACTION;
Session 1: SELECT * FROM data_col WHERE expires < ‘2014-03-01’ ORDER BY expires LIMIT 1 FOR UPDATE;
Session 2: INSERT INTO data_col VALUES (‘med6′, 1,’2014-06-03 00:00:00’, ‘server6’); /* hangs */

With REPEATABLE-READ, we can see that when Session 1 locks records with condition “expires < ‘2014-03-01’ “, Session 2 can’t Insert the record and waiting for the lock release (lock_mode X insert intention waiting) from Session 1 because it’s using gap locks. Here,  insert intention is one type of gap lock.  This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

This is why for some scenario/ business logic, REPEATABLE-READ is better isolation level to prevent deadlocks by using more row locks. (including gap locks)

Case 3: Added Primary Key on dataname and Index on expires, tx_isolation=READ-COMMITTED.

Session 1: SET tx_isolation=’READ-COMMITTED’; START TRANSACTION;
Session 2: SET tx_isolation=’READ-COMMITTED’; START TRANSACTION;
Session 1: SELECT * FROM data_col WHERE expires < ‘2014-03-01’ ORDER BY expires LIMIT 1 FOR UPDATE;
Session 2: INSERT INTO data_col VALUES (‘med13′, 1,’2014-06-13 00:00:00’, ‘server13’); /* success */
Session 1: SELECT * FROM data_col WHERE expires < ‘2014-07-01’ ORDER BY expires LIMIT 1 FOR UPDATE; /* success */
Session 2: SELECT * FROM data_col WHERE expires < ‘2014-07-01’ ORDER BY expires LIMIT 1 FOR UPDATE; /* hang*/

If there is index on “expires” column which we use to filter the records, it will show different behavior with READ-COMMITED. In Case 1,  we were not able to acquire locks on higher range (“expires < ‘2014-07-01’ “)  while here, we can do that with index on expires. So when Session 1 has already locked the rows, another Session 2 can’t acquire the same lock and will be waiting to release locks from Session 1.

But it we remove LIMIT 1 from SELECT…FOR UPDATE then it will behave the same like Case 1.

———————–Just removed limit 1—————————————

Session 1: SET tx_isolation=’READ-COMMITTED’; START TRANSACTION;
Session 2: SET tx_isolation=’READ-COMMITTED’; START TRANSACTION;
Session 1: SELECT * FROM data_col WHERE expires < ‘2014-03-01’ ORDER BY expires FOR UPDATE;
Session 2: INSERT INTO data_col VALUES (‘med14′, 1,’2014-06-04 00:00:00’, ‘server14’); /* success */
Session 1: SELECT * FROM data_col WHERE expires < ‘2014-07-01’ ORDER BY expires FOR UPDATE; /* hang */
Session 2: SELECT * FROM data_col WHERE expires < ‘2014-07-01’ ORDER BY expires FOR UPDATE; /* deadlock*/

Case 4: Added Primary Key on dataname and Index on expires, tx_isolation=REPEATABLE-READ.

Session 1: SET tx_isolation=’REPEATABLE-READ’; START TRANSACTION;
Session 2: SET tx_isolation=’REPEATABLE-READ’; START TRANSACTION;
Session 1: SELECT * FROM data_col WHERE expires < ‘2014-07-01’ ORDER BY expires LIMIT 1 FOR UPDATE;
Session 2: INSERT INTO data_col VALUES (‘med15′, 1,’2014-06-10 00:00:00’, ‘server15’); /* success */

Here, Unlike Case 2, we’ll be able to INSERT record from Session 2 as Session 1 is not using gap lock.

——————–Successfully happened but if I’ll remove the limit 1, —————————-

Session 1: SET tx_isolation=’REPEATABLE-READ’; START TRANSACTION;
Session 2: SET tx_isolation=’REPEATABLE-READ’; START TRANSACTION;
Session 1: SELECT * FROM data_col WHERE expires < ‘2014-07-01’ ORDER BY expires FOR UPDATE;
Session 2: INSERT INTO data_col VALUES (‘med11′, 1,’2014-06-11 00:00:00’, ‘server11’); /* hang */

But here, Session 1 will use gap lock, so Session 2 can’t able to insert record until lock release.  To know more about deadlock, I would suggest to read below blog post, “How to deal with MySQL deadlocks” by my colleague Peiran Song.

There are plenty of blog posts to describe InnoDB locks but few things are still missing in manual. Here is the list of some of those bugs.

Conclusion: We can see from above test case that to prevent deadlocks, sometimes we have to use indexes,  sometimes higher isolation level helps, even if it counter-intuitive and sometimes application OR table schema changes can help.

If you are coming to the Percona Live MySQL Conference and Expo 2015, then visit me for this talk: “Understanding InnDB locks and deadlocks” (16 April 3:00PM – 3:50PM @ Ballroom A).

Share this post

Comment (1)

  • sheyda Reply

    Regardless of the isolation level, the most efficient way to update/insert/delete is to do it in an incremental fashion/small batches to avoid deadlocks.

    April 9, 2015 at 2:51 pm

Leave a Reply