How can we help you today?

How to Solve Deadlocks Caused by ALTER TABLE enable/disable keys

Created by: Sophie van Vrijberghe de Coningh

Modified on: Fri, 29 May, 2020 at 1:19 PM

On busy shops, we often see an issue appear that Magento for some reason still hasn’t fixed in the Magento core.

This issue causes massive performance issues and deadlocks on shops that receive lots of requests and have a steady database use.


The Problem

Indexers that extend Mage_Index_Model_Resource_Abstract run an ALTER TABLE ... DISABLE|ENABLE KEYS statement before and after inserting data.

The statement is only supported by MyISAM tables so it is useless for modern Magento installations, but it still requires a table metadata lock even if MyISAM is not used.

The catalogsearch index does still use MyISAM, but it doesn’t extend Mage_Index_Model_Resource_Abstract so it isn’t affected.

As the statement doesn’t actually do anything it should be removed and the lock would be avoided.

We’ve seen many database deadlocks caused by this legacy code and recommend developers to remove it from the code.

It may seem like it wouldn’t have that much impact as the lock should only be for a very short time, but in production we’ve seen large queues of queries ‘Waiting for table metadata lock’ on tables like cataloginventory_stock_status and catalog_product_index_price when the ALTER TABLE statement is blocked by some other very slow transaction.

The Solution

In OpenMage, an LTS fork of the Magento 1 codebase, a pull request is made to resolve this issue by removing the DISABLE/ENABLE KEYS statement. You can find this Pull request in their github repository.

The Patch

A full patch can be found here

We do not recommend hacking the Magento core, but instead copying the changed files to app/code/local.

Sophie is the author of this solution article.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.