MySQL(InnoDB)でのDeadLock調査

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

mysql> SHOW ENGINE INNODB STATUS;

-- (省略) --

=====================================
2017-09-30 06:06:40 0x7f215046a700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 47 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 35 srv_active, 0 srv_shutdown, 2694 srv_idle
srv_master_thread log flush and writes: 2729
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 576
OS WAIT ARRAY INFO: signal count 495
RW-shared spins 0, rounds 244, OS waits 117
RW-excl spins 0, rounds 222, OS waits 9
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 244.00 RW-shared, 222.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-30 05:34:37 0x7f215811b700
*** (1) TRANSACTION:
TRANSACTION 10214, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 37, OS thread handle 139781060650752, query id 6538 172.17.0.1 root update
insert into `lock_test` -- (省略) -- <= DeadLockとなったSQL文(1)

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 47 page no 3 n bits 96 index PRIMARY of table `temp`.`lock_test` trx id 10214 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;;

*** (2) TRANSACTION:
TRANSACTION 10215, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 25, OS thread handle 139781188204288, query id 6540 172.17.0.1 root update
insert into `lock_test` -- (省略) -- <= DeadLockとなったSQL文(2)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 47 page no 3 n bits 96 index PRIMARY of table `temp`.`lock_test` trx id 10215 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 47 page no 3 n bits 96 index PRIMARY of table `temp`.`lock_test` trx id 10215 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;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 10239
Purge done for trx's n:o < 10220 undo n:o < 0 state: running but idle
History list length 36
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421256463523440, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421256463522528, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421256463521616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

-- (省略) --

----------------------------
END OF INNODB MONITOR OUTPUT
============================

*** (1) TRANSACTION:
...
insert into `lock_test` -- (省略) -- <= DeadLockとなったSQL文(1)
...
*** (2) TRANSACTION:
...
insert into `lock_test` -- (省略) -- <= DeadLockとなったSQL文(2)
...
*** WE ROLL BACK TRANSACTION (2)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 47 page no 3 n bits 96 index PRIMARY of table `temp`.`lock_test` trx id 10215 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;