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
SETentity_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.