Mysql::Error: Lock wait timeout exceeded; try restarting transaction

This post pertains to MySQL with InnoDB.

With heavy concurrency it is common to see the “Lock wait timeout” error on some database transactions. This is a temporary error that can often be made to go away just by trying the transaction again. Here is a little helper method you can put in a Rails initializer, for instance, to give your model a concise way to retry a few times.

class ActiveRecord::Base

  def self.retry_mysql_error(tries=3, &b)
    count = 0
    begin
      count += 1
      return b.call
    rescue Mysql::Error
      if count < tries
        sleep(0.1 * count)
        retry
      end
      raise
    end
  end

end

Just pass it a block (and an optional number of tries) and it should help to reduce the number of timeouts you see. Be careful not to carry over inadvertently any assumptions across retries.

class Download < ActiveRecord::Base

  def Download.increment_download_count!(filename)
    Download.retry_mysql_error(5) do
      record = Download.find_by_filename(filename)
      record.download_count += 1
      record.save!
    end
  end

end

It also plays nice with RSpec.

describe Download do

  describe "increment_download_count!" do

    it "retries mysql error five times" do
      Download.should_receive(:find_by_filename).exactly(5).times.and_raise(Mysql::Error)
      lambda { Download.increment_download_count!(true) }.should raise_error(Mysql::Error)
    end

  end

end

Of course, this technique should be balanced against other considerations. If it is unable to complete successfully after all the retries, it will re-raise the Mysql::Error. If this happens often, you might need to make deeper changes to your application.