When your query is blocked, but there is no blocking query - Part 3

In the previous blog posts I've talked about transactions which block other transactions but don't do anything and about some possible solutions.

In this post I will show you how to get even more information about what is locked by a transaction.

As you might have noticed the information_schema.innodb_locks table doesn't show all locks. This is what the documentation says:
"The INNODB_LOCKS table contains information about each lock that an InnoDB transaction has requested but not yet acquired, and each lock that a transaction holds that is blocking another transaction."

So if would like to know all locks held by a transaction this doesn't help.

There is lots of information in the output of "SHOW ENGINE INNODB STATUS\G" in the section about TRANSACTIONS.

------------
TRANSACTIONS
------------
Trx id counter 8991
Purge done for trx's n:o < 8991 undo n:o < 0 state: running but idle
History list length 50
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x7ff0c8727700, query id 202 localhost msandbox init
show engine innodb status
---TRANSACTION 8972, ACTIVE 1313 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 2, OS thread handle 0x7ff0c8768700, query id 106 localhost msandbox update
insert into t1 values(4)
------- TRX HAS BEEN WAITING 1313 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 18 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t1` trx id 8972 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

------------------
---TRANSACTION 8970, ACTIVE 1652 sec
4 lock struct(s), heap size 1184, 10 row lock(s), undo log entries 9
MySQL thread id 1, OS thread handle 0x7ff0c87a9700, query id 21 localhost msandbox cleaning up

So there are 10 row locks held by transaction 8970 and transaction 8972 is waiting on record lock on test.t1. That's nothing new, that information is also in INNODB_LOCKS and INNODB_LOCK_WAITS.

But if we set  innodb_status_output_locks to ON this changes.

mysql [information_schema] > set global innodb_status_output_locks=ON;
Query OK, 0 rows affected (0.00 sec)

mysql [information_schema] > show engine innodb status\G
...some output removed...
------------
TRANSACTIONS
------------
Trx id counter 8991
Purge done for trx's n:o < 8991 undo n:o < 0 state: running but idle
History list length 50
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x7ff0c8727700, query id 204 localhost msandbox init
show engine innodb status
---TRANSACTION 8972, ACTIVE 1544 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 2, OS thread handle 0x7ff0c8768700, query id 106 localhost msandbox update
insert into t1 values(4)
------- TRX HAS BEEN WAITING 1544 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 18 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t1` trx id 8972 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

------------------
TABLE LOCK table `test`.`t1` trx id 8972 lock mode IX
RECORD LOCKS space id 18 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t1` trx id 8972 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

---TRANSACTION 8970, ACTIVE 1883 sec
4 lock struct(s), heap size 1184, 10 row lock(s), undo log entries 9
MySQL thread id 1, OS thread handle 0x7ff0c87a9700, query id 21 localhost msandbox cleaning up
TABLE LOCK table `test`.`t1` trx id 8970 lock mode IX
RECORD LOCKS space id 18 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t1` trx id 8970 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000313; asc       ;;
 1: len 6; hex 000000001f4e; asc      N;;
 2: len 7; hex bd000001750110; asc     u  ;;
 3: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000314; asc       ;;
 1: len 6; hex 000000001f4e; asc      N;;
 2: len 7; hex bd00000175011e; asc     u  ;;
 3: len 4; hex 80000002; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000315; asc       ;;
 1: len 6; hex 000000001f4e; asc      N;;
 2: len 7; hex bd00000175012c; asc     u ,;;
 3: len 4; hex 80000003; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000406; asc       ;;
 1: len 6; hex 000000002113; asc     ! ;;
 2: len 7; hex 92000001800110; asc        ;;
 3: len 4; hex 80000004; asc     ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 00000000040c; asc       ;;
 1: len 6; hex 000000002116; asc     ! ;;
 2: len 7; hex 95000001590110; asc     Y  ;;
 3: len 4; hex 80000004; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000412; asc       ;;
 1: len 6; hex 00000000211c; asc     ! ;;
 2: len 7; hex 990000015c0110; asc     \  ;;
 3: len 4; hex 80000004; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000419; asc       ;;
 1: len 6; hex 00000000211e; asc     ! ;;
 2: len 7; hex 9b0000014d0110; asc     M  ;;
 3: len 4; hex 80000004; asc     ;;

Record lock, heap no 9 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000500; asc       ;;
 1: len 6; hex 00000000230a; asc     # ;;
 2: len 7; hex 8a0000013c0110; asc     <  ;;
 3: len 4; hex 80000004; asc     ;;

TABLE LOCK table `tmp`.`#sql2cfe_1_0` trx id 8970 lock mode IX
RECORD LOCKS space id 18 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t1` trx id 8970 lock_mode X locks rec but not gap
Record lock, heap no 9 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000500; asc       ;;
 1: len 6; hex 00000000230a; asc     # ;;
 2: len 7; hex 8a0000013c0110; asc     <  ;;
 3: len 4; hex 80000004; asc     ;;
...some output removed...

Now we get to see the full information about the InnoDB locks.

There is one more thing in the SHOW ENGINE INNODB STATUS output you should pay attention to: deadlocks.

As you can see the record locks use the GEN_CLUST_INDEX index which is the index that is used if no primary key is specified. For some locking issues adding one or more indexes can fix the issue as InnoDB then doesn't need to take as many row locks.