Peter Marklund

Peter Marklund's Home

Mon August 17, 2009
Programming

Rails Counter Cache Updates Leading to MySQL Deadlock

I've gotten a few error messages lately where a plain vanilla ActiveRecord counter cache update (update_counters_without_lock method) has lead to an error being thrown from Mysql - "Mysql::Error: Deadlock found when trying to get lock; try restarting transaction: UPDATE `events` SET `attendees_count` = COALESCE(`attendees_count`, 0) + 1 WHERE (`id` = 1067)".

It seems someone else has tried to report this as a bug but Mysql is saying that it's a feature and is referring to the lock modes documentation. There is some interesting info on deadlocks in InnoDB over at rubyisms. I haven't had time to dig into the theory though. Has anybody else had this issue? What can be done about it (other than switch to PostgreSQL)?

Comments

Peter said over 5 years ago:

hello

--------------------------------------------------------------------------------

Jason said over 4 years ago:

I'm having this issue too..

--------------------------------------------------------------------------------

Jintha said over 4 years ago:

I found that there is a gem to handle this in this post http://blog.littleimpact.de/index.php/2008/08/03/mysqlerror-deadlock-found-when-trying-to-get-lock/ . Did not try it myself, but i hope it may be a solution.

--------------------------------------------------------------------------------

Mina said over 3 years ago:

Regarding PostgreSQL, it currently has a similar problem which I've documented at http://mina.naguib.ca/blog/2010/11/22/postgresql-foreign-key-deadlocks.html

The good news is that it's actively being fixed.

--------------------------------------------------------------------------------