Mysql tuning plus catching ActiveRecord::StatementInvalid errors..

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

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