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.
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.
SELECT IS_USED_LOCK('mydatabase_name.reindex_full')
This function is useful if you need to know which thread is holding a certain lock. It returns the connection id of that thread, or NULL if the specified lock is free.
RELEASE_LOCK('mydatabase_name.reindex_full')
RELEASE_LOCK() should return 1. If it returns 0, that lock has been acquired by someone else, and can’t be release by you. If the specified lock does not currently exist, returns NULL. In both cases, your code is likely to be buggy.
If you find that some indexes are locked for a long time this is the evidence of the lost process which locked the index and probably some tables in database. If the process is lost then
SELECT RELEASE_LOCK('mydatabase_name.reindex_full')
is out of the control. You have to kill the process manually then all related locks will be released. Please follow my instructions bellow to release the index lock:You may also find that some tables are locked because of locked index. Check them too