Mysql – Disable InnoDB deadlock detection

There are situations where InnoDB deadlock would come up when you least expect it. For example, SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three past questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

• Reasons for occasionally slow queries?
• Trouble deciphering a deadlock in an innodb status log
• Will these two queries result in a deadlock if executed in sequence?

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that even SELECT queries can lock InnoDB data. Although autocommit could be enabled (rendering each query its own transaction), a single SQL statement can still be victimized by deadlocks.

You also have special cases of SELECT where you can lock specific rows on demand.

Based on InnoDB Deadlocking link, the sequences of event to cause this situation could theoretically be as follows:

• Your SQL UPDATEs a single row but generates an error
• The UPDATE causes a rollback of the one row
• The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk instead of just documenting and hopong you google for it. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)

This is definitely a head-scratcher, but here's one possibility, especially if you're running MySQL 5.1 or earlier. MySQL Bug #37346 addresses an issue where there are locks at two different layers that are (at least partially) unaware of each other's existence... an uncommitted transaction is holding locks inside the storage engine (invisible to MySQL's core), while the ALTER TABLE is holding a different kind of lock, outside the storage engine (invisible to InnoDB).

In the bug report, when the ALTER TABLE is almost done, it bumps into the inner locks and ultimately times out with the message you see.

This is referenced as "fixed" in the release notes for 5.5.3 but I didn't find it mentioned in 5.1.x, which suggests to me that it might still exist there. It seems like a long shot but it did seem worth mentioning.

Until I found the above, my initial thoughts had to do with the table that is referenced by fk_foo_1... I was going to speculate that InnoDB might be trying something unnecessary related to validation of the foreign key constraint (unnecessary, since the constraint is about to disappear)... or that an uncommitted update or delete on the referenced table might be setting locks on the table you're trying to alter... an update or delete "over there," needing to be validated or trying to cascade "over here," while the ALTER TABLE is underway, and not waiting as it should for the lock held by the ALTER TABLE.