How to configure a datasource programmatically

I'd like to know how can I configure a datasource programmatically withtout using application.properties.

Spring Boot way:

@Configuration
public class KubDataSourceConfig {

  @Bean
  public DataSource getDataSource() {

    Map<String, String> credentials = getCredentials();

    return DataSourceBuilder.create()
            .url(credentials.get("url"))
            .username(credentials.get("username"))
            .password(credentials.get("password"))
            .build();
  }
}

While there isn't a way to do this similar to Spring Boot, you can achieve that with this workaround.

Create a class that implements javax.sql.DataSource, just like below:

`
@ApplicationScoped
public class CustomDataSource extends OracleDataSource {

private static final long serialVersionUID = 1L;

public CustomDataSource() throws SQLException {
	super();
}

@Override
public Connection getConnection() throws SQLException {
    OracleDataSource oracleRootSource = new OracleDataSource();
    oracleRootSource.setServerName("hostname");
    oracleRootSource.setServiceName("sid");
    oracleRootSource.setPortNumber(1521);
    oracleRootSource.setDriverType("thin");
    oracleRootSource.setUser("user");
    oracleRootSource.setPassword("pass");
    return oracleRootSource.getConnection();
}

}
`

It's not that easy, but you can create an AgroalDataSource directly

// create supplier
AgroalDataSourceConfigurationSupplier dataSourceConfiguration = new AgroalDataSourceConfigurationSupplier();
// get reference to connection pool
AgroalConnectionPoolConfigurationSupplier poolConfiguration = dataSourceConfiguration.connectionPoolConfiguration();
// get reference to connection factory
AgroalConnectionFactoryConfigurationSupplier connectionFactoryConfiguration = poolConfiguration.connectionFactoryConfiguration();

// configure pool
poolConfiguration
  .initialSize(10)
  .maxSize(10)
  .minSize(10)
  .maxLifetime(Duration.of(5, ChronoUnit.MINUTES))
  .acquisitionTimeout(Duration.of(30, ChronoUnit.SECONDS));

// configure supplier
connectionFactoryConfiguration
  .jdbcUrl("jdbcUrl")
  .credential(new NamePrincipal("username"))
  .credential(new SimplePassword("password"));

AgroalDataSource datasource=AgroalDataSource.from(dataSourceConfiguration.get());

This can all be collapsed to a couple lines, just broke it apart to see the pieces

There's a DataSources class in Quarkus that looks like it should be able to be invoked programtically, but it expects some properties to be set at build time.

You can also use an AgroalPropertiesReader

Map<String,String> props=new HashMap<>();

props.put(AgroalPropertiesReader.MAX_SIZE,"10");
props.put(AgroalPropertiesReader.MIN_SIZE,"10");
props.put(AgroalPropertiesReader.INITIAL_SIZE,"10");
props.put(AgroalPropertiesReader.MAX_LIFETIME_S,"300");
props.put(AgroalPropertiesReader.ACQUISITION_TIMEOUT_S,"30");
props.put(AgroalPropertiesReader.JDBC_URL,"jdbcUrl");
props.put(AgroalPropertiesReader.PRINCIPAL,"username");
props.put(AgroalPropertiesReader.CREDENTIAL,"password");

AgroalDataSource datasource = AgroalDataSource.from(new AgroalPropertiesReader()
  .readProperties(props)
  .get());

We're experiencing leaks of both Driver and Connection instances : instances only, the db itself has no opened leaks ; i.e, .close() seems fine.

Heap dump reveals several hundreds of MB retained by both Driver and Connection.

We're also programmatically creating instances of AgroalConnectionFactoryConfigurationSupplier() at runtime, because its .jdbcurl(xxx) where in our case, xxx is dynamic per tenant

Is the supplier supposed to be leak safe regarding the Driver and is creating instances of suppliers the proper way to do it ? Looking at https://github.com/agroal/agroal/blob/master/agroal-spring-boot-starter/src/main/java/io/agroal/springframework/boot/AgroalDataSource.java there is only one supplier at InitiliazingBean

