Quite often we get the following error in Magento when we try to make a change to one of our Categories.
SQLSTATE[HY000]  Lock wait timeout exceeded; try restarting transaction, query was: UPDATE
created_at= '2015-10-30 11:24:57',
updated_at= '2016-11-27 07:33:07',
children_count= ? WHERE (
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.