数据库死锁日志查看

https://segmentfault.com/a/1190000018730103

1	show engine innodb status;

记录锁，间隙锁，Next-key 锁和插入意向锁。这四种锁对应的死锁如下：

记录锁（LOCK_REC_NOT_GAP）: lock_mode X locks rec but not gap
间隙锁（LOCK_GAP）: lock_mode X locks gap before rec
Next-key 锁（LOCK_ORNIDARY）: lock_mode X
插入意向锁（LOCK_INSERT_INTENTION）: lock_mode X locks gap before rec insert intention

关于显式锁和隐式锁

死锁日志

=====================================
2018-08-05 21:20:27 0x7fd40c082700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 251 srv_active, 0 srv_shutdown, 22663 srv_idle
srv_master_thread log flush and writes: 22905
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 513
OS WAIT ARRAY INFO: signal count 450
RW-shared spins 0, rounds 569, OS waits 286
RW-excl spins 0, rounds 127, OS waits 1
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 569.00 RW-shared, 127.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-08-05 21:15:42 0x7fd40c0b3700
*** (1) TRANSACTION:
TRANSACTION 1095010, ACTIVE 21 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 16, OS thread handle 140548578129664, query id 3052 183.6.50.229 root update
insert into t_bitfly values(7,7)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2514 page no 4 n bits 72 index num_key of table `test`.`t_bitfly` trx id 1095010 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000007; asc     ;;
 1: len 8; hex 8000000000000008; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 1095015, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 17, OS thread handle 140548711855872, query id 3056 183.6.50.229 root update
insert into t_bitfly values(5,5)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2514 page no 4 n bits 72 index num_key of table `test`.`t_bitfly` trx id 1095015 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;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000007; asc     ;;
 1: len 8; hex 8000000000000008; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2514 page no 4 n bits 72 index num_key of table `test`.`t_bitfly` trx id 1095015 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000007; asc     ;;
 1: len 8; hex 8000000000000008; asc         ;;

省略。。。

一些注释：
LATEST DETECTED DEADLOCK：标示为最新发生的死锁；
(1) TRANSACTION：此处表示事务1开始；
MySQL thread id 16, OS thread handle 140548578129664, query id 3052 183.6.50.229 root update：此处为记录当前数据库线程id；
insert into t_bitfly values(7,7)：表示事务1在执行的sql ，不过比较悲伤的事情是show engine innodb status 是查看不到完整的事务的sql 的，通常显示当前正在等待锁的sql；
(1) WAITING FOR THIS LOCK TO BE GRANTED：此处表示当前事务1等待获取行锁；
(2) TRANSACTION：此处表示事务2开始；
insert into t_bitfly values(5,5)：表示事务2在执行的sql
(2) HOLDS THE LOCK(S)：此处表示当前事务2持有的行锁；
(2) WAITING FOR THIS LOCK TO BE GRANTED：此处表示当前事务2等待获取行锁；