And https://github.com/agroal/agroal/blob/master/agroal-spring-boot-starter/src/main/java/io/agroal/springframework/boot/AgroalDataSource.java#L206 has an explicite delegate = null after the .close() to let the GC collect it - I presume ? This null is curious I don't understand it.

Should we retain a single supplier instance and set its .jdbcurl(xxx) where xxx is a moving multi tenant url ?

@barreiro maybe ?

Hi guys, were you able to progressed in this issue? We are interested in building an AgroalDatasource dynamically at runtime because we will be fetching the DB configurations from AWS parameter store. Currently, I'm able to set the credentials using credentialsProvider but not the jdbc.url as there is no provider for it. Most likely we need the AgroalConnectionFactoryConfigurationSupplier as well.

Not sure if it interests anyone but I whipped up an example of how do to this. It's not as straightforward as it maybe ought to be but is definitely doable. In addition, perhaps the docs could be extended to include concrete examples of implementing TenantConnectionResolver?

Hi All,

I created a class MyTenantConnectionResolver implements TenantConnectionResolver. It creates datasource on runtime as follow:

Map<String, String> props = new HashMap<>();

props.put(AgroalPropertiesReader.MAX_SIZE, "10");
props.put(AgroalPropertiesReader.MIN_SIZE, "2");
props.put(AgroalPropertiesReader.INITIAL_SIZE, "2");
props.put(AgroalPropertiesReader.MAX_LIFETIME_S, "57");
props.put(AgroalPropertiesReader.ACQUISITION_TIMEOUT_S, "54");
props.put(AgroalPropertiesReader.PROVIDER_CLASS_NAME, "org.mariadb.jdbc.Driver");
props.put(AgroalPropertiesReader.JDBC_URL, "jdbc:mariadb://localhost:3306/mydb_" + tenantId);
props.put(AgroalPropertiesReader.PRINCIPAL, "myusername");
props.put(AgroalPropertiesReader.CREDENTIAL, "mypassword");

AgroalDataSource datasource = AgroalDataSource.from(new AgroalPropertiesReader()
					  .readProperties(props)
					  .get());

However the transaction magement using @Transactional annotation is broken. The transaction is not rolled back when there is RuntimeException.

Anyone having the same problem?

Hi @jingglang, without any more information there's not a whole lot that any of us can do to offer assistance other than take wild guesses at what is going wrong. Programatic datasource transactions work (at least for me) just the same as if I had configured the datasource at build-time.

Thank you @alexkolson
I just created a small project that can replicate my problem.

quarkus_rollback_issue

This is a snippet code that throws the RuntimeException but the transaction still committed:

@GET
@Path("create_error/{name}")
@Transactional
public List<Country> createCountryError(@PathParam("name") String name) {
	countryService.createCountry(name);
	if (true) {
		throw new RuntimeException("Testing database rollback");
	}
	return countryService.findCountries();
}

Thanks again

Hi @jingglang

Thanks for the reproducer! I am seeing the behavior you describe. With multi-tenancy enabled, there appears to be different behavior when it comes to rollback. When using a non multi-tenant configuration, a rollback is executing without issue. Specifically in your reproducer, it appears to be related to the fact that Country has a database-generated Id and hibernate makes an insert immediately (regardless of transaction) to get the id value. That's fine and expected behavior as far as I'm aware. What's not fine is that with the multi-tenant datasource setup never issues a ROLLBACK to remove the inserted record when the exception is thrown, whereas the non-multi-tenant datasource setup does.

I've included the (hopefully) relevant trace logs for comparison and as documentation. Hopefully the Quarkus team has some suggestions!

With multi-tenancy

