Mysql "lock wait timeout" tuning

We’re seeing these errors intermittently:

Mysql::Error: Lock wait timeout exceeded; try restarting transaction:
UPDATE chairs SET published_at = NULL, updated_at = ‘2008-07-30
15:31:05’ WHERE id = 2147
[RAILS_ROOT]/vendor/rails/activerecord/lib/active_record/
connection_adapters/abstract_adapter.rb:147:in `log’

The root issue is probably slowness in the Rails app, which causes
some long transactions. We’re trying to find some way to tune around
this while the code is refactored; does anyone know a way to optimize
mysql for this issue?

On Jul 30, 4:42 pm, Aramis K. [email protected] wrote:

The root issue is probably slowness in the Rails app, which causes
some long transactions. We’re trying to find some way to tune around
this while the code is refactored; does anyone know a way to optimize
mysql for this issue?

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 (GitHub - rails/deadlock_retry: NOTICE: official repository moved to https://github.com/heaps/deadlock_retry)
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

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

Thanks&Regards,
Kiran.

On Jul 31, 4:13 pm, Frederick C. [email protected]

It was indeed an unindexed field – thanks Fred!