Search code examples
postgresqlspring-bootatomikoshibernate-jpa

Spring boot routing datasource not work correctly

I am configuring spring boot web app with Postgres, Hibernate, Atomikos, JPA and Thymeleaf. I use AbstractRoutingDataSource to route to new datasource as below:

    @Override
    protected Object determineCurrentLookupKey() {
            String currentTenantId = TenantContextHolder.getCurrentTenantId();

            // Get endpoint, database name from default database

            // New datasource
            DataSource dataSource = constructDataSource(setting, endpoint, databaseName);
            resolvedDataSourceMap.putIfAbsent(currentTenantId, dataSource);
            setTargetDataSources(resolvedDataSourceMap);
            afterPropertiesSet();

            return currentTenantId;

}

If I call 

userRepository.countByUserId("aaa"); // This is custom method in my repository

, it routes and return result correctly, but if I call 

userRepository.findAll(); // My repository extends from SimpleJPARepository

, I always get the following error (If I call again, it will work ok!) Please help me explain why and what I should do to fix this error.

2018-03-24 12:03:47.566  INFO 17472 --- [nio-8080-exec-2] c.a.d.xa.XATransactionalResource         : 0000000002-pgxad: refreshed XAResource
2018-03-24 12:03:47.702 ERROR 17472 --- [nio-8080-exec-2] c.a.datasource.xa.XAResourceTransaction  : XA resource 'defaultDataSource-pgxad': prepare for XID '3139322E3136382E31372E3135352E746D313532313836373830313734343030303032:3139322E3136382E31372E3135352E746D32' raised -3: the XA resource detected an internal error

org.postgresql.xa.PGXAException: Error preparing transaction
    at org.postgresql.xa.PGXAConnection.prepare(PGXAConnection.java:309) ~[postgresql-42.2.1.jar:42.2.1]
    at com.atomikos.datasource.xa.XAResourceTransaction.prepare(XAResourceTransaction.java:384) ~[transactions-jta-4.0.6.jar:na]
    at com.atomikos.icatch.imp.PrepareMessage.send(PrepareMessage.java:40) [transactions-4.0.6.jar:na]
    at com.atomikos.icatch.imp.PrepareMessage.send(PrepareMessage.java:19) [transactions-4.0.6.jar:na]
    at com.atomikos.icatch.imp.PropagationMessage.submit(PropagationMessage.java:67) [transactions-4.0.6.jar:na]
    at com.atomikos.icatch.imp.Propagator$PropagatorThread.run(Propagator.java:63) [transactions-4.0.6.jar:na]
    at com.atomikos.icatch.imp.Propagator.submitPropagationMessage(Propagator.java:42) [transactions-4.0.6.jar:na]
    at com.atomikos.icatch.imp.ActiveStateHandler.prepare(ActiveStateHandler.java:171) [transactions-4.0.6.jar:na]
    at com.atomikos.icatch.imp.CoordinatorImp.prepare(CoordinatorImp.java:523) [transactions-4.0.6.jar:na]
    at com.atomikos.icatch.imp.CoordinatorImp.terminate(CoordinatorImp.java:687) [transactions-4.0.6.jar:na]
    at com.atomikos.icatch.imp.CompositeTransactionImp.commit(CompositeTransactionImp.java:282) [transactions-4.0.6.jar:na]
    at com.atomikos.icatch.jta.TransactionImp.commit(TransactionImp.java:172) [transactions-jta-4.0.6.jar:na]
    at com.atomikos.icatch.jta.TransactionManagerImp.commit(TransactionManagerImp.java:414) [transactions-jta-4.0.6.jar:na]
    at com.atomikos.icatch.jta.UserTransactionImp.commit(UserTransactionImp.java:86) [transactions-jta-4.0.6.jar:na]
    at org.springframework.transaction.jta.JtaTransactionManager.doCommit(JtaTransactionManager.java:1040) [spring-tx-5.0.4.RELEASE.jar:5.0.4.RELEASE]

...

Caused by: org.postgresql.util.PSQLException: ERROR: 準備されたトランザクションは無効です。
  Hint: max_prepared_transactionsを非ゼロに設定してください。

...

2018-03-24 12:03:47.715 ERROR 17472 --- [nio-8080-exec-2] c.a.datasource.xa.XAResourceTransaction  : XA resource '0000000002-pgxad': prepare for XID '3139322E3136382E31372E3135352E746D313532313836373830313734343030303032:3139322E3136382E31372E3135352E746D33' raised -3: the XA resource detected an internal error

org.postgresql.xa.PGXAException: Error preparing transaction
    at org.postgresql.xa.PGXAConnection.prepare(PGXAConnection.java:309) ~[postgresql-42.2.1.jar:42.2.1]
    at com.atomikos.datasource.xa.XAResourceTransaction.prepare(XAResourceTransaction.java:384) ~[transactions-jta-4.0.6.jar:na]
Solution

  • It is error when I accessed 2 databases in a same transaction. Therefore, I need to create new transaction by REQUIRE_NEW as below:

    @Transactional(propagation = Propagation.REQUIRES_NEW)
Tenant findByTenantId(Long tenantId);

    or, as @a_horse_with_no_name said, "need to set max_prepared_transactions to something greater than the default of 0 in order to use 2phase commits and distributed transactions".

    Thank for your response, @a_horse_with_no_name!