Databases
StackOverflow2013
Postsdbo
POSharing a transaction while using AbstractRoutingDataSource to switch datasources
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSharing a transaction while using AbstractRoutingDataSource to switch datasources
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload