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.

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.

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.

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.