MySQL Bugs: #55859: MySQL and InnoDB locks are taken in different orders in different statements

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

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.

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.)

Fixed in 5.5 with the new MDL.

The suggested cure is worse than the disease.

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.