Lock wait timeout exceeded

Question

Quite often we get the following error in Magento when we try to make a change to one of our Categories.

SQLSTATE[HY000] [1205] Lock wait timeout exceeded; try restarting transaction, query was: UPDATE catalog_category_entity SET entity_type_id = ?, attribute_set_id = ?, parent_id = ?, created_at = '2015-10-30 11:24:57', updated_at = '2016-11-27 07:33:07', path = ?, position = ?, level = ?, children_count = ? WHERE (entity_id=250)

It seems to only be occurring after we have moved around or reorganised a category and I can see from System > Index Management that Catalog URL Rewrites is still being processed. Once the indexing is finished we can make changes to categories, but this is a recurring problem which really slows things down.

I've checked Catalog > URL Rewrite Management and we have almost 500,000 rewrites, we have 4 store views, 350,000 plus of them are custom although we obviously did not create them all. I believe this is what is slowing everything down.

I'm not sure what to do here, if I truncate the core_url_rewrite database table with TRUNCATE TABLE 'core_url_rewrite'; and re-index Catalog URL Rewrites in System > Index Management will that fix it? Is there anything I should be aware of before I cary try this? I'll do a table & db back up first of course and test it in a local environment.

Answer 1

The error is because the transaction is reaching its timeout period. If you did not set this in your my.cnf the default is 50 seconds I would highly recommend you look at your sever settings to ensure that they are optimized for your database using tools such as MySQLTuner

I have the innodb_lock_wait_timeout set to 7200 (2 hours) in a store with 500k products with 2 store views.

Example to add or change in my.cnf

innodb_lock_wait_timeout = 7200

DO NOT TRUNCATE.... yet

When you "moved around or reorganised a category" Magento will keep the references to these original links (hence the massive amount of links and 4 store views as well) and redirect them for visitors / bots / search engines that have book marked or indexed these pages.This can negatively affect your SEO, when you truncate they will receive a error 404 message. My personal recommendation is BEFORE doing major changes to the structure, ensure that nothing of the sort has been done for at least a month, then truncate then do your restructuring. This will cut down enormously on the size of your URL Rewrites

Since you've already re-arranged your categories, the best thing to do now is wait for a month to give visitors / bots / search engines the time to update the links --- then purge.

Also checkout:Magento core_url_rewrite table excessively large