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*/