Transaction is not rolled back in JOOQ

Question

I have a code that is very similar to this one:

dslContext.transaction(new TransactionalRunnable()
{
    @Override
    public void run(Configuration arg0) throws Exception
    {
        dao1.insert(object1);
        //Object 1 is inserted in the database 
        //despite the exception that is being thrown
        if(true)
           throw new RuntimeException();
        dao2.insert(object2)
    }
});

This is the code I'm using to create the dsl context and the daos that have been generated with JOOQ.

ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
comboPooledDataSource.setDriverClass(org.postgresql.Driver.class.getName());
comboPooledDataSource.setJdbcUrl("jdbc:postgresql://localhost:5432/database?searchpath=schema");
comboPooledDataSource.setUser("user");
comboPooledDataSource.setPassword("password");
comboPooledDataSource.setMinPoolSize(5);
comboPooledDataSource.setAcquireIncrement(5);
comboPooledDataSource.setMaxPoolSize(25);
Configuration configuration=new DefaultConfiguration().set(comboPooledDataSource).set(
                        SQLDialect.POSTGRES);
DSLContext dslContext=DSL.using(configuration);
Dao1 dao1=new Dao1(configuration);
Dao2 dao2=new Dao2(configuration);

Why am I getting this behavior?

Answer 1

I'm letting spring handle the transactions with jOOQ. Here is how:

This is the spring configuration class:

@Configuration
public class SpringConfiguration
{
    @Bean
    public DataSource dataSource() throws PropertyVetoException
    {
        comboPooledDataSource.setDriverClass(org.postgresql.Driver.class.getName());
        comboPooledDataSource
                    .setJdbcUrl("jdbc:postgresql://localhost:5432/database?searchpath=schema");
        comboPooledDataSource.setUser("databaseuser");
        comboPooledDataSource.setPassword("password");

        comboPooledDataSource.setMinPoolSize(5);
        comboPooledDataSource.setAcquireIncrement(5);
        comboPooledDataSource.setMaxPoolSize(25);
        return comboPooledDataSource;
    }

    @Bean
    public DataSourceTransactionManager transactionManager() throws PropertyVetoException
    {
        return new DataSourceTransactionManager(dataSource());
    }

    @Bean
    public TransactionAwareDataSourceProxy transactionAwareDataSource() throws PropertyVetoException
    {
        return new TransactionAwareDataSourceProxy(dataSource());
    }

    @Bean
    public DataSourceConnectionProvider connectionProvider() throws PropertyVetoException
    {
        return new DataSourceConnectionProvider(transactionAwareDataSource());
    }

    @Bean
    public org.jooq.Configuration configuration() throws PropertyVetoException
    {
        return new DefaultConfiguration().set(connectionProvider()).set(transactionProvider()).set(SQLDialect.POSTGRES);
    }

    @Bean
    public TransactionProvider transactionProvider() throws PropertyVetoException
    {
        return new SpringTransactionProvider(transactionManager());
    }

    @Bean
    public DSLContext dslContext() throws PropertyVetoException
    {
        return DSL.using(configuration());
    }
} 

And this is the SpringTransactionProvider:

public class SpringTransactionProvider implements TransactionProvider
{
    DataSourceTransactionManager transactionManager;

    public SpringTransactionProvider(DataSourceTransactionManager transactionManager)
    {
        this.transactionManager = transactionManager;
    }

    @Override
    public void begin(TransactionContext ctx)
    {
        TransactionStatus tx = transactionManager.getTransaction(new DefaultTransactionDefinition(
                TransactionDefinition.PROPAGATION_REQUIRED));
        ctx.transaction(new SpringTransaction(tx));
    }

    @Override
    public void commit(TransactionContext ctx)
    {
        transactionManager.commit(((SpringTransaction) ctx.transaction()).tx);
    }

    @Override
    public void rollback(TransactionContext ctx)
    {
        transactionManager.rollback(((SpringTransaction) ctx.transaction()).tx);
    }

    class SpringTransaction implements Transaction
    {
        final TransactionStatus tx;

        SpringTransaction(TransactionStatus tx)
        {
            this.tx = tx;
        }
    }
}

And finally to get the DSLContext:

ApplicationContext applicationContext = new AnnotationConfigApplicationContext(SpringConfiguration.class);
DSLContext dslContext=applicationContext.getBean(DSLContext.class);

You will need those jars in your classpath to get this to work: spring-tx.jar, spring-aop.jar, spring-expression.jar, spring-core.jar, spring-beans.jar, spring-jdbc.jar and spring-context.jar :)

Answer 2

Your DAOs are configured with a different configuration than your transaction. This means that each DAO runs its code in a new auto-committed transaction, even if you put that logic inside of a TransactionalRunnable.

This would work:

dslContext.transaction(new TransactionalRunnable()
{
    @Override
    public void run(Configuration arg0) throws Exception
    {
        new Dao1(arg0).insert(object1);
        if(true)
           throw new RuntimeException();
        new Dao2(arg0).insert(object2)
    }
});

Note that [DSLContext.transaction(TransactionalRunnable][1]) does not modify the dslContext and its enclosed Configuration. This means that if your data source is not working e.g. like a JavaEE or Spring TransactionAwareDataSourceProxy, then you must use the argument Configuration of your run() method to run further queries, either by wrapping it with DSL.using(configuration) or by passing it to your daos.

A much simpler option would be to use a data source that is transaction aware (i.e. it binds a transaction to a thread), such that the same thread will always get the same transacted JDBC Connection from the datasource.