Peter Marklund

Peter Marklund's Home

2009-08-17

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)?

4 comment(s)

Comments

Mina said 2010-11-23 04:14:

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.
--------------------------------------------------------------------------------

Jintha said 2009-12-18 13:51:

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.
--------------------------------------------------------------------------------

Jason said 2009-11-26 06:57:

I'm having this issue too..
--------------------------------------------------------------------------------

Peter said 2009-09-05 15:23:

hello
--------------------------------------------------------------------------------