Steps to reproduce

require "active_record"

ActiveRecord::Base.establish_connection(
  adapter:          "sqlite3",
  database:         ":memory:",
  pool:             5, # default
  checkout_timeout: 5, # default
)

threads = 10.times.map do
  Thread.new do
    ActiveRecord::Base.connection.execute "SELECT 1"
  end
end

threads.each(&:join)

Expected behavior

I expected the above code to complete without errors.

The connection pool is intentionally smaller than the number of threads, but since we're executing cheap queries, I expected connections to be immediately checked back into the pool, so that other threads can use them.

Actual behavior

I get connection timeout from 6 threads:

ActiveRecord::ConnectionTimeoutError: could not obtain a connection
from the pool within 5.000 seconds (waited 5.001 seconds);
all pooled connections were in use

This is because, once Active Record checks out a connection, it locks it to the thread that checked it out, so only that thread can continue to use it. Once the main thread + 4 other threads checked out their 5 connections, the other 6 threads didn't have any connections available.

Current workarounds

Historically, Active Record users have been recommended to either

1. clear out active connections with AR::Base.clear_active_connections!
2. manually check out the connection with AR::Base.connection_pool.with_connection { ... }

In Rails, this has been managed automatically. Originally, there was a ConnectionManagement middleware that called AR::Base.clear_active_connections! after each request, releasing locked connections back into the pool. Today, this is surprisingly done by AR::QueryCache, which makes it appear as if though this functionality is only needed for query caching, when in fact it affects Active Record usage in general.

In other web frameworks such as Sinatra, you need to call manually AR::Base.clear_active_connections! after each request. In background jobs, it's recommended to use AR::Base.connection_pool.with_connection { ... }.

Rationale

I believe Active Record's connection pool should automatically check connections back into the pool after queries are finished, available for any other thread to use it. Basically, what AR::Base.connection_pool.with_connection { ... } does.

This is how Sequel's connection pool works. If we run the same script using Sequel, it will complete without errors:

require "sequel"

DB = Sequel.sqlite(max_connections: 5, pool_timeout: 5)

threads = 10.times.map do
  Thread.new do
    DB.run "SELECT 1"
  end
end

threads.each(&:join)

I believe this change would reduce the chance of people running out of connections. For example, they wouldn't need to remember to use ActiveRecord::Base.connection_pool.with_connection { ... } in background jobs. Yes, users should configure sufficiently large pools, but maybe at some point they start using threads inside background jobs, and don't realize that this requires them to increase the pool size even more.

I also believe this could reduce the load on the database, because it would allow using the smaller number of connections in certain cases. A pool of 50 background workers could theoretically use only 10 connections if the queries are fast.

System configuration

Rails version: master, 6.0.0, and probably any other version before

Ruby version: 2.6.0