2023-04-17 00:10:02,167 DEBUG [org.hib.id.IdentifierGeneratorHelper] (executor-thread-0) Natively generated identity: 12
2023-04-17 00:10:02,167 TRACE [org.hib.res.jdb.int.ResourceRegistryStandardImpl] (executor-thread-0) Releasing result set [io.agroal.pool.wrapper.ResultSetWrapper@35b52da0]
2023-04-17 00:10:02,168 TRACE [org.hib.res.jdb.int.ResourceRegistryStandardImpl] (executor-thread-0) Closing result set [io.agroal.pool.wrapper.ResultSetWrapper@35b52da0]
2023-04-17 00:10:02,168 TRACE [org.hib.res.jdb.int.ResourceRegistryStandardImpl] (executor-thread-0) Releasing statement [wrapped[ ClientPreparedStatement{sql:'insert into country (name) values (?)', parameters:['salsa']} ]]
2023-04-17 00:10:02,168 DEBUG [org.hib.res.jdb.int.ResourceRegistryStandardImpl] (executor-thread-0) HHH000387: ResultSet's statement was not registered
2023-04-17 00:10:02,168 TRACE [org.hib.res.jdb.int.ResourceRegistryStandardImpl] (executor-thread-0) Closing prepared statement [wrapped[ ClientPreparedStatement{sql:'insert into country (name) values (?)', parameters:['salsa']} ]]
2023-04-17 00:10:02,168 TRACE [org.hib.eng.jdb.int.JdbcCoordinatorImpl] (executor-thread-0) Starting after statement execution processing [BEFORE_TRANSACTION_COMPLETION]
2023-04-17 00:10:02,168 DEBUG [org.hib.cac.int.TimestampsCacheEnabledImpl] (executor-thread-0) Pre-invalidating space [country], timestamp: 1681683062168
2023-04-17 00:10:02,168 TRACE [org.inf.qua.hib.cac.CaffeineCache] (executor-thread-0) Cache put key=country value=1681683062168
2023-04-17 00:10:02,168 TRACE [com.arj.ats.jta] (executor-thread-0) TransactionImple.setRollbackOnly
2023-04-17 00:10:02,168 TRACE [com.arj.ats.arjuna] (executor-thread-0) BasicAction::preventCommit( BasicAction: 0:ffffc0a8b22d:cd51:643c71ec:6 status: ActionStatus.RUNNING)
2023-04-17 00:10:02,169 TRACE [com.arj.ats.jta] (executor-thread-0) TransactionImple.getStatus: javax.transaction.Status.STATUS_MARKED_ROLLBACK
2023-04-17 00:10:02,169 TRACE [com.arj.ats.jta] (executor-thread-0) BaseTransaction.rollback
2023-04-17 00:10:02,169 TRACE [com.arj.ats.jta] (executor-thread-0) TransactionImple.rollbackAndDisassociate
2023-04-17 00:10:02,169 TRACE [com.arj.ats.arjuna] (executor-thread-0) BasicAction::Abort() for action-id 0:ffffc0a8b22d:cd51:643c71ec:6
2023-04-17 00:10:02,169 TRACE [com.arj.ats.jta] (executor-thread-0) SynchronizationImple.afterCompletion - Class: class org.hibernate.resource.transaction.backend.jta.internal.synchronization.RegisteredSynchronization HashCode: 1231655981 toString: org.hibernate.resource.transaction.backend.jta.internal.synchronization.RegisteredSynchronization@4969942d
2023-04-17 00:10:02,169 TRACE [org.hib.res.tra.bac.jta.int.syn.RegisteredSynchronization] (executor-thread-0) Registered JTA Synchronization : afterCompletion(4)
2023-04-17 00:10:02,169 TRACE [org.hib.res.tra.bac.jta.int.syn.SynchronizationCallbackCoordinatorTrackingImpl] (executor-thread-0) Synchronization coordinator: afterCompletion(status=4)
2023-04-17 00:10:02,169 TRACE [org.hib.res.tra.bac.jta.int.syn.SynchronizationCallbackCoordinatorNonTrackingImpl] (executor-thread-0) Synchronization coordinator: doAfterCompletion(successful=false, delayed=false)
2023-04-17 00:10:02,169 TRACE [org.hib.res.tra.int.SynchronizationRegistryStandardImpl] (executor-thread-0) SynchronizationRegistryStandardImpl.notifySynchronizationsAfterTransactionCompletion(5)
2023-04-17 00:10:02,169 TRACE [org.hib.res.jdb.int.AbstractLogicalConnectionImplementor] (executor-thread-0) LogicalConnection#afterTransaction
2023-04-17 00:10:02,170 TRACE [org.hib.res.jdb.int.ResourceRegistryStandardImpl] (executor-thread-0) Releasing JDBC resources
2023-04-17 00:10:02,170 DEBUG [org.hib.res.jdb.int.LogicalConnectionManagedImpl] (executor-thread-0) Initiating JDBC connection release from afterTransaction
2023-04-17 00:10:02,170 TRACE [org.hib.res.jdb.int.ResourceRegistryStandardImpl] (executor-thread-0) Releasing JDBC resources
2023-04-17 00:10:02,170 DEBUG [io.qua.hib.orm.run.ten.HibernateMultiTenantConnectionProvider] (executor-thread-0) selectConnectionProvider(persistenceUnitName=<default>, tenantIdentifier=0)
2023-04-17 00:10:02,170 TRACE [org.hib.int.SessionImpl] (executor-thread-0) SessionImpl#afterTransactionCompletion(successful=false, delayed=false)
2023-04-17 00:10:02,170 DEBUG [org.hib.cac.int.TimestampsCacheEnabledImpl] (executor-thread-0) Invalidating space [country], timestamp: 1681683002170
2023-04-17 00:10:02,170 TRACE [org.inf.qua.hib.cac.CaffeineCache] (executor-thread-0) Cache put key=country value=1681683002170
2023-04-17 00:10:02,170 DEBUG [org.hib.eng.tra.int.TransactionImpl] (executor-thread-0) On TransactionImpl creation, JpaCompliance#isJpaTransactionComplianceEnabled == false
2023-04-17 00:10:02,170 TRACE [org.hib.int.SessionImpl] (executor-thread-0) Automatically closing session
2023-04-17 00:10:02,170 TRACE [org.hib.int.SessionImpl] (executor-thread-0) Closing session [52b62145-cd21-42f6-9bb4-f32824bc9563]
2023-04-17 00:10:02,171 TRACE [com.arj.ats.jta] (executor-thread-0) TransactionImple.getStatus: javax.transaction.Status.STATUS_ROLLEDBACK
2023-04-17 00:10:02,171 TRACE [org.hib.eng.jdb.int.JdbcCoordinatorImpl] (executor-thread-0) Closing JDBC container [org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl@30781fc7]
2023-04-17 00:10:02,171 TRACE [org.hib.res.jdb.int.ResourceRegistryStandardImpl] (executor-thread-0) Releasing JDBC resources
2023-04-17 00:10:02,171 TRACE [org.hib.res.jdb.int.LogicalConnectionManagedImpl] (executor-thread-0) Closing logical connection
2023-04-17 00:10:02,171 TRACE [org.hib.res.jdb.int.LogicalConnectionManagedImpl] (executor-thread-0) Logical connection closed
2023-04-17 00:10:02,171 TRACE [org.inf.qua.hib.cac.Sync] (executor-thread-0) 0 tasks done, 0 tasks not done yet
2023-04-17 00:10:02,171 TRACE [org.inf.qua.hib.cac.Sync] (executor-thread-0) Finished 0 tasks before completion
2023-04-17 00:10:02,171 TRACE [org.inf.qua.hib.cac.Sync] (executor-thread-0) Invoked 0 tasks after completion, 0 are synchronous.
2023-04-17 00:10:02,171 TRACE [com.arj.ats.arjuna] (executor-thread-0) BasicAction::removeChildThread () action 0:ffffc0a8b22d:cd51:643c71ec:6 removing 3
2023-04-17 00:10:02,171 TRACE [com.arj.ats.arjuna] (executor-thread-0) BasicAction::removeChildThread () action 0:ffffc0a8b22d:cd51:643c71ec:6 removing 3 result = true
2023-04-17 00:10:02,171 TRACE [com.arj.ats.arjuna] (executor-thread-0) TransactionReaper::remove ( BasicAction: 0:ffffc0a8b22d:cd51:643c71ec:6 status: ActionStatus.ABORTED )

