InnoDB Locks and Deadlocks with or without Index for Different Isolation Level

[This article was written by Nilnandan Joshi] 

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

------------
TRANSACTIONS
------------
Trx id counter 29502
Purge done for trx's n:o < 29500 undo n:o < 0 state: running but idle
History list length 86
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 41, OS thread handle 0x7f2e901f5700, query id 175 localhost root init
show engine innodb status
---TRANSACTION 29501, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 40, OS thread handle 0x7f2e90226700, query id 174 localhost root update
INSERT INTO data_col VALUES ('med6', 1,'2014-06-03 00:00:00', 'server6')
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 16 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `nil`.`data_col` trx id 29501 lock_mode X insert intention waiting
------------------
---TRANSACTION 29500, ACTIVE 10 sec
2 lock struct(s), heap size 360, 13 row lock(s)
MySQL thread id 39, OS thread handle 0x7f2e90257700, query id 173 localhost root cleaning up

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

------------
TRANSACTIONS
------------
Trx id counter 29452
Purge done for trx's n:o < 29450 undo n:o < 0 state: running but idle
History list length 68
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 41, OS thread handle 0x7f2e901f5700, query id 140 localhost root init
show engine innodb status
---TRANSACTION 29451, ACTIVE 23 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 40, OS thread handle 0x7f2e90226700, query id 139 localhost root Sending data
SELECT * FROM data_col WHERE expires < '2014-07-01' ORDER BY expires LIMIT 1 FOR UPDATE
------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15 page no 4 n bits 80 index `expires` of table `nil`.`data_col` trx id 29451 lock_mode X locks rec but not gap waiting
------------------
---TRANSACTION 29450, ACTIVE 29 sec
3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 39, OS thread handle 0x7f2e90257700, query id 138 localhost root cleaning up

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

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-04-04 14:22:49 7f3f0a084700
*** (1) TRANSACTION:
TRANSACTION 29051, ACTIVE 52 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 9 row lock(s)
MySQL thread id 39, OS thread handle 0x7f3f0a0b5700, query id 251 localhost root Creating sort index
SELECT * FROM data_col WHERE expires < '2014-07-01' ORDER BY expires FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15 page no 3 n bits 80 index `PRIMARY` of table `nil`.`data_col` trx id 29051 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 29052, ACTIVE 13 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 40, OS thread handle 0x7f3f0a084700, query id 252 localhost root Creating sort index
SELECT * FROM data_col WHERE expires < '2014-07-01' ORDER BY expires FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 15 page no 3 n bits 80 index `PRIMARY` of table `nil`.`data_col` trx id 29052 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15 page no 3 n bits 80 index `PRIMARY` of table `nil`.`data_col` trx id 29052 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)
------------

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

------------
TRANSACTIONS
------------
Trx id counter 29454
Purge done for trx's n:o < 29450 undo n:o < 0 state: running but idle
History list length 68
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 41, OS thread handle 0x7f2e901f5700, query id 149 localhost root init
show engine innodb status
---TRANSACTION 29453, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 40, OS thread handle 0x7f2e90226700, query id 148 localhost root update
INSERT INTO data_col VALUES ('med11', 1,'2014-06-11 00:00:00', 'server11')
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15 page no 3 n bits 80 index `PRIMARY` of table `nil`.`data_col` trx id 29453 lock_mode X locks gap before rec insert intention waiting
------------------
---TRANSACTION 29452, ACTIVE 18 sec
2 lock struct(s), heap size 360, 12 row lock(s)
MySQL thread id 39, OS thread handle 0x7f2e90257700, query id 147 localhost root cleaning up
--------

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.

• http://bugs.mysql.com/bug.php?id=71638 – “Manual does not explain “insert intention” lock mode properly”
• http://bugs.mysql.com/bug.php?id=71736 – “Manual does not explain locks set by UPDATE properly”
• http://bugs.mysql.com/bug.php?id=71916 – “Manual does not explain locks set for UPDATE … WHERE PK=’const’ properly”
• http://bugs.mysql.com/bug.php?id=71735 – “Manual does not explain locks set by SELECT … FOR UPDATE properly”

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).