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.

1 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

innodb_print_all_deadlocks = 1


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;


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 !!!

  • We are running 5.5.34. Are there any options for our version?
    – gwgeller
    Commented Dec 31, 2014 at 18:49
  • My mistake. It is in 5.5 dev.mysql.com/doc/refman/5.5/en/…. I'll update my answer. Commented Dec 31, 2014 at 19:19
  • Works as advertised, thanks! I changed the setting using sql so I didn't have to restart. Coincidentally a couple of the less serious deadlocks occurred and showed up in the error log.
    – gwgeller
    Commented Jan 2, 2015 at 17:01
  • Setting the value at runtime doesn't work for me, it writes nothing in the error log.
    – lapo
    Commented Dec 19, 2016 at 13:26
  • 1
    @Jordan AWS never gives out SUPER and I mentioned this 6 years ago : dba.stackexchange.com/questions/34525/…. They use triggers to make sure you cannot hack mysql.user and run FLUSH PRIVILEGES;. Commented Sep 18, 2019 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.