4

When i am reindexing Catalog Search Index from backend and at the same time if my front end users are searching something with search in mysite then i getting errors such as

Error: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

please can anyone tell me how to overcome this issue.

yantrakaar
  • 374
  • 3
  • 15
  • Did check here first? http://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded – Dushyant Joshi Mar 14 '14 at 10:06
  • Yes , I checked all those posts . But this is more related to magento . Just a logic through which I can stop this conflict . I cannot disable search and reindexing in magento .Just need a better solution . – yantrakaar Mar 14 '14 at 10:10

3 Answers3

4

This is a Magento bug, which has been around since Magento 1.7.

When reindexing the catalogsearch_fulltext index, a transaction is started which does the following, for each store in succession:

  1. Rebuild the catalogsearch_fulltext table
  2. Reset search results: update the catalogsearch_query table (set is_processed to 0) and clear the catalogsearch_result table

From the moment when the catalogsearch_query query is executed, all searches in the frontend should wait until the complete indexing process has finished (they also cause writes to the catalogsearch_query and catalogsearch_result tables). For a large catalog with multiple store views, this can take quite a while, resulting in a "lock wait timeout exceeded" error.

I have reported this to Magento via the Bug tracker: http://www.magentocommerce.com/bug-tracking/issue/index/id/933

I think it shouldn't be necessary to store search results in the database; instead, they should be stored in the registry, such that they can be referenced multiple times during a single request. Then it isn't necessary anymore to reset search results during reindexing. Also, the need for a transaction disappears.

I did not implement this solution, as we mostly use ElasticSearch nowadays.

Aad Mathijssen
  • 630
  • 9
  • 22
0

Yes.. this is happen because 1 row of InnonDB DB tables are updated at the same time..

Bhavesh Gadoya
  • 9
  • 3
0

Try to Reindex your Catalog Search Index using shell (SSH).

Try to navigate shell folder from Magento root directory. then execute the below command.

// To check the indexer status.
php -f indexer.php status 

// To reindex the indexer data.
php -f indexer.php reindex

// To check the any help in this indexer process.
php -f indexer.php help

Try this using SSH, it will help you to reindex all the data.

Maniprakash Chinnasamy
  • 2,778
  • 29
  • 55
  • I encountered these problems in several webshops. It happens when you reindex the catalog search index and perform a search in the frontend at the same time. It doesnt matter in which way you fire off the reindexing process – RichardBernards Mar 21 '14 at 15:18
  • And it looks that it still happen on Enterprise 1.14.0.1 – Ricardo Martins Jul 10 '14 at 18:49