Mysql "lock wait timeout" tuning

rubyonrails-talk
1

We're seeing these errors intermittently:

2

Well first off make sure that mysql isn't locking rows it doesn't need to. Mysql locks index ranges, so if you update something (or do a locking select ) and the indexed used is not very discriminating (or there isn't an index at all) then you'll be locking a whole bunch of rows that don't need to be locked. Fundamentally though, this happens because something you are doing is taking too long. Pretty much the only thing you can do at the mysql (as far as I know) is change the timeout after which mysql throws that error.

There is a plugin (http://github.com/rails/deadlock_retry/tree/master) that automatically retries for you. (we've used this although sometimes, for reasons we've not yet fully investigated, the retries also deadlock more often than you'd expect them to)

Fred

Fred

3

Try to add this line in environment.rb ActiveRecord::Base.verification_timeout = 570

Thanks&Regards, Kiran.

4

It was indeed an unindexed field -- thanks Fred!