View last several innodb deadlocks

Question

I see that I can view the latest deadlock in mysql/innodb, but is there a way to view past deadlocks? We have two deadlock issues, one being important and the other not. The less important deadlock happens several times a day so it becomes the "latest" deadlock.

Answer 1

There is a setting that was introduced in MySQL 5.5.30 : innodb_print_all_deadlocks

When this option is enabled, information about all deadlocks in InnoDB user transactions is recorded in the mysqld error log. Otherwise, you see information about only the last deadlock, using the SHOW ENGINE INNODB STATUS command. An occasional InnoDB deadlock is not necessarily an issue, because InnoDB detects the condition immediately, and rolls back one of the transactions automatically. You might use this option to troubleshoot why deadlocks are happening if an application does not have appropriate error-handling logic to detect the rollback and retry its operation. A large number of deadlocks might indicate the need to restructure transactions that issue DML or SELECT ... FOR UPDATE statements for multiple tables, so that each transaction accesses the tables in the same order, thus avoiding the deadlock condition.

Just add this setting to my.cnf

[mysqld]
innodb_print_all_deadlocks = 1

or

[mysqld]
innodb_print_all_deadlocks = on

You don't have to restart mysql. Just login to mysql and run

mysql> SET GLOBAL innodb_print_all_deadlocks = 1;

or

mysql> SET GLOBAL innodb_print_all_deadlocks = 'ON';

This setting is new to me too.

Give it a Try and tells us all what you think !!!