Failed to retrieve a working connection from transactional data source

[marcusportmann]

Hello,

I have an application with two transactional data sources configured.

Only the first (default) is configured to support JPA as shown below.

datasources:
  default:
    url: jdbc:postgresql://localhost:5432/demo
    username: demo
    password: demo
    driver-class-name: org.postgresql.xa.PGXADataSource
    min-pool-size: 1
    max-pool-size: 5
  db1:
    url: jdbc:postgresql://localhost:5432/db1
    username: db1
    password: db1
    driver-class-name: org.postgresql.xa.PGXADataSource
    min-pool-size: 1
    max-pool-size: 5

jpa:
  default:
    compile-time-hibernate-proxies: true
    properties:
      hibernate:
        hbm2ddl:
          auto: none
        show_sql: true
        transaction:
          coordinator_class: jta
          jta:
            platform: JBossTS

I am encountering an issue when injecting the second data source (db1) and attempting to retrieve a connection from the data source for a standard JDBC call as shown below.

  @Inject
  @Named("db1")
  private DataSource db1DataSource;

  @Test
  void simpleJdbcTest() throws Exception {
    try (Connection connection = db1DataSource.getConnection()) {
      try (PreparedStatement statement = connection.prepareStatement("INSERT INTO demo.data (id, integer_value, string_value, date_value, timestamp_value) VALUES (?, ?, ?, ?, ?)")) {
        int value = random.nextInt();

        statement.setLong(1, System.currentTimeMillis());
        statement.setInt(2,  value);
        statement.setString(3,"New Test Data " + value);
        statement.setObject(4, LocalDate.now());
        statement.setObject(5, LocalDateTime.now());

        statement.executeUpdate();
      }
    }
  }

What is injected is a TransactionAwareDataSource.DataSourceProxy instance.

I have traced the db1DataSource.getConnection() call and the bean lookup in the TransactionAwareDataSource.DataSourceProxy.getTransactionAwareConnection method returns a TransactionalConnection$Intercepted instance.

From what I can see, the transactional database connection is not initialised and registered with the TransactionSynchronizationManager.

This causes the prepareStatement call to fail.

I traced the prepareStatement call and observed that the DataSourceUtils.doGetConnection method is invoked with allowCreate = false.

This is a problem since no existing ConnectionHolder instance exists for the data source that can be retrieved from the TransactionSynchronizationManager, and no new ConnectionHolder instance can be created and registered with the TransactionSynchronizationManager.

I tested with other calls, e.g. connection.getMetaData, and encountered the same problem.

When I use the default data source that is configured for JPA it works correctly. After Tracing, it appears that the reason it is working is that the connection is initialized and registered with the TransactionSynchronizationManager by the HibernateTransactionManager.doBegin method.

I am unfortunately not familiar enough with Micronaut to suggest the best place to initialise the connection and register it with the TransactionSynchronizationManager.

Is there some way to resolve this issue?

[graemerocher]

There needs to be a surrounding transaction of the db1 datasource. Since this is a test you would have to inject the appropriate transaction manager and wrap it manually:

@Inject @Named("db2") SynchronousTransactionManager<?> transactionManager

then:

transactionManager.executeWrite((status) -> {
   // logic here
});

[marcusportmann]

Unfortunately, the proposed solution did not work.

No SynchronousTransactionManager<?> instance can be injected for the non-JPA data sources.

I believe this is because of the following.

When the io.micronaut.data:micronaut-data-hibernate-jpa dependency is added it pulls in the io.micronaut.data:micronaut-data-tx-hibernate dependency.

This introduces the io.micronaut.transaction.hibernate5.HibernateTransactionManager bean, which replaces the io.micronaut.transaction.jdbc.DataSourceTransactionManager bean.

While a DataSourceTransactionManager instance is created for each DataSource instance, a HibernateTransactionManager instance is only created for each SessionFactory.

This means that if multiple data sources are configured in the application.yaml configuration file, but they are not all configured for JPA, then those data sources without JPA configuration do not have a matching SynchronousTransactionManager (HibernateTransactionManager) instance.

So, it seems that micronaut does not support using multiple JPA and non-JPA transactional datasources in a single application.

I am investigating whether I can create my own TransactionManagerFactory similar to the io.micronaut.configuration.hibernate.jpa.spring.HibernateTransactionManagerFactory in the micronaut-hibernate-jpa-spring library.

The plan is to create io.micronaut.transaction.jdbc.DataSourceTransactionManager instances for the non-JPA data sources. The only challenge I forsee is the JPA-enabled data source which I would need to skip somehow since a HibernateTransactionManager instance will already be created for this.

[graemerocher]

Interesting problem, yes you analysis is correct. I think it could be possible to fix this with injecting by @Order priority in Micronaut 3.0

[marcusportmann]

For context, I am attempting to get Micronaut to support both multiple data sources and JTA transactions. The intention is to have support for XA two-phase commit across multiple data sources and other XA resources.

I have successfully integrated Agroal, an XA compliant connection pool, and Narayana, the JTA transaction manager from JBoss. I based my implementation on the Spring JtaTransactionManager implementation. My implementation supports multiple data sources, mixing XA and non-XA data sources, and configures Narayana to support the recovery of XA transactions in the case of an application failure.

I have a sample application that demonstrates how this works and a library that provides the same capability. The library is part of an effort to migrate my Inception framework to Micronaut, which is currently based on Spring and Angular.

Sample Application: https://github.com/marcusportmann/appnaut-poc

Library: https://github.com/marcusportmann/appnaut/tree/main/src/appnaut-jta

So far, I am making good progress in porting my framework. The next challenge is enabling support for multiple JPA persistence units to allow the correct scoping of entity beans and prevent issues with application entity beans conflicting with framework entity beans with the same name.

[graemerocher]

Nice work, could make a nice contribution to https://github.com/micronaut-projects/micronaut-sql