January 16, 2009

Monkey patching ActiveRecord to automatically release connections

Filed under: concurrency, patch, rails, ruby — Tags: , , , — coderrr @ 12:25 am

First let me say this is only really useful if you are using ActiveRecord in a non-Rails, multi-threaded context. As in standalone Ruby app or maybe even in a different web framework.

The point of this patch is to allow you to take advantage of ActiveRecord’s connection pool without having to ever deal with it explicitly. The biggest thing you have to worry about with the connection pool is releasing connections when you’re done with them, so that is what this patch does for you. This allows you to have large numbers of threads, even long running ones, using ActiveRecord without requiring an equally sized connection pool.

For example, let’s say you’ve setup your connection pool to allow 3 connections. Try to run this code:

ts = []
6.times do
  ts << do
ts.each &:join

It’s going to take approximately 5 seconds to finish. Why? Because the first 3 threads take up all the connections from the pool. When the 4th thread tries to checkout a connection it waits up to wait_timeout (defaults to 5) seconds for a connection to become available. If it can’t get any within that amount of time it will checkin connections for dead threads and then try to checkout again. If you tried to do this with 7 threads you would get a “cannot obtain database connection error” because even after 5 seconds there wasn’t an available connection for the last thread.

As I showed in my previous posts you could solve this relatively easily by calling a cleanup method periodically or at the end of every thread. But how about this example:

6.times do do
    sleep 100 # or do something non ActiveRecord related for 100 seconds

This one cannot be solved as easily. None of the threads die quickly so there are no connections to reclaim. This is where my patch comes in. Even though our threads are alive for 100 seconds we are really only using a connection for a small % of their lifespan. If your application fits this usage pattern then this patch is for you.

How it works
First we have the cleanup_connection method. Wrap any code with this method and it will ensure that your connection is checked back into the pool when the block completes:

        def cleanup_connection
          return yield if Thread.current[:__AR__cleanup_connection]

            Thread.current[:__AR__cleanup_connection] = true
            Thread.current[:__AR__cleanup_connection] = false

The thread locals are used to make sure the connection won’t be released on nested calls. I will demonstrate the need for this protection in a second. But for now, let’s fix our previous example:

10.times do do
    User.cleanup_connection do
    sleep 100

Each thread will release its connection before the sleep and all the threads should complete their find statements very quickly. But who wants to call cleanup_connections all over their code. Luckily, you don’t have to. My patch wraps all the necessary ActiveRecord internals with it instead of you having to wrap your code.

The following example illustrates the need for nesting protection. Keep in mind my patch has wrapped both transaction and create with cleanup_connection.

User.transaction do
  User.create(:name => '1')
  User.create(:name => '2')

Without nesting protection the first create call would have released our connection after it completed. Which means another thread could have checked it out in between or we could have gotten a different connection for the second call. Either way, our transaction would have been messed up. In short, nesting protection allows you to wrap the smallest amount of code necessary rather than requiring you wrap your code at a very high level. This allows you to keep the connections checked out for as short a time as possible.

There is of course a performance hit for all the added method calls and the cost of constantly checking out/in connections to the pool, but it’s not much. On a query which takes 0.15 seconds to run here are the numbers:

1000x queries
without patch:
with patch:
with patch where all 1000 calls are nested under a single cleanup_connection to prevent checkin/checkout for every call:

So the penalty is about 3%. The actual overhead penalty is about 30% (query of 0.001 seconds) so the faster your queries the closer you move toward this.

How to use it
Get it here. It’s a monkey patch so require it anywhere after ActiveRecord has been loaded.

I’ve done my best to try to find all the ActiveRecord methods that need to be wrapped but it’s possible that I have missed some. Because of this I monkey patch the connection method to raise an alert whenever it is called from outside of a cleanup_connection block. If you see one of these, you can look through the stack trace, determine which method needs to be wrapped and add it to the methods_to_wrap hash. After you are confident all necessary methods are patched you can remove the connection monkeypatch to speed things up a bit.

