mperham / deadlock_retry Goto Github PK

We recently found that we were dropping parts of transactions on the floor. The symptom was this error message

Deadlock detected on retry 1, restarting transaction
Cannot log innodb status: Mysql::Error: Access denied; you need the PROCESS privilege for this operation: show innodb status

Whilst the error is rescued it would still mess up the transaction and, more importantly, would do so silently.

We fixed it by introducing a config option which controlled whether log_innodb_status was called or not (it's off by default) - a better solution may be to check for privileges somehow but it looks like that's not trivial and may introduce performance problems.

Title says it all.

From http://dev.mysql.com/doc/refman/5.1/en/show-engine.html
"Older (and now deprecated) synonyms are SHOW INNODB STATUS for SHOW ENGINE INNODB STATUS and SHOW MUTEX STATUS for SHOW ENGINE INNODB MUTEX. SHOW INNODB STATUS and SHOW MUTEX STATUS are removed in MySQL 5.5."

https://github.com/qertoip/transaction_retry/

We're trying it on our Rails 4.2.11 app right now and it appears to work great. It has completely resolved our Deadlock issues we've been seeing during intensive, concurrent imports and doesn't seem to be causing any other issues as far as we can tell.

If a deadlock occurs in an after commit hook for a record deadlock_retry will retry the commit but issues an "UPDATE" instead of an "INSERT" statement, resulting in the record never being saved even though calling #save returns true. This is easily demonstrated with the following code:

require 'deadlock_retry'

class MyTest < ActiveRecord::Base

  after_create :deadlock_error
  after_commit :puts_after_commit, :on => :create

  def deadlock_error
    unless @errored
      @errored = true
      raise ActiveRecord::StatementInvalid.new("Deadlock found when trying to get lock")
    end
    return
  end

  def puts_after_commit
    puts "After Commit"
  end
end

my_test = MyTest.new(:name => "Testing")

if my_test.save
  MyTest.find(my_test.id)
end

This will have the following output:

> my_test = MyTest.new(:name => "Testing")
=> #<MyTest id: nil, name: "Testing"> 
> if my_test.save
?>     MyTest.find(my_test.id)
?>   end

After Commit
ActiveRecord::RecordNotFound: Couldn't find MyTest with ID=1
from /Users/dimas/.rvm/gems/ree-1.8.7-2011.03/gems/activerecord-3.0.11/lib/active_record/relation/finder_methods.rb:306:in `find_one'
from /Users/dimas/.rvm/gems/ree-1.8.7-2011.03/gems/activerecord-3.0.11/lib/active_record/relation/finder_methods.rb:291:in `find_with_ids'
from /Users/dimas/.rvm/gems/ree-1.8.7-2011.03/gems/activerecord-3.0.11/lib/active_record/relation/finder_methods.rb:107:in `find'
from /Users/dimas/.rvm/gems/ree-1.8.7-2011.03/gems/activerecord-3.0.11/lib/active_record/base.rb:444:in `__send__'
from /Users/dimas/.rvm/gems/ree-1.8.7-2011.03/gems/activerecord-3.0.11/lib/active_record/base.rb:444:in `find'
from (irb):22

And if you look at the log you'll see:

SQL (0.1ms)  BEGIN
SQL (1.9ms)  describe `my_tests`
AREL (0.2ms)  INSERT INTO `my_tests` (`name`) VALUES ('Testing')
SQL (0.5ms)  ROLLBACK
Deadlock detected on retry 1, restarting transaction
SQL (3.3ms)  BEGIN
AREL (0.3ms)  UPDATE `my_tests` SET `name` = 'Testing' WHERE `my_tests`.`id` = 1
SQL (0.1ms)  COMMIT
MyTest Load (0.2ms)  SELECT `my_tests`.* FROM `my_tests` WHERE `my_tests`.`id` = 1 LIMIT 1

It looks like deadlock_retry only wraps transactions, but "Lock wait timeout exceeded" errors can also be raised from update commands, which are not always wrapped in a transaction. ActiveRecord::Base.update_counters is one such case.

Any chance of a new release of the gem with the latest edits in?