January 8, 2009

ActiveRecord threading issues and resolutions

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.)

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.

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.

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.

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.

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!

[…] 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 […]