Mysql – How long can Deadlock info hold in innodb status page

deadlockinnodbMySQL

When I run SHOW ENGINE INNODB STATUS\G, I can see this timestamp

------------------------
LATEST DETECTED DEADLOCK
------------------------
121118 17:14:31

Questions

  • Any idea how long Deadlock details/info holds in innodb status page ?
  • After how long does it refreshes or clears the recent deadlock info?
  • Any way I can force to get the current status of Deadlocks?

Regards,
Mannoj

Best Answer

The first thing you must look for is the setting for innodb_lock_wait_timeout.

Suppose you run this SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; and get

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql>

At the very least, you should rightly expect a newly detected deadlock on a busy DB server every 50 seconds.

InnoDB cannot detect deadlock under two conditions:

  1. LOCK TABLES is explicit executed
  2. A lock set by a storage engine other than InnoDB is involved

These two conditions are usually self-inflicted and relatively easy to find. Setting the innodb_lock_wait_timeout would be the usual workaround.

However, there is a one boobytrap you need to be aware of. It is possible for SELECT queries to create row-level locks. Many developers are not aware of this quirky behavior. In fact, according to the MySQL Documentation:

If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

I discussed this back on Aug 08, 2011 : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?

RECOMMENDATIONS

  • Spend time searching for SELECTs that do JOINs of InnoDB and MyISAM tables (hopefully you have none). If any, please convert the MyISAM tables to InnoDB so that InnoDB locks can be better monitored and better managed.
  • Search carefully for deadly embrace situations
  • Avoid doing mysqldumps with --lock-tables and --single-transaction mixed.

With these recommendations, you can hopefully assure that the deadlock status in SHOW ENGINE INNODB STATUS\G should be updated with a degree of regularity.

Related Question