ActiveRecord threading issues and resolutions

This post is ActiveRecord 2.2 and MySQL specific

There are a few big issues when you’re dealing ActiveRecord in a mutli-threaded context.

1) The ‘mysql’ gem isn’t thread safe.
You can end up in fun interpreter-wide deadlock situations if you try to use AR concurrently with it. Solution? mysqlplus. (I think you might also be able to use the pure ruby mysql gem (ruby-mysql), but of course that’s slower and really old, I don’t know if it still works.)

gem sources -a http://gems.github.com
sudo gem install oldmoe-mysqlplus

Update: I ran into a deadlock with the Ruby implementation of async_query, so use the C one instead.
Then add this somewhere so that ActiveRecord uses it:

require 'rubygems'
gem 'oldmoe-mysqlplus'
require 'mysqlplus'

class Mysql
  alias_method :query, :c_async_query
end

2) Database connection timeouts.
In mysql it’s the infamous “Server has gone away” error. This happens when a thread opens a connection (with an initial AR query) and then doesn’t make another query within the DB’s timeout value. So the DB disconnects you and on the next query you get an error.

There are two solution to this:
a) You call ActiveRecord::Base.verify_active_connections! periodically, or …
b) You monkeypatch the connection adapter to reconnect on a “Server has gone away” error.

module ActiveRecord::ConnectionAdapters
  class MysqlAdapter
    alias_method :execute_without_retry, :execute
    def execute(*args)
      execute_without_retry(*args)
    rescue ActiveRecord::StatementInvalid
      if $!.message =~ /server has gone away/i
        warn "Server timed out, retrying"
        reconnect!
        retry
      end

      raise
    end
  end
end

verify_active_connections! seems much nicer. But there’s a catch. It can only be used if you can call it at a time when you know all threads will NOT be making any queries over their DB connection. The way it verifies if the connection is active or not is by making a query. So if you have two threads trying to make two queries over the same connection you could get some weird behavior.

Most threaded non-Rails apps will probably NOT have a time when they know all threads are inactive. Meaning you can’t use this solution, you have to go with the monkeypatch.

3) Connection cleanup.
Every thread will get a new connection from the connection pool by default. But when the thread dies, the connection will not be returned to the pool. Meaning you will run out of connections and get errors. There are a few solutions to this:
a) Use a thread pool if this suits your concurrency model.
b1) If it doesn’t, periodically call ActiveRecord::Base.connection_pool.clear_stale_cached_connections!. Don’t worry, this one is thread-safe unlike verify_active_connections!. All it does is return connections which are assigned to dead threads to the pool.
b2) Instead of periodically calling it (from some other thread) you could monkeypatch Thread.new to automatically call it after every thread has finished running.

class << Thread
  alias orig_new new
  def new
    orig_new do
      yield
      t = Thread.current
      Thread.orig_new do
        sleep 0.1  while t.alive?
        ActiveRecord::Base.connection_pool.clear_stale_cached_connections!
      end
    end
  end
end

You have to sleep a little or else the original thread won’t have terminated yet and thus AR won’t clear the connection for it.

Update: I wasn’t thinking totally clearly on this one. If you’re going to monkeypatch Thread.new you might as well just have the connection pool release the connection explicitly:

class << Thread
  alias orig_new new
  def new
    orig_new do
      begin
        yield
      ensure
        ActiveRecord::Base.connection_pool.release_connection
      end
    end
  end
end

c) c exists but I haven’t fully finished figuring it out yet, maybe in another post?

Please let me know if I have misstated or confused something, or if you have a better way to handle any of the above.

---

Possibly related posts: (automatically generated)

• Code Review
• New version of pgAdmin
• Chord sketches