Failed to retrieve a working connection from transactional data source

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?