How to manage opening and closing database connections while working with activerecords and multiple threads

Question

I am trying to implement a multithreaded method in rails such that I can create/update multiple records very quickly.

This is the outline of my program.

ActiveRecord::Base.transaction do
  (1..10).each do |i|
    arr[i] = Thread.new {
       <some logic on obj>
       ...
       ...
       obj.save! 
    }
  end
  arr.each {|t| t.join}
end

This gives me warnings on my log.

DEPRECATION WARNING: Database connections will not be closed automatically, 
please close your database connection at the end of the thread by calling `close`
on your connection.

And it gives me an error

could not obtain a database connection within 5 seconds (waited 5.059358 seconds). 
The max pool size is currently 5; consider increasing it.

I tried: - changing database.yaml and increasing the poolsize and timeout there. - modified the existing code in the following way.

   ActiveRecord::Base.connection_pool.clear_stale_cached_connections!
   begin
     ActiveRecord::Base.transaction do
        (1..10).each do |i|
          arr[i] = Thread.new {
             <some logic on obj>
             ...
             ...
             obj.save!
             ActiveRecord::Base.connection.close 
          }
        end
        arr.each {|t| t.join}
     end
   ensure
     ActiveRecord::Base.connection.close if ActiveRecord::Base.connection
     ActiveRecord::Base.clear_active_connections!
   end

I am still getting the same warning and error. I am obviously missing the concept here. How do I proceed with this?

Blue Smith · Accepted Answer · 2016-02-20 04:09:49Z

31

To prevent connection leak in multi threads, you have to manage the connection manually. You can try:

Thread.new do
  ActiveRecord::Base.connection_pool.with_connection do
    # Do whatever
  end
end

One problem of ActiveRecord::Base.connection_pool.with_connection is that it always prepare a connection even if the code inside does not need it.

We can improve it a little bit by using ActiveRecord::Base.connection_pool.release_connection:

Thread.new do
  begin
    # Do whatever
  ensure
    ActiveRecord::Base.connection_pool.release_connection
  end
end

edited Feb 20, 2016 at 4:09

answered Nov 4, 2014 at 10:56

Blue Smith

8,8402 gold badges30 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

anemaria20 Over a year ago

This method only works for connections that have been obtained through connection or with_connection methods, connections obtained through checkout will not be automatically released. source: api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/…

Max Popoff Over a year ago

@anemaria20 Yes, it says this. But even if you don't use with_connection, explicitly, that method works. E.g. you can try -

(1..1000).each{ Thread.new { puts Model.count; ActiveRecord::Base.connection_pool.release_connection; sleep rand(10) } }; nil

It will actually wait until any connection is released if the pool is full, but w/o explicit release_connection it will give you pool timeout error

phoet · Accepted Answer · 2014-05-12 00:12:36Z

0

if you want to run in N threads, make sure you have N connections in the pool

#  ActiveRecord::Base.connection_config
#  # => {pool: 5, timeout: 5000, database: "db/development.sqlite3", adapter: "sqlite3"}

otherwise you will be able to fetch a connection from the pool.

answered May 12, 2014 at 0:12

phoet

18.9k4 gold badges49 silver badges76 bronze badges

Collectives™ on Stack Overflow

How to manage opening and closing database connections while working with activerecords and multiple threads

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related