mysql> show variables like '%output_locks'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | innodb_status_output_locks | OFF | +----------------------------+-------+ 1 row in set (0.01 sec)
mysql> set global innodb_status_output_locks=ON; Query OK, 0 rows affected (0.00 sec)
---TRANSACTION 7714, ACTIVE 2 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 MySQL thread id 12, OS thread handle 140057782679296, query id 94 localhost root executing insert into a select 4 ------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 70 page no 4 n bits 72 index id of table `test`.`a` trx id 7714 lock mode S waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 6; hex 000000000302; asc ;;
------------------ TABLE LOCK table `test`.`a` trx id 7714 lock mode IX RECORD LOCKS space id 70 page no 4 n bits 72 index id of table `test`.`a` trx id 7714 lock mode S waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 6; hex 000000000302; asc ;;
---TRANSACTION 7713, ACTIVE 83 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 11, OS thread handle 140057782945536, query id 93 localhost root executing insert into a select 4 ------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 70 page no 4 n bits 72 index id of table `test`.`a` trx id 7713 lock mode S waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 6; hex 000000000302; asc ;;
------------------ TABLE LOCK table `test`.`a` trx id 7713 lock mode IX RECORD LOCKS space id 70 page no 4 n bits 72 index id of table `test`.`a` trx id 7713 lock mode S waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 6; hex 000000000302; asc ;;
---TRANSACTION 7712, ACTIVE 454 sec 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 MySQL thread id 7, OS thread handle 140057783478016, query id 45 localhost root TABLE LOCK table `test`.`a` trx id 7712 lock mode IX RECORD LOCKS space id 70 page no 4 n bits 72 index id of table `test`.`a` trx id 7712 lock_mode X locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 6; hex 000000000302; asc ;;
------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-05-14 14:50:49 0x7f61be668700 *** (1) TRANSACTION: TRANSACTION 7715, ACTIVE 4 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 12, OS thread handle 140057782679296, query id 98 localhost root executing insert into a select 4 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 70 page no 4 n bits 72 index id of table `test`.`a` trx id 7715 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 6; hex 000000000301; asc ;;
*** (2) TRANSACTION: TRANSACTION 7713, ACTIVE 129 sec inserting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1 MySQL thread id 11, OS thread handle 140057782945536, query id 96 localhost root executing insert into a select 4 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 70 page no 4 n bits 72 index id of table `test`.`a` trx id 7713 lock mode S locks gap before rec Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 6; hex 000000000301; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 70 page no 4 n bits 72 index id of table `test`.`a` trx id 7713 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 6; hex 000000000301; asc ;;