On Sep 30, 6:52 pm, Tim W <[EMAIL PROTECTED]> wrote:
> 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
> 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
