Enabling MySQL deadlock logging

Recently I had to help track down a deadlock which happened many hours ago on a database. Unfortunately the first responders to the incident didn’t save the output from MySQLs SHOW ENGINE INNODB STATUS command, so this being quite a busy database it was hard to track down.

However, I stumbled across the MySQL setting innodb_print_all_deadlocks, which when turned on will log all of the deadlocks that happen within MySQL to the mysql error log.

Not only does this allow you to more easily get the actual deadlocks that are happening on your system, it means you can look into the past to also debug deadlocks - a godsend when you have a 24/7 site.