Posted almost 4 years ago. Visible to the public. Repeats.

Using ActiveRecord with threads might use more database connections than you think

Database connections are not thread-safe. That's why ActiveRecord uses a separate database connection for each thread.


For instance, the following code requires three database connections:

Copy
3.times do Thread.new do User.first # first database access makes a new connection end end

Unfortunately ActiveRecord never returns these connections after the thread finishes, unless you tell it to. Since the number of connections is limited, you will eventually run out of connections. In that case, the next thread trying to connect will raise ActiveRecord::ConnectionTimeoutError.

The number of connections is limited by both your database server and your ActiveRecord pool size configuration, which defaults to 5. The ActiveRecord connection pool holds a list of all connections your worker process has made so far. With your help, the pool can re-use connections that are no longer in use by threads that have terminated.

Explicitily return the connection when you're done

We already learned that this code leaks a database connection:

Copy
Thread.new do User.first # first database access makes a new connection end

To fix this, you can call clear_active_connections! to release the database connection back into the pool when you're done:

Copy
Thread.new do begin User.first # first database access makes a new connection ensure ActiveRecord::Base.clear_active_connections! end end

The clear_active_connections! is available in Rails 4 and 5. There are similar methods with slightly different semantics in earlier versions of Rails.

After the connection was returned to the pool, it can be re-used by the next thread.

Rails does this automatically after every request, for the one thread used to process the request. So you only need to do this for any threads you spawn yourself. Rails does not know about any additional threads from your code, so it cannot release the connections for you.

As an alternative, ActiveRecord also has a with_connection method that you can wrap your code in:

Copy
Thread.new do ActiveRecord::Base.with_connection do # your code here end end

The one drawback is that this will always check out a connection from the pool, regardless whether or not your code will actually need a connection. E.g. rendering partials in parallel will not need a connection if the rendered data is already preloaded (which it should be).

Dealing with unfixable code

Sometimes you need to work with code that is out of your control. E.g. convoluted legacy code or code from external gems. If that code makes threads and leaks database connections, you might not be able to fix it with the methods above.

For these cases you can set a reaping_frequency option in your database.yml. This will schedule a thread that will release unused connections every x seconds.

For code that is in your own control, I recommend to not rely on the reaper and make sure your code doesn't leak instead.

Once an application no longer requires constant development, it needs periodic maintenance for stable and secure operation. makandra offers monthly maintenance contracts that let you focus on your business while we make sure the lights stay on.

Owner of this card:

Avatar
Henning Koch
Last edit:
12 months ago
by Henning Koch
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Henning Koch to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more