Without multi-tenancy

2023-04-17 00:43:26,590 DEBUG [org.hib.id.IdentifierGeneratorHelper] (executor-thread-0) Natively generated identity: 25
2023-04-17 00:43:26,590 TRACE [org.hib.res.jdb.int.ResourceRegistryStandardImpl] (executor-thread-0) Releasing result set [io.agroal.pool.wrapper.ResultSetWrapper@c54acc4]
2023-04-17 00:43:26,591 TRACE [org.hib.res.jdb.int.ResourceRegistryStandardImpl] (executor-thread-0) Closing result set [io.agroal.pool.wrapper.ResultSetWrapper@c54acc4]
2023-04-17 00:43:26,591 TRACE [org.hib.res.jdb.int.ResourceRegistryStandardImpl] (executor-thread-0) Releasing statement [wrapped[ ClientPreparedStatement{sql:'insert into country (name) values (?)', parameters:['moreFoo']} ]]
2023-04-17 00:43:26,591 DEBUG [org.hib.res.jdb.int.ResourceRegistryStandardImpl] (executor-thread-0) HHH000387: ResultSet's statement was not registered
2023-04-17 00:43:26,591 TRACE [org.hib.res.jdb.int.ResourceRegistryStandardImpl] (executor-thread-0) Closing prepared statement [wrapped[ ClientPreparedStatement{sql:'insert into country (name) values (?)', parameters:['moreFoo']} ]]
2023-04-17 00:43:26,591 TRACE [org.hib.eng.jdb.int.JdbcCoordinatorImpl] (executor-thread-0) Starting after statement execution processing [BEFORE_TRANSACTION_COMPLETION]
2023-04-17 00:43:26,593 DEBUG [org.hib.cac.int.TimestampsCacheEnabledImpl] (executor-thread-0) Pre-invalidating space [country], timestamp: 1681685066593
2023-04-17 00:43:26,593 TRACE [org.inf.qua.hib.cac.CaffeineCache] (executor-thread-0) Cache put key=country value=1681685066593
2023-04-17 00:43:26,596 TRACE [com.arj.ats.jta] (executor-thread-0) TransactionImple.setRollbackOnly
2023-04-17 00:43:26,596 TRACE [com.arj.ats.arjuna] (executor-thread-0) BasicAction::preventCommit( BasicAction: 0:ffffc0a8b22d:cea2:643c7a0e:0 status: ActionStatus.RUNNING)
2023-04-17 00:43:26,596 TRACE [com.arj.ats.jta] (executor-thread-0) TransactionImple.getStatus: javax.transaction.Status.STATUS_MARKED_ROLLBACK
2023-04-17 00:43:26,596 TRACE [com.arj.ats.jta] (executor-thread-0) BaseTransaction.rollback
2023-04-17 00:43:26,597 TRACE [com.arj.ats.jta] (executor-thread-0) TransactionImple.rollbackAndDisassociate
2023-04-17 00:43:26,597 TRACE [com.arj.ats.arjuna] (executor-thread-0) BasicAction::Abort() for action-id 0:ffffc0a8b22d:cea2:643c7a0e:0
2023-04-17 00:43:26,603 TRACE [com.arj.ats.arjuna] (executor-thread-0) BasicAction::doAbort (XAResourceRecord < resource:io.agroal.narayana.LocalXAResource@54d29cd4, txid:< formatId=131077, gtrid_length=35, bqual_length=36, tx_uid=0:ffffc0a8b22d:cea2:643c7a0e:0, node_name=quarkus, branch_uid=0:ffffc0a8b22d:cea2:643c7a0e:3, subordinatenodename=null, eis_name=0 >, heuristic: TwoPhaseOutcome.FINISH_OK com.arjuna.ats.internal.jta.resources.arjunacore.XAResourceRecord@1d7f324e >)
2023-04-17 00:43:26,604 TRACE [com.arj.ats.jta] (executor-thread-0) XAResourceRecord.topLevelAbort for XAResourceRecord < resource:io.agroal.narayana.LocalXAResource@54d29cd4, txid:< formatId=131077, gtrid_length=35, bqual_length=36, tx_uid=0:ffffc0a8b22d:cea2:643c7a0e:0, node_name=quarkus, branch_uid=0:ffffc0a8b22d:cea2:643c7a0e:3, subordinatenodename=null, eis_name=0 >, heuristic: TwoPhaseOutcome.FINISH_OK com.arjuna.ats.internal.jta.resources.arjunacore.XAResourceRecord@1d7f324e >, record id=0:ffffc0a8b22d:cea2:643c7a0e:4
2023-04-17 00:43:26,604 TRACE [com.arj.ats.jta] (executor-thread-0) TransactionImple.getStatus: javax.transaction.Status.STATUS_ROLLING_BACK
2023-04-17 00:43:26,605 DEBUG [org.mar.jdb.cli.imp.StandardClient] (executor-thread-0) execute query: ROLLBACK
2023-04-17 00:43:26,605 TRACE [org.mar.jdb.cli.soc.imp.PacketWriter] (executor-thread-0) send: conn=229 (M)
       +--------------------------------------------------+
       |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+------+--------------------------------------------------+------------------+
