MySQL的死锁可以通过show engine innodb status\G;来查看，
最近的死锁信息在LATEST DETECTED DEADLOCK下面。
但是这种方式只能显示最新的一条死锁信息，该方式无法完全捕获到系统发生的死锁信息。
MySQL 系统内部提供一个innodb_print_all_deadlocks参数，该参数默认是关闭的，
开启后可以将死锁信息自动记录到 MySQL 的错误日志中。下面我们来看下这个参数的作用：
# 查看参数是否开启mysql> show variables like 'innodb_print_all_deadlocks';+----------------------------+-------+| Variable_name | Value |+----------------------------+-------+| innodb_print_all_deadlocks | OFF |+----------------------------+-------+1 row in set (0.00 sec)# 开启innodb_print_all_deadlocks,此参数是全局参数，可以动态调整。mysql> set global innodb_print_all_deadlocks = 1;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'innodb_print_all_deadlocks';+----------------------------+-------+| Variable_name | Value |+----------------------------+-------+| innodb_print_all_deadlocks | ON |+----------------------------+-------+1 row in set (0.00 sec)
查看错误日志文件放在哪里
mysql> show variables like 'log_error%';+---------------------+---------------------+| Variable_name | Value|+---------------------+---------------------+| log_error | /var/log/mysqld.log || log_error_verbosity | 3 |+---------------------+---------------------+2 rows in set (0.00 sec)
也可以查看f的log-error属性
新建表
CREATE TABLE t1 (id int(11) NOT NULL,a int(11) DEFAULT NULL,b int(11) DEFAULT NULL,PRIMARY KEY (id),KEY a (a)) ENGINE=InnoDB;insert into t1(id,a,b)values(5,5,5),(10,10,10);
构造死锁情况
使用show engine innodb status\G;查看
找到LATEST DETECTED DEADLOCK
------------------------LATEST DETECTED DEADLOCK-------------------------03-10 14:57:50 0x7f5c285a3700*** (1) TRANSACTION:TRANSACTION 1399, ACTIVE 20 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 36, OS thread handle 140033790449408, query id 647 localhost root updatingupdate t1 set b = b + 222 where id = 5*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 25 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 1399 lock_mode X locks rec but not gap waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 80000005; asc;;1: len 6; hex 000000000576; ascv;;2: len 7; hex 4c000001580110; asc L X ;;3: len 4; hex 80000005; asc;;4: len 4; hex 8000008a; asc;;*** (2) TRANSACTION:TRANSACTION 1398, ACTIVE 34 sec starting index readmysql tables in use 1, locked 13 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 35, OS thread handle 140033790719744, query id 648 localhost root updatingupdate t1 set b = b + 111 where id = 10*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 25 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 1398 lock_mode X locks rec but not gapRecord lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 80000005; asc;;1: len 6; hex 000000000576; ascv;;2: len 7; hex 4c000001580110; asc L X ;;3: len 4; hex 80000005; asc;;4: len 4; hex 8000008a; asc;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 25 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 1398 lock_mode X locks rec but not gap waitingRecord lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 8000000a; asc;;1: len 6; hex 000000000577; ascw;;2: len 7; hex 4d000001590110; asc M Y ;;3: len 4; hex 8000000a; asc;;4: len 4; hex 80000107; asc;;*** WE ROLL BACK TRANSACTION (2)------------TRANSACTIONS------------
也可以通过查看错误日志找到死锁信息，
-03-10T06:57:50.483512Z 35 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.-03-10T06:57:50.484236Z 35 [Note] InnoDB:*** (1) TRANSACTION:TRANSACTION 1399, ACTIVE 20 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 36, OS thread handle 140033790449408, query id 647 localhost root updatingupdate t1 set b = b + 222 where id = 5-03-10T06:57:50.484283Z 35 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 25 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 1399 lock_mode X locks rec but not gap waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 80000005; asc;;1: len 6; hex 000000000576; ascv;;2: len 7; hex 4c000001580110; asc L X ;;3: len 4; hex 80000005; asc;;4: len 4; hex 8000008a; asc;;-03-10T06:57:50.484432Z 35 [Note] InnoDB: *** (2) TRANSACTION:TRANSACTION 1398, ACTIVE 34 sec starting index readmysql tables in use 1, locked 13 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 35, OS thread handle 140033790719744, query id 648 localhost root updatingupdate t1 set b = b + 111 where id = 10-03-10T06:57:50.484455Z 35 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 25 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 1398 lock_mode X locks rec but not gapRecord lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 80000005; asc;;1: len 6; hex 000000000576; ascv;;2: len 7; hex 4c000001580110; asc L X ;;3: len 4; hex 80000005; asc;;4: len 4; hex 8000008a; asc;;-03-10T06:57:50.484600Z 35 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 25 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 1398 lock_mode X locks rec but not gap waitingRecord lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 8000000a; asc;;1: len 6; hex 000000000577; ascw;;2: len 7; hex 4d000001590110; asc M Y ;;3: len 4; hex 8000000a; asc;;4: len 4; hex 80000107; asc;;-03-10T06:57:50.484737Z 35 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)
