Issue with Declarative Transactions and TransactionAwareDataSourceProxy in combination with JOOQ

Question

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

I have the following issue:

• During testing (JUnit), @Transactional works as expected. A single method is transactional no matter how many times I use the DSLContext's store() method, and a RuntimeException triggers a rollback of the entire transaction.
• During actual production runtime, @Transactional is completely ignored. A method is no longer transactional, and TransactionSynchronizationManager.getResourceMap() holds two separate values: one showing to my connection pool (which is not transactional), and one showing the TransactionAwareDataSourceProxy).

In this case, I would have expected only a single resource of type TransactionAwareDataSourceProxy which wraps my DB CP.

• After much trial and error using the second set of configuration changes I made (noted below with "AFTER"), @Transactional works correctly as expected even during runtime, though TransactionSynchronizationManager.getResourceMap() holds the following value:

In this case, my DataSourceTransactionManager seems to not even know the TransactionAwareDataSourceProxy (most likely due to my passing it the simple DataSource, and not the proxy object), which seems to completely 'skip' the proxy anyway.

My question is: the initial configuration that I had seemed correct, but did not work. The proposed 'fix' works, but IMO should not work at all (since the transaction manager does not seem to be aware of the TransactionAwareDataSourceProxy).

What is going on here? Is there a cleaner way to fix this issue?

BEFORE (not transactional during runtime)

@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 (transactional during runtime, as expected, all non-listed beans identical to 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'll turn my comments into an answer.

The transaction manager should NOT be aware of the proxy. From the documentation:

Note that the transaction manager, for example DataSourceTransactionManager, still needs to work with the underlying DataSource, not with this proxy.

The class TransactionAwareDataSourceProxy is a special purpose class that is not needed in most cases. Anything that is interfacing with your data source through the Spring framework infrastructure should NOT have the proxy in their chain of access. The proxy is intended for code that cannot interface with the Spring infrastructure. For example, a third party library that was already setup to work with JDBC and did not accept any of Spring's JDBC templates. This is stated in the same docs as above:

This proxy allows data access code to work with the plain JDBC API and still participate in Spring-managed transactions, similar to JDBC code in a J2EE/JTA environment. However, if possible, use Spring's DataSourceUtils, JdbcTemplate or JDBC operation objects to get transaction participation 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 any code that needs to bypass the Spring framework then do not use the TransactionAwareDataSourceProxy at all. If you do have legacy code like this then you will need to do what you already configured in your second setup. You will need to create two beans, one which is the data source, and one which is the proxy. You should then give the data source to all of the Spring managed types and the proxy to the legacy types.