Log MySQL Deadlocks to the Error Log

This article provides a straightforward guide on how to configure MySQL to automatically write deadlock information directly to the system error log. By enabling this configuration, database administrators can easily monitor and troubleshoot transactional conflicts without having to manually query engine status tables after a deadlock occurs.

To configure MySQL to log all InnoDB deadlocks to the system error log, you must enable the innodb_print_all_deadlocks system variable. By default, this setting is disabled. When enabled, details about both the lock waiter and the lock holder are written to the error log.

Enable Logging Temporarily (Without Restarting)

You can enable deadlock logging immediately on a running MySQL server by setting the global variable. This change takes effect instantly but will be reverted if the MySQL service restarts.

Run the following SQL command as an administrative user:

SET GLOBAL innodb_print_all_deadlocks = ON;

To verify that the setting has been applied successfully, run:

SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

The output should show the value as ON.

Enable Logging Permanently

To ensure that deadlock logging remains enabled after a server reboot or a MySQL service restart, you must add the configuration to your MySQL option file (typically my.cnf on Linux or my.ini on Windows).

  1. Open your MySQL configuration file in a text editor.
  2. Locate the [mysqld] section.
  3. Add the following line under the [mysqld] header:
innodb_print_all_deadlocks = 1
  1. Save and close the file.
  2. If you did not apply the change dynamically using the SET GLOBAL command, restart the MySQL service to apply the changes:
# On systemd-based Linux distributions
sudo systemctl restart mysql

Viewing the Deadlock Logs

Once enabled, any future deadlocks will be recorded in the standard MySQL error log. The location of this log file is determined by the log_error system variable. You can find the path to your error log by running:

SHOW VARIABLES LIKE 'log_error';

Open this log file using your preferred text viewer or command-line tool (such as tail -f on Linux) to monitor and analyze deadlock trace details as they occur.