Anyone who’s built a reasonably complex web site on top of a database has seen database deadlock. MySQL will throw a timeout error after waiting for 30 seconds, thereby ending the deadlock in a brutal but effective manner. Rails has a plugin, known as deadlock_retry, which will catch those errors and simply retry the transaction. There’s a good chance this will work since deadlocks are race conditions but it’s not very effective for finding and solving the underlying problem.
So I’ve forked the code and spruced it up a bit:
- If a deadlock happens, the code will log the output of SHOW INNODB STATUS, which will indicate who was waiting for what. Knowing the tables in contention goes a long way to understanding the problem.
- The code now runs as a gem, not as a plugin. You don’t need to put the code in your application, just add this config.gem reference in config/environment.rb and use
rake gems:installto install it:
config.gem 'mperham-deadlock_retry', :lib => 'deadlock_retry', :source => 'http://gems.github.com'
This is code I wrote for our internal usage of deadlock_retry at FiveRuns. Please let me know if you find this improvement useful.
2 responses so far ↓
1 Matt // Jul 5, 2009 at 2:18 pm
Thanks for sharing this. It works great, I was running into a deadlock issue with my high database use voice broadcasting and predictive dialing application.
2 Matt // Jul 7, 2009 at 3:51 pm
I noticed after installing your gem, my imports work, but under high load the imports take about 5 times as long as they normally do. Are any mysql priorities changed, or anything else of that sort that would slow down my bulk imports when using your deadlock_retry gem?
Leave a Comment