Problem with declarative transactions and TransactionAwareDataSourceProxy combined with JOOQ

Question

I have a data source configuration class that looks like this, with a separate DataSource beans for test and non-test environments using JOOQ. In my code, I do not use DSLContext.transaction(ctx -> {...} , but rather mark the method as transactional, so that JOOQ refuses Spring declarative transactions for the transactional transaction. I use Spring 4.3.7.RELEASE .

I have the following problem:

• During testing (JUnit), @Transactional works as @Transactional . One method is transactional no matter how many times I use the DSLContext store() method, and a RuntimeException triggers the rollback of the entire transaction.
• During the actual project @Transactional completely ignored. The method is no longer transactional, and TransactionSynchronizationManager.getResourceMap() contains two separate values: one shows my connection pool (which is not transactional), and one shows TransactionAwareDataSourceProxy ).

In this case, I would expect only one resource of type TransactionAwareDataSourceProxy , which transfers my DB CP.

• After much trial and error using the second set of configuration changes I made (“AFTER” below), @Transactional works correctly as expected even at runtime, although TransactionSynchronizationManager.getResourceMap() has the following meaning:

In this case, my DataSourceTransactionManager doesn't seem to even know TransactionAwareDataSourceProxy (most likely due to the fact that I passed it a simple DataSource , not a proxy object), which seems to completely skip the proxy server anyway.

My question is: the initial configuration, which I seemed correct, but did not work. The proposed “fix” works, but the IMO should not work at all (since the transaction manager does not seem to know about TransactionAwareDataSourceProxy ).

What's going on here? Is there a cleaner way to solve this problem?

BEFORE (not a transaction at run time)

 @Configuration @EnableTransactionManagement @RefreshScope @Slf4j public class DataSourceConfig { @Bean @Primary public DSLContext dslContext(org.jooq.Configuration configuration) throws SQLException { return new DefaultDSLContext(configuration); } @Bean @Primary public org.jooq.Configuration defaultConfiguration(DataSourceConnectionProvider dataSourceConnectionProvider) { org.jooq.Configuration configuration = new DefaultConfiguration() .derive(dataSourceConnectionProvider) .derive(SQLDialect.POSTGRES_9_5); configuration.set(new DeleteOrUpdateWithoutWhereListener()); return configuration; } @Bean public DataSourceTransactionManager transactionManager(DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } @Bean public DataSourceConnectionProvider dataSourceConnectionProvider(DataSource dataSource) { return new DataSourceConnectionProvider(dataSource); } @Configuration @ConditionalOnClass(EmbeddedPostgres.class) static class EmbeddedDataSourceConfig { @Value("${spring.jdbc.port}") private int dbPort; @Bean(destroyMethod = "close") public EmbeddedPostgres embeddedPostgres() throws Exception { EmbeddedPostgres embeddedPostgres = EmbeddedPostgresHelper.startDatabase(dbPort); return embeddedPostgres; } @Bean @Primary public DataSource dataSource(EmbeddedPostgres embeddedPostgres) throws Exception { DataSource dataSource = embeddedPostgres.getPostgresDatabase(); return new TransactionAwareDataSourceProxy(dataSource); } } @Configuration @ConditionalOnMissingClass("com.opentable.db.postgres.embedded.EmbeddedPostgres") @RefreshScope static class DefaultDataSourceConfig { @Value("${spring.jdbc.url}") private String url; @Value("${spring.jdbc.username}") private String username; @Value("${spring.jdbc.password}") private String password; @Value("${spring.jdbc.driverClass}") private String driverClass; @Value("${spring.jdbc.MaximumPoolSize}") private Integer maxPoolSize; @Bean @Primary @RefreshScope public DataSource dataSource() { log.debug("Connecting to datasource: {}", url); HikariConfig hikariConfig = buildPool(); DataSource dataSource = new HikariDataSource(hikariConfig); return new TransactionAwareDataSourceProxy(dataSource); } private HikariConfig buildPool() { HikariConfig config = new HikariConfig(); config.setJdbcUrl(url); config.setUsername(username); config.setPassword(password); config.setDriverClassName(driverClass); config.setConnectionTestQuery("SELECT 1"); config.setMaximumPoolSize(maxPoolSize); return config; } } 

AFTER (a run-time transaction, as expected, all non-listed beans are identical above)

 @Configuration @EnableTransactionManagement @RefreshScope @Slf4j public class DataSourceConfig { @Bean public DataSourceConnectionProvider dataSourceConnectionProvider(TransactionAwareDataSourceProxy dataSourceProxy) { return new DataSourceConnectionProvider(dataSourceProxy); } @Bean public TransactionAwareDataSourceProxy transactionAwareDataSourceProxy(DataSource dataSource) { return new TransactionAwareDataSourceProxy(dataSource); } @Configuration @ConditionalOnMissingClass("com.opentable.db.postgres.embedded.EmbeddedPostgres") @RefreshScope static class DefaultDataSourceConfig { @Value("${spring.jdbc.url}") private String url; @Value("${spring.jdbc.username}") private String username; @Value("${spring.jdbc.password}") private String password; @Value("${spring.jdbc.driverClass}") private String driverClass; @Value("${spring.jdbc.MaximumPoolSize}") private Integer maxPoolSize; @Bean @Primary @RefreshScope public DataSource dataSource() { log.debug("Connecting to datasource: {}", url); HikariConfig hikariConfig = buildPoolConfig(); DataSource dataSource = new HikariDataSource(hikariConfig); return dataSource; // not returning the proxy here } } } 

Answer 1

I will reply my comments in response.

The transaction manager does NOT need to know the proxy. From the documentation :

Note that a transaction manager, such as a DataSourceTransactionManager, still needs to work with the underlying DataSource, and not with this proxy.

The TransactionAwareDataSourceProxy class is a special-purpose class that is not required in most cases. Everything that interacts with your data source through the Spring infrastructure infrastructure must NOT have a proxy in its access chain. A proxy server is for code that cannot communicate with the Spring framework. For example, a third-party library that is already configured to work with JDBC and has not accepted any of the J7BC Spring templates. This is indicated in the same documents as above:

This proxy allows the data access code to work with a simple JDBC API and is still involved in Spring transactions, similar to JDBC code in the J2EE / JTA environment. However, if possible, use Spring. DataSourceUtils, JdbcTemplate or JDBC objects to get involved in a transaction even without a proxy for the target DataSource, avoiding the need to define such a proxy in the first place.

If you do not have code that should bypass the Spring framework, then do not use TransactionAwareDataSourceProxy at all. If you have legacy code like this, you will need to do what you already configured in the second setup. You will need to create two beans, one of which is the data source, and the other is the proxy. Then you must provide the data source to all Spring managed types and the proxy for legacy types.