How to check number of innodb deadlocks that have occurred

Question: How to Monitor Number of InnoDB Deadlocks?

To log all deadlock information to the mysql error log, you can enable the global variable “innodb_print_all_deadlocks”. However, that is not useful for monitoring via a program or script. So, we’ll use standard SQL queries instead.

To see a once-off answer, you can run this query:

mysql> select * from information_schema.innodb_metrics where name='lock_deadlocks'\G
*************************** 1. row ***************************
  NAME: lock_deadlocks
  SUBSYSTEM: lock
  COUNT: 1168
  MAX_COUNT: 1168
  MIN_COUNT: NULL
  AVG_COUNT: 4.728744939271255
  COUNT_RESET: 1168
  MAX_COUNT_RESET: 1168
  MIN_COUNT_RESET: NULL
  AVG_COUNT_RESET: NULL
  TIME_ENABLED: 2014-09-29 12:04:46
  TIME_DISABLED: NULL
  TIME_ELAPSED: 247
  TIME_RESET: NULL
  STATUS: enabled
  TYPE: counter
  COMMENT: Number of deadlocks
1 row in set (0.00 sec)

We could also create some stored routine that checks the INNODB_METRICS table every few seconds and prints the value when it changed. For example:

drop procedure if exists `track_metric`;
delimiter $
create procedure `track_metric`(`p_name` varchar(193) charset utf8)
begin
  declare `v_count` bigint unsigned default 0;
  declare `v_old_count` bigint unsigned default 0;
  set global innodb_monitor_enable=`p_name`;
  repeat
    select `COUNT` from `information_schema`.`INNODB_METRICS` where `NAME`=p_name into v_count;
    if `v_old_count` <> `v_count` then select now(),`p_name` as `metric`,`v_count` as `count`; end if;
    set `v_old_count`:=`v_count`;
    do sleep(1);
  until 1=2 end repeat;
end $
delimiter ;
call `track_metric`('lock_deadlocks');

The output may look like this, changing only when new deadlocks occur:

mysql> call `track_metric`('lock_deadlocks');
+---------------------+----------------+-------+
| now()               | metric         | count |
+---------------------+----------------+-------+
| 2014-09-29 15:32:41 | lock_deadlocks |   546 |
+---------------------+----------------+-------+
1 row in set (0.00 sec)

+---------------------+----------------+-------+
| now()               | metric         | count |
+---------------------+----------------+-------+
| 2014-09-29 15:32:44 | lock_deadlocks |   555 |
+---------------------+----------------+-------+
1 row in set (3.01 sec)

+---------------------+----------------+-------+
| now()               | metric         | count |
+---------------------+----------------+-------+
| 2014-09-29 15:32:51 | lock_deadlocks |   602 |
+---------------------+----------------+-------+
1 row in set (10.02 sec)

+---------------------+----------------+-------+
| now()               | metric         | count |
+---------------------+----------------+-------+
| 2014-09-29 15:34:31 | lock_deadlocks |   607 |
+---------------------+----------------+-------+
1 row in set (1 min 50.09 sec)