How long can Deadlock info hold in innodb status page?

Question

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

Answer 1

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.

Answer 2

SHOW ENGINE INNODB STATUS info survives for several seconds, often about a minute. I have heard of no reliable way to grab the info before it vanishes. Checking every 30 seconds would do a pretty good job.

Deadlock detection is "immediate", and on of the transactions is ROLLBACKd. I don't mean it happens immediately after the BEGIN; rather, it happens as soon as a lock is attempted where that lock would cause a "deadlock".

lock_wait_timeout is not related to deadlocks, but rather to waiting for lock(s) to be freed without deadlocking.

Answer 3

You can only see the latest deadlock in INNODB STATUS.

You may also use the new 5.6 variable: http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks This way you'll be able to log all deadlocks.