How can I execute a Database Operation outside of a transaction in Rails / ActiveRecord

Question

I need to execute some raw SQL in my Rails app. The query will do cause an implicit commit if it is performed within a transaction. We are using MySQL with InnoDB and the query will include e.g. create table.

Executing the query with ActiveRecord::Base.connection.execute triggers the implict commit which is a problem.

It feels like I just need a separate connection for performing my queries. Can ActiveRecord provide this? I've seen discussions of connecting to multiple databases but not multiple connections to the same database.

A solution doesn't have to involve ActiveRecord if there's a better way.

Our Rails and ActiveRecord version is 3.2.3.

Answer 1

Database connections are done on a per thread basis (this is basically required for thread safety), which you can use to your advantage: just execute your code in a separate thread, for example

ActiveRecord::Base.transaction do
  # ...
  Thread.new do
    ActiveRecord::Base.connection.execute "..." # in a new connection
  end.join
end

As of rails 4, activerecord no longer reaps connections created in this way automatically. To avoid leaking connections you need to return them to the pool. As Matt Connelly suggests, the easiest way to do this is to use the with_connection method which will check the connection back in at the end of the block, for example

Thread.new do
  ActiveRecord::Base.connection_pool.with_connection do
    ...
  end
end