Once I needed to detect Deadlocks and log them, I will describe how to do it:
Let’s see if deadlocks logging is enabled, by default it is OFF:
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';
Let’s enable logging of deadlocks in the error file:
SET GLOBAL innodb_print_all_deadlocks = ON;
Let’s see where the error logs are located, usually it’s /var/log/mysql/error.log:
SHOW VARIABLES LIKE 'log_error';
Deadlocks can also be viewed by running the query:
SHOW ENGINE INNODB STATUS\G;
Since there was a Zabbix agent on the server, I used it to track when the word ‘deadlock’ appeared in the logs (running every three hours, depending on the size of the log file, I don’t recommend running it often):
UserParameter=deadlocks_error,grep -Fr 'deadlock' /var/log/mysql/error.log | wc -l
See also my article:
How to install and configure Zabbix-agent on Ubuntu