13

In my photos class I have this association.

belongs_to :user, :touch => true

One day I got this exception.

A ActiveRecord::StatementInvalid occurred in photos#update:

 Mysql::Error: Deadlock found when trying to get lock; try restarting transaction:
 UPDATE `users` SET `updated_at` = '2011-09-20 14:17:44' WHERE `users`.`id` = 6832
 production/ruby/1.8/gems/activerecord-3.0.10/lib/active_record/connection_adapters/abstract_adapter.rb:207:in `log'

What should I do to prevent future exceptions like this from occurring? I would like the update statement shown in the error to not use locking if possible. I don't think using optimistic locking will work in this case because optimistic locking would probably raise an ActiveRecord::StaleObjectError instead.

Eric Coulthard
  • 500
  • 6
  • 20

1 Answers1

10

That's an issue I stumbled upon myself, too.

Short answer: There is no easy way around this problem. All the touches are wrapped in the same transaction, hence the deadlock.

Long answer: I am guessing you need touch objects to invalidate some sort of (dependent) caches. The usually recommended use of touch only works for a limited amount of "relationships". E.g. invalidating the article when the comment is being updated.

My solution was the asynchronous collection (using a sidekiq job) of DB Objects that need to be invalidated. I wrote my own control logic for it that defines what (other) objects need to be invalidated when an object changed. E.g. comment ==> article.

This way we had a way more verbose way invalidating dependent objects. Plus I invalidated using an Model.update_all which was way faster then the "touch chain". It solved our deadlock problems (and added verbosity and performance to our cache invalidation).

Extra tip: Don't use updated_at. It's highly debatable if a DB object really changed because another object changed. Overwriting the cache_key model lets you easily define a custom cache key like "#{id}-#{valid_from}". valid_from could be a timestamp you define on your models (and that you use instead of updated_at).

Michael Schäfermeyer
  • 2,794
  • 1
  • 16
  • 8
  • Thanks for the answer. It's 2 years later and I am not entirely sure why I was using touch. Invalidating the cache seems like a good reason. I now use sweepers to expire cache entries. http://guides.rubyonrails.org/caching_with_rails.html#sweepers. I don't use touch anywhere anymore. – Eric Coulthard Jun 18 '13 at 14:49
  • I like the idea of valid_from, but I think you need to add the class name into the cache_key as well, eg [class]/[id]-[timestamp] as per http://signalvnoise.com/posts/3113-how-key-based-cache-expiration-works – iheggie Apr 15 '14 at 07:46