|000000| 09 00 00 00 03 52 4F 4C  4C 42 41 43 4B          | .....ROLLBACK    |
+------+--------------------------------------------------+------------------+


2023-04-17 00:43:26,613 TRACE [org.mar.jdb.cli.soc.imp.PacketReader] (executor-thread-0) read: conn=229 (M)
       +--------------------------------------------------+
       |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+------+--------------------------------------------------+------------------+
|000000| 07 00 00 01 00 00 00 00  00 00 00                | ...........      |
+------+--------------------------------------------------+------------------+


2023-04-17 00:43:26,613 TRACE [com.arj.ats.arjuna] (executor-thread-0) BasicAction::doAbort() result for action-id (0:ffffc0a8b22d:cea2:643c7a0e:0) on record id: (0:ffffc0a8b22d:cea2:643c7a0e:4) is (TwoPhaseOutcome.FINISH_OK) node id: (quarkus)
2023-04-17 00:43:26,614 TRACE [com.arj.ats.jta] (executor-thread-0) SynchronizationImple.afterCompletion - Class: class io.agroal.narayana.NarayanaTransactionIntegration$InterposedSynchronization HashCode: 223149229 toString: io.agroal.narayana.NarayanaTransactionIntegration$InterposedSynchronization@d4cfcad
2023-04-17 00:43:26,615 TRACE [com.arj.ats.jta] (executor-thread-0) TransactionImple.getStatus: javax.transaction.Status.STATUS_ROLLEDBACK
2023-04-17 00:43:26,615 DEBUG [org.mar.jdb.cli.imp.StandardClient] (executor-thread-0) execute query: set autocommit=1
2023-04-17 00:43:26,615 TRACE [org.mar.jdb.cli.soc.imp.PacketWriter] (executor-thread-0) send: conn=229 (M)
       +--------------------------------------------------+
       |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+------+--------------------------------------------------+------------------+
