title: Sharing a transaction while using AbstractRoutingDataSource to switch datasources
body: <p>How to share a transaction between datasources when using <a href="http://blog.springsource.org/2007/01/23/dynamic-datasource-routing/" rel="nofollow">AbstractRoutingDataSource</a> to switch the active data source?</p>
  
  <p>So far, without the transaction the queries get executed on both databases correctly, but when I start a transaction, everything executes on the same database (i.e. I cannot switch to the second database anymore).</p>
  
  <p>Any ideas?</p>
  
  <pre><code>@Transactional
  public void crossDbTransactionTest() {
      // Selects a datasource from my pool of AbstractRoutingDataSources
      DbConnectionContextHolder.setDbConnectionByYear(2012);
  
      // execute something in the first database
      this.executeSomeJpaQuery("xyz"); 
  
      // switch to the second database
      DbConnectionContextHolder.setDbConnectionByYear(2011);
  
      // execute something in the second database
      this.executeSomeJpaQuery("xyz"); // on any errors rollback changes in both databases
  }
  </code></pre>
  
  <p>EDIT1 (added configuration files):</p>
  
  <p>persistence.xml:</p>
  
  <pre><code>&lt;persistence-unit name="primarnaKonekcija" transaction-type="RESOURCE_LOCAL"&gt;
      &lt;provider&gt;org.hibernate.ejb.HibernatePersistence&lt;/provider&gt;
      &lt;properties&gt;
          &lt;property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect" /&gt;
          &lt;property name="hibernate.max_fetch_depth" value="1" /&gt;
          &lt;property name="hibernate.transaction.manager_lookup_class"
                                value="org.hibernate.transaction.JBossTransactionManagerLookup" /&gt;
      &lt;/properties&gt;
  &lt;/persistence-unit&gt;
  </code></pre>
  
  <p>spring-jpa.xml:</p>
  
  <pre><code>&lt;!-- Shared DB credentials --&gt;
  &lt;context:property-placeholder location="classpath:config.properties" /&gt;
  
  &lt;!-- DB connections by year --&gt;
  &lt;bean id="parentDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" abstract="true"&gt;
      &lt;property name="driverClassName" value="${db.driver}" /&gt;
      &lt;property name="username" value="${db.user}" /&gt;
      &lt;property name="password" value="${db.password}" /&gt;
  &lt;/bean&gt;
  &lt;bean id="dataSource" class="myPackage.DbConnectionRoutingDataSource"&gt;
      &lt;!-- Placeholder that is replaced in BeanFactoryPostProcessor --&gt;
      &lt;property name="targetDataSources"&gt;
          &lt;map key-type="int"&gt;
              &lt;entry key="0" value-ref="placeholderDs" /&gt;
          &lt;/map&gt;
      &lt;/property&gt;
      &lt;property name="defaultTargetDataSource" ref="placeholderDs" /&gt;
  &lt;/bean&gt;
  
  &lt;!-- EntityManager configuration --&gt;
  &lt;bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"&gt;
      &lt;property name="persistenceUnitName" value="primarnaKonekcija" /&gt;
      &lt;property name="dataSource" ref="dataSource" /&gt;
      &lt;property name="jpaVendorAdapter"&gt;
          &lt;bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"&gt;
              &lt;property name="databasePlatform" value="org.hibernate.dialect.SQLServerDialect" /&gt;
              &lt;property name="showSql" value="true" /&gt;
          &lt;/bean&gt;
      &lt;/property&gt;
  &lt;/bean&gt;
  
  &lt;tx:annotation-driven /&gt;
  &lt;tx:jta-transaction-manager /&gt;
  </code></pre>
  
  <p>EDIT 2:</p>
  
  <p>Tried switching everything to JTA and JNDI provided datasources. </p>
  
  <p>Changing transaction-type="RESOURCE_LOCAL" to transaction-type="JTA" didn't work either - JtaStatusHelper throws an NullPointerException, saying that the transactionManager is null.</p>
  
  <p>EDIT 3:</p>
  
  <p>Added JBossTransactionManagerLookup to persistence.xml, now I get the "Adding multiple last resources is disallowed" when switching to second datasource within the transaction.</p>
  
  <p>EDIT 4:</p>
  
  <p>Tried <a href="https://community.jboss.org/wiki/Multiple1PC" rel="nofollow">setting JBOSS</a> so I get past that error - database switching works now with the expected warning: "Multiple last resources have been added to the current transaction. This is transactionally unsafe and should not be relied upon.".
  Gonna try to configure MSSQL XA driver in JBOSS next.</p>
  
  <p>EDIT 5:</p>
  
  <p>After <a href="http://msdn.microsoft.com/en-us/library/aa342335%28v=sql.105%29.aspx" rel="nofollow">configuring MSSQL XA</a>, everything works as intended, will post an answer with steps needed to set this up.</p>