For whatever reason I keep getting hundreds of locked tables on a certain query. The MYSQL server has to get restarted every couple of hours. We are trying to solve the problem. Is there any way, or a setting, to either auto kill the query after a certain amount of time so it doesn't stay locked for such a very long time, eventually eating all the ram???
Someone else can probably help you with the locking issue but I'd also recommend looking at the query and the indexes. Can you post it?
The same query, appears to be updating articles views. | 2983175 | boxing_site | 10.78.3.3:53437 | boxing_forum | Query | 148 | Locked | UPDATE `boxing_forum`.`nnet_articles` SET `nnet_views`=`nnet_views`+20 WHERE `nnet_aid`='68189' | | 2983177 | boxing_site | 10.78.3.4:41447 | boxing_forum | Query | 176 | Locked | UPDATE `boxing_forum`.`nnet_articles` SET `nnet_views`=`nnet_views`+20 WHERE `nnet_aid`='68186' | | 2983180 | boxing_site | 10.78.3.4:41455 | boxing_forum | Query | 175 | Locked | UPDATE `boxing_forum`.`nnet_articles` SET `nnet_views`=`nnet_views`+20 WHERE `nnet_aid`='68116' | | 2983181 | boxing_site | 10.78.3.3:53460 | boxing_forum | Query | 171 |
I'm guessing you don't know the code very well? Have you got an index on nnet_aid? Is nnet_aid a unique value or will there be lots of rows with the same nnet_aid value? Adding 20 each time seems odd. I use cakePHP and it has the ability to set counters automatically but the increments are 1. Oh, and have you heard about this book - it's a bit like the dad in my greek wedding - tell me a word and I'll tell you how it has it's root in boxing
nnet_aid has many values. It adds 20 each time because when it hit the server with every hit, it was killing the site and really slowing it down. So we set a caching system where it doesn't hit the server until 20 cached views are reached per each individual article. I have not heard about that book.