Mysql tuning plus catching ActiveRecord::StatementInvalid errors..

rubyonrails-talk
1

Recently our Rails app has been choking every once and a while on updates to the "users" table. The error normally appears like so:

A ActiveRecord::StatementInvalid occurred in activity_responses#create:

  Mysql::Error: Lock wait timeout exceeded; try restarting transaction: UPDATE `users` SET `created_at` = '2007-03-02 23:26:33', `home_phone` = NULL, ....

The users table currently has about 350,000 rows and consists of about 50 fields. The error only occurs on the users table yet there are 3 or 4 other tables with much more rows that work just fine (although the updating is not as often as the users table). I have googled and googled this error and haven't come up with much. I have tweaked and retweaked mysql settings to no avail and also tried tweaking a few rails variables. I think it may have to do with certain queries that are not indexed properly, but I can't seem to find anything that would point me to where or what to fix. Any ideas on where to go next to track down this issue?

Onto the 2nd part.. While I hate that some users get this error, it is happening once or twice a day. Is there anyway to catch this error so I can send them a specific error message on the subject and let them know it is ok to try again in a minute or two? (Rather then the standard error message)

Thanks.. -tim

2

Recently our Rails app has been choking every once and a while on updates to the "users" table. The error normally appears like so:

A ActiveRecord::StatementInvalid occurred in activity_responses#create:

Mysql::Error: Lock wait timeout exceeded; try restarting transaction: UPDATE `users` SET `created_at` = '2007-03-02 23:26:33', `home_phone` = NULL, ....

The users table currently has about 350,000 rows and consists of about 50 fields. The error only occurs on the users table yet there are 3 or 4 other tables with much more rows that work just fine (although the updating is not as often as the users table). I have googled and googled this error and haven't come up with much. I have tweaked and retweaked mysql settings to no avail and also tried tweaking a few rails variables. I think it may have to do with certain queries that are not indexed properly, but I can't seem to find anything that would point me to where or what to fix. Any ideas on where to go next to track down this issue?

Basically that error means that one transaction was waiting to get a lock on some rows, waited a while and then gave up. InnoDB does row level locking, and locks index ranges, so a missing (or inefficient (ie not very precise)) index can cause it to lock far more rows than it needs to, making this error much more likely. There is also a mysql setting that controls how long it will wait before giving up. THere is also a plugin (deadlock_retry) that will retry failed writes like this (for us at least it has not been a panacea). Looking at the source for this plugin should also give you some clues about how to deal with the second part of your problem.

Fred