Rails core
I’ve submitted a patch to Rails for just the cleanup_connection portion. I think it would be a nice addition to the already existing but not very useful with_connection method. Feel free to comment or +/-1 the patch.


  1. […] from and makes the ActiveRecord behave correctly in a threaded environment. Please see Monkey patching ActiveRecord to automatically release connections « coderrr for more […]

    Pingback by Ramblings Ramaze and ActiveRecord — February 4, 2009 @ 7:49 am

  2. Thanks, man you saved my day! Really
    great work. Throwing Exceptions was a clever idea, i find uses of the connection method easily and quickly

    Comment by Vincent — March 12, 2009 @ 8:57 am

  3. I notice that validates_uniqueness_off triggers two warnings to be recorded:

    connection called outside of cleanup_connection block
    c:/ruby1.8.7/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/validations.rb:728:in `validates_uniqueness_of_


    connection called outside of cleanup_connection block
    c:/ruby1.8.7/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/validations.rb:734:in `validates_uniqueness_of_

    Looking at the source, it calls connection twice (on those lines, as you’d expect) just to get hold of some config it needs, e.g. line 728:
    comparison_operator = “#{connection.case_sensitive_equality_operator} ?”

    So I’m not sure if these need wrapping or not. I tried adding ActiveRecord::Validations::ClassMethods => [:validates_uniqueness_of] to methods_to_wrap but it didn’t stop the error being logged, which confuses me a bit. So, how best to deal with this situation?

    Comment by Sam — March 20, 2009 @ 2:26 pm

  4. Hrm….

    What version of rails are you using? Do you have any code you can show me? I tried a 2.2.2 clean rails proj with a model with a validates_uniqueness_of and didn’t get those warnings.

    Answering whether or not you need to wrap around connection method calls for config uses, the answer is yes. If you don’t wrap it, it will still open a connection which will just sit there until your code reaches and finishes a cleanup_connection block.

    Comment by coderrr — March 21, 2009 @ 1:19 am

  5. I’m not using Rails, which is why I need this patch in the first place. As it happens I’m using Ramaze, which is fully threaded.

    It’s in a customer project so I can’t show code I’m afraid. It’s not doing anything very exciting mind you and it always logs the warning when the uniqueness validation fires. What part of the patch should be already covering that case?

    Comment by Sam — March 21, 2009 @ 8:20 am

  6. Below is my call chain for a validates_uniqueness_of call. The reason I don’t get a warning is because “transaction” is a parent in the call chain, and my monkeypatch wraps transaction with connection_cleanup.

    So I’m thinking if for some reason you have removed transaction from the wrapped methods, or if for some reason ActiveRecord isn’t calling transaction you might get this.

    Can you show the full stack trace? Mine is:

    /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/validations.rb:728:in `validates_uniqueness_of’
    /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/validations.rb:400:in `validates_each’
    /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/validations.rb:397:in `each’
    /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/validations.rb:397:in `validates_each’
    /usr/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/callbacks.rb:182:in `call’
    /usr/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/callbacks.rb:182:in `evaluate_method’
    /usr/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/callbacks.rb:166:in `call’
    /usr/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/callbacks.rb:90:in `run’
    /usr/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/callbacks.rb:90:in `each’
    /usr/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/callbacks.rb:90:in `send’
    /usr/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/callbacks.rb:90:in `run’
    /usr/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/callbacks.rb:277:in `run_callbacks’
    /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/validations.rb:1030:in `valid_without_callbacks?’
    /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/callbacks.rb:286:in `valid?’
    /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/validations.rb:1019:in `save_without_dirty!’
    /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/dirty.rb:87:in `save_without_transactions!’
    /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:150:in `save!’
    /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/database_statements.rb:66:in `transaction’
    /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:129:in `transaction’
    /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:138:in `transaction’
    /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:150:in `save!’
    /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:158:in `rollback_active_record_state!’
    /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:150:in `save!’
    /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/validations.rb:991:in `create!’

    Comment by coderrr — March 21, 2009 @ 2:10 pm

  7. Call stack for the first error is below (the second one is much the same).

    The notable difference is that you call save! which in turn calls transaction, whereas I call valid? directly, and that doesn’t call transaction. My version of the patch is exactly as downloaded – no modifications.

    connection called outside of cleanup_connection block
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/validations.rb:728:in `validates_uniqueness_of’
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/validations.rb:400:in `validates_each’
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/validations.rb:397:in `each’
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/validations.rb:397:in `validates_each’
    /usr/local/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/callbacks.rb:182:in `call’
    /usr/local/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/callbacks.rb:182:in `evaluate_method’
    /usr/local/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/callbacks.rb:166:in `call’
    /usr/local/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/callbacks.rb:90:in `run’
    /usr/local/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/callbacks.rb:90:in `each’
    /usr/local/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/callbacks.rb:90:in `send’
    /usr/local/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/callbacks.rb:90:in `run’
    /usr/local/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/callbacks.rb:277:in `run_callbacks’
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/validations.rb:1029:in `valid_without_callbacks?’
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/callbacks.rb:286:in `valid?’
    /Users/me/Dev/Ruby/Test_trunk/lib/crud/crud_instance_methods.rb:76:in `edit_submit’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/template.rb:63:in `__send__’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/template.rb:63:in `render_method’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/template.rb:48:in `result_and_file’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/template.rb:40:in `reaction_or_file’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/template.rb:71:in `wrap_compile’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/template/ezamar.rb:23:in `transform’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/action/render.rb:115:in `uncached_render’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/action/render.rb:39:in `render’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/action/render.rb:11:in `stack’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/action/render.rb:28:in `render’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/controller.rb:266:in `handle’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/dispatcher/action.rb:29:in `call’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/dispatcher/action.rb:28:in `catch’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/dispatcher/action.rb:28:in `call’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/dispatcher.rb:122:in `filter’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/snippets/ordered_set.rb:49:in `each’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/snippets/ordered_set.rb:49:in `__send__’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/snippets/ordered_set.rb:49:in `method_missing’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/dispatcher.rb:121:in `filter’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/dispatcher.rb:109:in `dispatch’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/dispatcher.rb:108:in `catch’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/dispatcher.rb:108:in `dispatch’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/dispatcher.rb:107:in `catch’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/dispatcher.rb:107:in `dispatch’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/dispatcher.rb:78:in `general_dispatch’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/dispatcher.rb:53:in `call’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/current.rb:19:in `call’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/reloader.rb:84:in `call’
    /usr/local/lib/ruby/gems/1.8/gems/rack-0.9.1/lib/rack/showstatus.rb:20:in `call’
    /usr/local/lib/ruby/gems/1.8/gems/rack-0.9.1/lib/rack/showexceptions.rb:23:in `call’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/adapter/base.rb:121:in `respond’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/snippets/ramaze/state.rb:29:in `initialize’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/snippets/ramaze/state.rb:29:in `new’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/snippets/ramaze/state.rb:29:in `wrap’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/adapter/base.rb:120:in `respond’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/adapter/base.rb:111:in `call’
    /usr/local/lib/ruby/gems/1.8/gems/rack-0.9.1/lib/rack/handler/mongrel.rb:59:in `process’
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/lib/mongrel.rb:159:in `process_client’
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/lib/mongrel.rb:158:in `each’
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/lib/mongrel.rb:158:in `process_client’
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/lib/mongrel.rb:285:in `run’
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/lib/mongrel.rb:285:in `initialize’
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/lib/mongrel.rb:285:in `new’
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/lib/mongrel.rb:285:in `run’
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/lib/mongrel.rb:268:in `initialize’
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/lib/mongrel.rb:268:in `new’
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/lib/mongrel.rb:268:in `run’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/adapter/mongrel.rb:17:in `startup’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/adapter/base.rb:58:in `start’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/adapter.rb:58:in `start_adapter’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze/adapter.rb:28:in `startup’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze.rb:95:in `start’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze.rb:94:in `each’
    /Users/me/Dev/Ruby/RamazeGit/lib/ramaze.rb:94:in `start’

    Comment by Sam — March 21, 2009 @ 9:42 pm

  8. Ok so after some investigation I found that because of how validations (and ActiveSupport callbacks) work, it’s actually not possible to wrap a validation method. (MIGHT be possible with some extremely dirty hax)

    So the best thing here would be to just wrap AR::Base#valid?:

    ActiveRecord::Base => [:quoted_id, :valid?],

    I’ve also updated the file on github.

    Comment by coderrr — March 22, 2009 @ 3:20 am

  9. That works great now! Thanks a lot for your help.

    Comment by Sam — March 22, 2009 @ 8:59 am

  10. np

    Comment by coderrr — March 22, 2009 @ 9:19 am

  11. […] actually to have wrap all your code in those annoying blocks, you can check out this monkeypatch (blog post here) which essentially wraps all DB touching ActiveRecord methods with with_connection for you. […]

    Pingback by ActiveRecord’s with_connection is now useful! « coderrr — May 5, 2009 @ 10:27 pm

  12. Is this patch still required for the latest version of ActiveRecord?

    I, like a previous poster am using Ramaze and it’s site still mentions the patch.

    Cheers, P

    Comment by pauliephonic — July 6, 2009 @ 8:04 pm

  13. sure is, i have my doubts that’ll be changing anytime soon. if you care enough go make a ruckus about how AR needs to have this functionality built into it :)

    Comment by coderrr — July 6, 2009 @ 8:06 pm

  14. I have this exact same problem (multithreaded Ruby daemon that uses ActiveRecord 2.3.10), and had the same idea as you… except that I was only planning to wrap #transaction and #execute. What is the downside to that?


    Comment by Christopher J Bottaro — November 2, 2010 @ 1:14 am

    • Oh, I see now. #execute is a method on the connection adapter.

      Comment by Christopher J Bottaro — November 2, 2010 @ 4:44 am

  15. […] This gem will only work with ActiveRecord >= 3.0.0. For a 2.x version, see here. ← MagRails […]

    Pingback by Scaling ActiveRecord in Adhearsion | Mojo Lingo Blog — October 10, 2011 @ 9:37 pm

  16. I faced similar issue and fixed it by releasing the connection explicitly, you can find my article here for more details.

    Comment by vigram karuppiah — July 30, 2014 @ 1:27 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: Logo

You are commenting using your 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

The Silver is the New Black Theme. Blog at


Get every new post delivered to your Inbox.

Join 31 other followers

%d bloggers like this: