Proper way to set up transactions in Spring for different data sources?

Question

I have an application which needs to connect to multiple databases. This is an administrative application which is basically used to manage entries in different databases - we do not need to access multiple databases simultaneously nor do we need any sort of distributed transaction management.

Basically one area of the application lets you create gadgets in Database A, and another area of the application lets you configure similar gadgets in Database B.

We already have transactions set up and working perfectly when using just one datasource. The configuration looks like so:

<aop:config>
    <aop:pointcut id="companyServicePoint" 
          expression="execution(* com.company.service.CompanyService.*(..))" />

    <aop:advisor advice-ref="companyServiceTxAdvice"
         pointcut-ref="companyServicePoint"/>
</aop:config>

<tx:advice id="companyServiceTxAdvice" transaction-manager="txManager">
    <tx:attributes>
        <!-- set propogation required on create methods, all others are read-only -->
        <tx:method name="create*" propagation="REQUIRED"/>
        <tx:method name="*" read-only="true" />
    </tx:attributes>
</tx:advice>

<bean id="txManager" 
   class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource" />
</bean>

This sets a pointcut on any execution of any methods within CompanyService and associates transaction advice with the pointcut which requires transactions for any methods whose name starts with "create". The transaction advice is associated with a TransactionManager which is tied to the dataSource.

When adding a second (or more) datasources, how can I apply the same transaction advice to other datasources? Since the AOP advice can only be associated with one transactionManager, which can only be associated with one dataSource, do I need to set up duplicate transaction advice?

If I setup duplicate transaction advice to the same pointcut, won't this mean that any invocations of methods in my CompanyService interface will require propogation against all of my dataSources?

To make my last question a little clearer, I will have multiple beans declared which implement the CompanyService interface, and each of these beans will have a separate CompanyDAO to access their individual DataSource. I fear that this approach will mean that when the companyService1 bean is invoked, transaction advice will be triggered on all companyService beans/dataSources.

Am I going about this the wrong way?

Update: I've actually tested out the configuration I talked about above (attaching two advisors to the same pointcut), and invoking any method on either individual instance of the CompanyService implementation does in fact create new transactions on both dataSources, as expected:

DEBUG company.serviceDataSourceTransactionManager - Creating new transaction with name [com.company.service.CompanyService.createCompany]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
DEBUG company.serviceDataSourceTransactionManager - Acquired Connection [connection1 string here...] for JDBC transaction
...
DEBUG company.serviceDataSourceTransactionManager - Creating new transaction with name [com.company.service.CompanyService.createCompany]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
DEBUG company.serviceDataSourceTransactionManager - Acquired Connection [connection2 string here...] for JDBC transaction
...
DEBUG company.serviceDataSourceTransactionManager - Rolling back JDBC transaction on Connection [connection1 string here...]
...
DEBUG company.serviceDataSourceTransactionManager - Rolling back JDBC transaction on Connection [connection2 string here...]

This seems like it would cause issues down the road, since either CompanyService instance is only ever working with a single DataSource.

Is there a better way to configure what I seek to accomplish?

Answer 1

Have you tried using the JtaTransactionManager?

http://forum.springsource.org/showthread.php?t=10476

Answer 2

Yes, you need a duplicate transaction advice. Notice in the following configuration that the pointcut expression selects a specific CompanyService bean.

<bean id="companyService1" class="com.company.service.CompanyServiceImpl">
  <property name="companyDao">
    <bean class="com.company.service.CompanyDAO">
      <property name="dataSource" ref="dataSource1"/>
    </bean>
  </property>
</bean>

<aop:config>
  <aop:pointcut
      id="companyServicePoint1"
      expression="bean(companyService1)"/>
  <aop:advisor
      advice-ref="companyServiceTxAdvice1"
      pointcut-ref="companyServicePoint1"/>
</aop:config>

<tx:advice id="companyServiceTxAdvice1" transaction-manager="txManager1">
  <tx:attributes>
    <!-- set propogation required on create methods, all others are read-only -->
    <tx:method name="create*" propagation="REQUIRED"/>
    <tx:method name="*" read-only="true"/>
  </tx:attributes>
</tx:advice>

<bean
    id="txManager1" 
    class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
  <property name="dataSource" ref="dataSource1"/>
</bean>

To configure another CompanyService bean, you need to duplicate the same verbose boilerplate. Another way to demarcate transactions in Spring uses TransactionProxyFactoryBean. It's slighty less verbose because it uses a parent bean definition to configure common properties inherited by child beans.

<bean
    id="baseTransactionProxy"
    class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean"
    abstract="true">
  <property name="transactionAttributes">
    <props>
      <prop key="create*">PROPAGATION_REQUIRED</prop>
      <prop key="*">PROPAGATION_REQUIRED,readOnly</prop>
    </props>
  </property>
</bean>

<bean id="companyService1" parent="baseTransactionProxy">
  <property name="transactionManager" ref="txManager1"/>
  <property name="target">
    <bean class="com.company.service.CompanyServiceImpl">
      <property name="companyDao">
        <bean class="com.company.service.CompanyDAO">
          <property name="dataSource" ref="dataSource1"/>
        </bean>
      </property>
    </bean>
  </property>
</bean>

<bean
    id="txManager1" 
    class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
  <property name="dataSource" ref="dataSource1"/>
</bean>