Transactional issue - @DatabaseSetup using different connection

[roberthunt]

I've been having issues with the @DatabaseSetup not being rolled back after tests in our project which is causing constraint violations as the data from the previous test is still in the database. I created a small standalone Spring Boot project but can't seem to replicate the behaviour despite using the same versions of the dependencies. I am still trying but even the simplest test case in our project fails (see test code below).

By observing the general log in the database I can see that for some reason the test and DBUnit operations are being executed on different connections (note the 3rd column - thread_id). The new connection obtained for the DBUnit data sets autocommit=1 so the writes are permanent. Had the DBUnit operations been executed in the existing connection then autocommit=0 and a rollback would prevent this data from being written permanently.

@ActiveProfiles("test")
@RunWith(SpringRunner.class)
@DatabaseSetup(value = "/testData.xml", type = DatabaseOperation.INSERT)
@Transactional
@ContextConfiguration(locations = {
        "classpath:applicationContext-core-persistence-test.xml",
        "classpath:applicationContext-core-transactionManager.xml",
        "classpath:applicationContext-core-profile-test.xml"
})
@TestExecutionListeners(TransactionDbUnitTestExecutionListener.class)
public class BugTest
{
    @Test
    public void test1()
    {
    }

    @Test
    public void test2()
    {
    }
}

The failure reason is:

2017-02-03 10:54:15:829 +0000 [main] WARN org.springframework.test.context.TestContextManager - Caught exception while allowing TestExecutionListener [com.github.springtestdbunit.TransactionDbUnitTestExecutionListener@6f3b5d16] to process 'before' execution of test method [public void com.centrix.event.repository.BugTest.test2()] for test instance [com.centrix.event.repository.BugTest@41041c31]
org.dbunit.DatabaseUnitException: Exception processing table name='interlocking'
...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'

Dataset for reference:

<dataset>
    <interlocking id="1"></interlocking>
</dataset>

I'm not completely familiar with the inner workings of @Transactional but my assumption was that a database connection would be obtained once per thread and stored in a ThreadLocal to be used for the various operations that are needed during the transaction and then being released at the end of the execution.

Hopefully I can either replicate or resolve the issue but any input would be appreciated.

[roberthunt]

Well that didn't take long, I started poking around in our applicationContext-core-transactionManager.xml configuration to discover we're using a JpaTransactionManager which binds the transaction to the JPA EntityManager which I guess explains why DBUnit it operating outside the scope of it.

I've swapped it for a DataSourceTransactionManager

Binds a JDBC Connection from the specified DataSource to the current thread

This sounds a lot more like what I want and how I assumed it was working. The tests seem to run fine now:

[roberthunt]

Well the DataSourceTransactionManager may have fixed the test case but it turned out not to be the correct solution in the end but it did point me in the right direction. When using JPA the JpaTransactionManager needs to be used for things to work properly so I had to reinstate it in our project, the thing that fixed it in the end though was to specify the jpaDialect on the entityManagerFactory bean.

<bean id="jpaDialect" class="org.springframework.orm.jpa.vendor.HibernateJpaDialect"/>

<bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="entityManagerFactory">
    <property name="jpaDialect" ref="jpaDialect" />
</bean>

For reference here was the full definition:

<bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="entityManagerFactory">
    <property name="jpaDialect" ref="jpaDialect" />
    <property name="entityManagerInterface" value="org.hibernate.jpa.HibernateEntityManager" />
    <property name="packagesToScan" value="com.example,org.springframework.data.jpa.convert.threeten"/>
    <property name="persistenceProviderClass" value="org.hibernate.jpa.HibernatePersistenceProvider"/>
    <property name="dataSource" ref="dataSource"/>
    <property name="jpaPropertyMap">
        <map>
            <entry key="hibernate.hbm2ddl.auto" value="create-drop"/>
            <entry key="hibernate.dialect" value="${database.dialect}"/>
            <entry key="hibernate.format_sql" value="true"/>
            <entry key="hibernate.use_sql_comments" value="true"/>
            <entry key="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
            <entry key="hibernate.max_fetch_depth" value="3"/>
            <entry key="hibernate.show_sql" value="false"/>
            <entry key="hibernate.generate_statistics" value="false"/>
            <entry key="hibernate.connection.release_mode" value="auto"/>
            <entry key="hibernate.transaction.auto_close_session" value="false"/>
        </map>
    </property>
</bean>

This is part of the problem with an old project that you introduce Spring Boot to, sometimes the existing configuration has interesting quirks that you don't spot immediately until they cause you problems. The mess above can be handled better by specifying a JpaVendorAdapter which will configure most of these settings.