|000000| 11 00 00 00 03 73 65 74  20 61 75 74 6F 63 6F 6D | .....set autocom |
|000010| 6D 69 74 3D 31                                   | mit=1            |
+------+--------------------------------------------------+------------------+


2023-04-17 00:43:26,617 TRACE [org.mar.jdb.cli.soc.imp.PacketReader] (executor-thread-0) read: conn=229 (M)
       +--------------------------------------------------+
       |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+------+--------------------------------------------------+------------------+
|000000| 19 00 00 01 00 00 00 02  40 00 00 00 10 00 0E 0A | ........@....... |
|000010| 61 75 74 6F 63 6F 6D 6D  69 74 02 4F 4E          | autocommit.ON    |
+------+--------------------------------------------------+------------------+


2023-04-17 00:43:26,617 DEBUG [org.mar.jdb.mes.ser.OkPacket] (executor-thread-0) System variable change:  autocommit = ON
2023-04-17 00:43:26,618 TRACE [io.agr.pool] (executor-thread-0) Datasource '<default>': Returning connection org.mariadb.jdbc.Connection@49ac8797
2023-04-17 00:43:26,618 TRACE [com.arj.ats.jta] (executor-thread-0) SynchronizationImple.afterCompletion - Class: class org.hibernate.resource.transaction.backend.jta.internal.synchronization.RegisteredSynchronization HashCode: 1240377679 toString: org.hibernate.resource.transaction.backend.jta.internal.synchronization.RegisteredSynchronization@49eea94f
2023-04-17 00:43:26,618 TRACE [org.hib.res.tra.bac.jta.int.syn.RegisteredSynchronization] (executor-thread-0) Registered JTA Synchronization : afterCompletion(4)
2023-04-17 00:43:26,619 TRACE [org.hib.res.tra.bac.jta.int.syn.SynchronizationCallbackCoordinatorTrackingImpl] (executor-thread-0) Synchronization coordinator: afterCompletion(status=4)
2023-04-17 00:43:26,619 TRACE [org.hib.res.tra.bac.jta.int.syn.SynchronizationCallbackCoordinatorNonTrackingImpl] (executor-thread-0) Synchronization coordinator: doAfterCompletion(successful=false, delayed=false)
2023-04-17 00:43:26,619 TRACE [org.hib.res.tra.int.SynchronizationRegistryStandardImpl] (executor-thread-0) SynchronizationRegistryStandardImpl.notifySynchronizationsAfterTransactionCompletion(5)
2023-04-17 00:43:26,619 TRACE [org.hib.res.jdb.int.AbstractLogicalConnectionImplementor] (executor-thread-0) LogicalConnection#afterTransaction
2023-04-17 00:43:26,619 TRACE [org.hib.res.jdb.int.ResourceRegistryStandardImpl] (executor-thread-0) Releasing JDBC resources
2023-04-17 00:43:26,620 DEBUG [org.hib.res.jdb.int.LogicalConnectionManagedImpl] (executor-thread-0) Initiating JDBC connection release from afterTransaction
2023-04-17 00:43:26,620 TRACE [org.hib.res.jdb.int.ResourceRegistryStandardImpl] (executor-thread-0) Releasing JDBC resources
2023-04-17 00:43:26,620 TRACE [org.hib.int.SessionImpl] (executor-thread-0) SessionImpl#afterTransactionCompletion(successful=false, delayed=false)
2023-04-17 00:43:26,622 DEBUG [org.hib.cac.int.TimestampsCacheEnabledImpl] (executor-thread-0) Invalidating space [country], timestamp: 1681685006622
2023-04-17 00:43:26,623 TRACE [org.inf.qua.hib.cac.CaffeineCache] (executor-thread-0) Cache put key=country value=1681685006622
2023-04-17 00:43:26,625 DEBUG [org.hib.eng.tra.int.TransactionImpl] (executor-thread-0) On TransactionImpl creation, JpaCompliance#isJpaTransactionComplianceEnabled == false
2023-04-17 00:43:26,625 TRACE [org.hib.int.SessionImpl] (executor-thread-0) Automatically closing session
2023-04-17 00:43:26,625 TRACE [org.hib.int.SessionImpl] (executor-thread-0) Closing session [162a4393-53c4-4153-bbd1-d3964dcde494]
2023-04-17 00:43:26,626 TRACE [com.arj.ats.jta] (executor-thread-0) TransactionImple.getStatus: javax.transaction.Status.STATUS_ROLLEDBACK
2023-04-17 00:43:26,626 TRACE [org.hib.eng.jdb.int.JdbcCoordinatorImpl] (executor-thread-0) Closing JDBC container [org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl@3210cffb]
2023-04-17 00:43:26,626 TRACE [org.hib.res.jdb.int.ResourceRegistryStandardImpl] (executor-thread-0) Releasing JDBC resources
2023-04-17 00:43:26,626 TRACE [org.hib.res.jdb.int.LogicalConnectionManagedImpl] (executor-thread-0) Closing logical connection
2023-04-17 00:43:26,626 TRACE [org.hib.res.jdb.int.LogicalConnectionManagedImpl] (executor-thread-0) Logical connection closed
2023-04-17 00:43:26,627 TRACE [org.inf.qua.hib.cac.Sync] (executor-thread-0) 0 tasks done, 0 tasks not done yet
2023-04-17 00:43:26,627 TRACE [org.inf.qua.hib.cac.Sync] (executor-thread-0) Finished 0 tasks before completion
2023-04-17 00:43:26,627 TRACE [org.inf.qua.hib.cac.Sync] (executor-thread-0) Invoked 0 tasks after completion, 0 are synchronous.
2023-04-17 00:43:26,628 TRACE [com.arj.ats.arjuna] (executor-thread-0) BasicAction::removeChildThread () action 0:ffffc0a8b22d:cea2:643c7a0e:0 removing 1
2023-04-17 00:43:26,628 TRACE [com.arj.ats.arjuna] (executor-thread-0) BasicAction::removeChildThread () action 0:ffffc0a8b22d:cea2:643c7a0e:0 removing 1 result = true
2023-04-17 00:43:26,628 TRACE [com.arj.ats.arjuna] (executor-thread-0) TransactionReaper::remove ( BasicAction: 0:ffffc0a8b22d:cea2:643c7a0e:0 status: ActionStatus.ABORTED )

