Eric Cavalcanti CajuCLC

Here is how we can see if we have MySQL locks due to indexing:

Display locks:

SELECT IS_USED_LOCK('magento.reindex_full');

Releasing locks:

SELECT RELEASE_LOCK('magento.reindex_full');

Sometimes you may have a run away reindex process that aborted due to a MySQL connection error of some sorts. It may be useful to use the following MySQL commands to aid in debugging.

Determining if a lock exists

Magento Enterprise labels the reindex lock via the constant REINDEX_FULL_LOCK in app/code/core/Enterprise/Index/Model/Observer.php

SELECT IS_FREE_LOCK('mydatabase_name.reindex_full')

Returns 1 is specified lock is free and can be acquired, 0 if it’s in use, NULL if an error occurs.

Determing the thread that is holding the lock

Magento Code Snippets

Download extension manually using mage

./mage config-set preferred_state stable
./mage clear-cache
./mage sync
./mage download community Module_Name

	-- When products are added/removed from a category this table stores all the relation data
	select * from catalog_category_product where product_id = '24526';

	-- The index that is used for presenting products in a category on the frontend
	select * from catalog_category_product_index where product_id = '24526';

	-- The change log table used for Magento EE partial index functionality
	select * from catalog_category_product_index_cl where product_id = '24526' and version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'catalog_category_product_index_cl');