On Tuesday 21 September 2010, michael_teter wrote:
My app is required to perform some user-driven CRUD operations on two
databases simultaneously. This is not the normal situation, but it
applies to a low-activity admin controller.
My current solution involves three models - DbDual, DbPrimary, and
DbSecondary. DbDual uses the other two within nested transactions,
and DbSecondary does an establish_connection to connect it to the
Your code looks like this?
class DbSecondary < ActiveRecord::Base
This does what it says, it connects DbSecondary (and its descendants, if
any) to the named database connection. If you suspect that this doesn’t
work as intended, look what the logs of your DB server say. If you see
that new connections to the DB are established for each request, make
sure that class caching is really enabled in production environment.
Regarding transactions: don’t expect any coordination over and above
what you do yourself. ActiveRecord does not give you distributed
transactions. I haven’t tried this myself, so big grains of salt are in
order. Let’s say you have code like this
# do something in first database
# do something in second database
In execution, this code is translated to two independent transactions,
one on each of the databases. The DbPrimary transaction is mapped to a
SAVEPOINT/RELEASE SAVEPOINT pair within the DbDual transaction. The
DbSecondary transaction is completely independent. However, if this
latter transaction raises a (suitable) exception, it still aborts the
enclosing DbDual transaction in addition to the DbSecondary one.
If a failure occurs immediately after the DbSecondary.transaction is
committed, for example because the connection to the first database was
lost, the second transaction will be committed whereas the first one
will be rolled back when the DB server decides it has timed out (or
whatever else might cause it to drop the transaction).