Did you notice the indexation time on some of your indexes? It varies from seconds to mostly hours. Depending on how you configured your cronjobs, your store might be busy indexing all day, continously. This could be your problem as it's not able to finish the indexations or at least keep up with it. Having a locked index at all times could cause some trouble and is known for these type of issues.
I have to make some assumtpions here, correct me if needeed. Specify some more information on your setup if you think this might be related to your problem. I have been working on a project that should help developers clean out their core_url_rewrite
table because it grows substantially over time in certain scenario's. I think yours is having this problem too since it ran for almost 3 hours, and the issue you describe could be related to it. I see similair things at test cases.
Is your problem specific to just the mass_action
event object? or does it happen with the other event types as well? (save, delete, reindex etc. (Mage_Index_Model_Event)). If not, I would say it's related to the indexes not being indexed properly. Given the fact there could be locks on tables, that are required for processing, i'm not sure of this. You could easily check for active locks using something like:
$indexes = Mage::getSingleton('index/indexer')->getProcessesCollection()->load();
foreach($indexes as $index){
if($index->isLocked(){
echo "Index" . $index->getIndexerCode() . " with state " . $index->getStatus() . " is locked since " . $index->getStartedAt() . "!";
}
}
Or use my gist, don't forget to remove it when your done. It's not for production usage.
One-page index & lock status overview
So i think that when you fix your index times, your problem might go away and the store could run way more smoothly. In the case of the core_url_rewrite
table, the overhead is generated by Magento itself in an effort to have unique URL's but ends up duplicating them. This has complications on the SEO and performance side. The solution to this problem is making the URL's unique and clearing out all the overhead, without damaging your SEO scores. When they're clean, you'll notice a big difference in index times. Some of my cases started generating sitemaps again after months.
Cleaning it can be tricky but the magerun bundle I put together from scripts I used can help you with at least the rewrite table. Currently it's a proof of concept, so make sure to have backups. When it's proven something useful I will rebuild it.
Magerun bundle with commands for cleaning core_url_rewrite
As for the other tables, I have to assume that there's something similar causing overhead since i have no other info from which i can relate an issue. Maybe you could add some more info on things like the size of your catalog, server specs, store scope configurations, they are all related to your index performance. You might also want to check your table to make sure it's not missing constraints etc.
Magento DB Repair
There's one stack post containing a great collection of information on Magento's indexes, just in case you haven't seen it already.
Stack post on indexes
I hope this is of any value to you, good luck
Best Answer
Need to set permission to var/cache folder
For immediate solution(Not recommended)