Upon further investigation, it seems there might be some additional plumbing one has to do when programmatically creating datasources. I'm still taking a look but it seems transaction integration might need to manually be set up when programmatically creating datasources:

quarkus/extensions/agroal/runtime/src/main/java/io/quarkus/agroal/runtime/DataSources.java

Line 285 in a4f329b

    
           if (dataSourceJdbcBuildTimeConfig.transactions != io.quarkus.agroal.runtime.TransactionIntegration.DISABLED) {

Hello @alexkolson
Your clue was spot on! I get the ROLLBACK by specifying NarayaTransactionIntegration to runtime-created agroal datasource.

@ApplicationScoped
@PersistenceUnitExtension
public class MyTenantConnectionResolver implements TenantConnectionResolver {
  private Logger log = Logger.getLogger(MyTenantConnectionResolver.class);
  @ConfigProperty(name = "quarkus.datasource.username")
  String username;
  @ConfigProperty(name = "quarkus.datasource.password")
  String password;
  @Inject
  TransactionManager transactionManager;
  @Inject
  TransactionSynchronizationRegistry transactionSynchronizationRegistry;

  @Override
  public ConnectionProvider resolve(String tenantId) {
    return new QuarkusConnectionProvider(createDatasource(tenantId));
  }

  private AgroalDataSource createDatasource(String tenantId) {

    try {

      AgroalDataSourceConfigurationSupplier dataSourceConfiguration = new AgroalDataSourceConfigurationSupplier();

      AgroalConnectionPoolConfigurationSupplier poolConfiguration = dataSourceConfiguration.connectionPoolConfiguration();

      TransactionIntegration txIntegration = new NarayanaTransactionIntegration(transactionManager, transactionSynchronizationRegistry, null, false, null);
      poolConfiguration
            .initialSize(2)
            .maxSize(10)
            .minSize(2)
            .maxLifetime(Duration.of(5, ChronoUnit.MINUTES))
            .acquisitionTimeout(Duration.of(30, ChronoUnit.SECONDS))
            .transactionIntegration(txIntegration); //This part, specify transaction integration

      AgroalConnectionFactoryConfigurationSupplier connectionFactoryConfiguration = poolConfiguration.connectionFactoryConfiguration();

      connectionFactoryConfiguration
            .jdbcUrl("jdbc:mariadb://localhost:3306/country_" + tenantId)
            .credential(new NamePrincipal(username))
            .credential(new SimplePassword(password));


      AgroalDataSource datasource = AgroalDataSource.from(dataSourceConfiguration.get());
      return datasource;
    } catch (SQLException ex) {
      throw new IllegalStateException("Failed to create a new data source based on the existing datasource configuration", ex);
    }
  }

}

Thank you,

Hey @jingglang! I'm really glad you were able to get transaction integration working with runtime datasources!

I have updated my example so that it demonstrates programmatically creating datasources that support transactions.

I'm also interesting in this type of behavior but without the use of hibernate. In order to take advantage of all the existing agroal plumbing as well as automatic dev services, I wonder if it'd be possible for the url config to be settable by some provider.

Imagine if quarkus.datasource."datasource-name".jdbc.url or quarkus.datasource.jdbc.url were optional but there was also a quarkus.datasource."datasource-name".jdbc.urlProvider or quarkus.datasource.jdbc.urlProvider where the providers would allow for say the creation of @RequestScoped data sources where the provider can generate the url when needed base on an inbound request

@kpagratis I think that warrants it's own specific issue as it's much more focused than the general of this issue (which I highly doubt we'll provide any time soon)