coderrr

January 8, 2009

ActiveRecord threading issues and resolutions

Filed under: concurrency, rails, ruby — Tags: , , — coderrr @ 11:38 pm

Shameless Plug: Use a VPN Service to protect your internet habits from becoming records stored in a database.

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.

16 Comments »

  1. Concurrency issues are what me makes love (to) Erlang.

    Comment by Apostlion — January 9, 2009 @ 1:27 pm

  2. [...] ActiveRecord threading issues and resolutions – Concerns specific to ActiveRecord 2.2 and MySQL. [...]

    Pingback by Double Shot #368 « A Fresh Cup — January 12, 2009 @ 12:34 pm

  3. you might get some traction from
    ActiveRecord::Base.connection.instance_eval {@connection.reconnect = true} or does that not work in multi-thread?

    Doesn’t rails have some means of gleaning the connections from now dead threads? If not this is a big problem :)

    I’m assuming you mean “when a thread dies” as “some servers like mongrel spawn a new thread per request, and when that thread dies.”?
    Thanks!

    Comment by roger — January 12, 2009 @ 6:52 pm

  4. Hey roger,

    Thanks for both your comments.

    Thanks for pointing out the mysql api reconnect flag, I’ll give it a try.

    AR does have a few ways of cleaning up connections from dead threads. One of them is the clear_stale_cached_connections which I talked about. I think I will do another post focusing on this.

    About “when a thread dies”… this post was actually mainly focusing on AR in a non-Rails-request context. Although if you are spawning new threads from inside a Rails request you will face the same issues. So no, I am not referring to threads which mongrel creates.

    Comment by coderrr — January 12, 2009 @ 7:40 pm

  5. interesting. I’m just not sure how to integrate the reconnect flag with thread pooled AR :)

    I think what AR does currently to recoup its AR instances [back to the pool] is an explicit call at the end of a request [?]

    Good point about how spawned threads would also run into this problem [within AR].
    GL!
    -=r

    Comment by roger — January 12, 2009 @ 7:56 pm

  6. Regarding how it recoups:
    http://coderrr.wordpress.com/2009/01/12/rails-22-activerecord-connection-cleanup/

    Comment by coderrr — January 12, 2009 @ 8:29 pm

  7. nice
    -=r

    Comment by roger — January 12, 2009 @ 8:36 pm

  8. I’ve so far had no issues using activerecord’s connection pool with the mysql gem.

    Would you explain or reference how the deadlock situation arises?

    Comment by nomeat — February 15, 2009 @ 1:31 am

  9. Good question nomeat…

    I ran into this deadlock in our production code a long time ago…

    Replacing mysql with mysqlplus fixed it. Now that doesn’t mean the deadlock was actually in the mysql gem itself, so I could be wrong. Or it could just be a really weird deadlock to reproduce.

    That being said mysqlplus is more preferrable in a threaded environment because it doesn’t block during the DB query. Of course its also less preferrable because its not as battle-tested as the mysql gem is.

    Comment by coderrr — February 16, 2009 @ 6:37 pm

  10. The error occurred when using an older version of mysqlplus that defaulted to using the ruby version of async_query, which somehow got confused when sockets were closed during C code [I guess--I haven't actually recreated it or looked at it, myself].
    Cheers!

    Comment by roger — February 18, 2009 @ 5:31 pm

  11. actually he was asking about a deadlock in the mysql (not mysqlplus) gem, which I refered to in this post

    Comment by coderrr — February 18, 2009 @ 5:38 pm

  12. I’m also curious where you use threads. Is it true to say that if you don’t explicitly make threads in your app, you’re ok?

    Comment by Andrew Roth — March 26, 2009 @ 1:31 am

  13. The threads are being used in a long running (non-web) server which uses ActiveRecord to communicate with the rails website.

    Threads are not used at all in the actual rails app servers which handle web requests.

    Comment by coderrr — March 26, 2009 @ 2:29 am

  14. Please, can you Pm me and tell me couple of more thinks about this, I’m seriously fan of one’s blog… will get solved properly asap.

    Comment by Dannie Paik — June 1, 2011 @ 11:23 am

  15. [...] to do that same thing. Do not not  not ever call verify_active_connections! in current Rails.  verify_active_connections! is not thread-safe in current rails.  I can tell you from experience that if you do call it while you have multi-threaded ActiveRecord [...]

    Pingback by Multi-threaded use of Rails ActiveRecord 3.0-3.1 | Bibliographic Wilderness — November 14, 2011 @ 7:32 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Customized Silver is the New Black Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 28 other followers

%d bloggers like this: