Bug #55859 MySQL and InnoDB locks are taken in different orders in different statements
Submitted: 9 Aug 2010 20:21 Modified: 27 Dec 2010 11:11
Reporter: Ben Krug Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[9 Aug 2010 20:21] Ben Krug
Description:
Some statements on InnoDB tables take MySQL locks first, then InnoDB locks.
Others ("LOCK TABLES WRITE") take the InnoDB locks first, then the MySQL
locks.

This can lead to deadlocks, which lead to timeouts.  (Since
MySQL doesn't detect deadlocks.)

This can be a source of deadlocks and timeouts that cannot be
programmatically avoided by application developers.

Request a flag to make this configurable, so that users can
change the behavior for LOCK TABLES if desired, to avoid this.
(This could actually be seen as a bug, but as a fix might
affect performance, making it configurable may be preferable.)

Some further details:

http://bugs.mysql.com/bug.php?id=4672 says:

What happens in ... LOCK TABLES ... WRITE is that:

1) MySQL calls ha_innobase::external_lock() to set an internal InnoDB table lock;
2) MySQL sets its own table lock;

So innodb X lock is obtained before mysql WRITE lock on table. Even in the code, I see external_lock() called before thr_multi_lock().

Although during all statements (i.e even inserts, deletes and updates) ha_innobase::external_lock() is called before thr_multi_lock(), the former skips getting IX lock for DML statements. The IX lock is obtained later in innodb, for example, during row_insert_for_mysql for insert.

As a result we have DML statements first getting mysql lock and then innodb lock while the “lock tables” (with auto-commit off) first gets innodb lock and then mysql lock and so they run into deadlock.

How to repeat:
check the code

Suggested fix:
Make this configurable.  Customers can program to avoid deadlocks in their applications, but as this is, it cannot be programmatically avoided.
[9 Aug 2010 20:53] Nagavamsi Ponnekanti
In multi-statement xacts there could be innodb locks held from previous statements. As a result, enforcing the order "innodb locks before mysql locks" is better than enforcing the order "mysql locks before innodb locks". So it would be good if the proposed configurable option enforces the order "innodb locks before mysql locks". (That probably means the config option would affect DML statements rather than "lock tables" statement.)
[10 Aug 2010 12:49] Konstantin Osipov
Fixed in 5.5 with the new MDL.
[10 Aug 2010 12:50] Konstantin Osipov
The suggested cure is worse than the disease.
[10 Aug 2010 12:55] Konstantin Osipov
What *can* be further improved in 5.5, is that, since we no longer needs the so called "MySQL locks" (i.e. thr_lock table level locks) when working with InnoDB engine, we could never try to acquire them for InnoDB tables.
But this will only lead to fewer mutex acquisitions, and otherwise will have no effect.