10

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.

13

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

  • We are running 5.5.34. Are there any options for our version? – gwgeller Dec 31 '14 at 18:49
  • My mistake. It is in 5.5 dev.mysql.com/doc/refman/5.5/en/…. I'll update my answer. – RolandoMySQLDBA Dec 31 '14 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 Jan 2 '15 at 17:01
  • Setting the value at runtime doesn't work for me, it writes nothing in the error log. – lapo Dec 19 '16 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;. – RolandoMySQLDBA Sep 18 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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