Transactions are not getting rollbacked with HikariCP

Question

I am using HikariCP for connection pooling. I have tried setting autoCommit to both true and false. Still my transactions are not getting rollbacked when an exception occur.

I have tried the same with org.apache.commons.dbcp.BasicDataSource. Transactions are getting rollbacked properly with this datasource but not when com.zaxxer.hikari.HikariDataSource is configured.

I'm using MySQL InnoDB database engine.

Edit:

@Service
@Transactional(rollbackFor = { Exception.class })
public class AServiceImpl {


@Override
public SomeDTO signUpUser(SomeDTO someDTO) throws Exception {

Company company = addCompany();
User user = addUser();
------------

}

private Company addCompany()

try{
    return companyRepository.addCompany();
} catch(PersistenceException e){
//throws exception
 }
} 

@Override
public User addUser()
try{
return userRepository.addUser();
}catch(PersistenceException e){
//throws exception
}
} 

Here, exception occurs at addUser method and records inserted through addCompany method isn't rollbacked

<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
        <property name="maximumPoolSize" value="100" />
        <property name="idleTimeout" value="900000" />
        <property name="connectionTimeout" value="2000" />
        <property name="minimumIdle" value="20" />
        <property name="maxLifetime" value="1800000" />
        <property name="leakDetectionThreshold" value="60000" />
        <property name="autoCommit" value="false" />

        <property name="dataSourceProperties">
            <props>
                <prop key="prepStmtCacheSize">300</prop>
                <prop key="prepStmtCacheSqlLimit">2048</prop>
                <prop key="cachePrepStmts">true</prop>
                <prop key="useServerPrepStmts">true</prop>
                <prop key="useLocalSessionState">true</prop>
                <prop key="useLocalTransactionState">true</prop>
                <prop key="rewriteBatchedStatements">true</prop>
                <prop key="cacheResultSetMetadata">true</prop>
                <prop key="cacheServerConfiguration">true</prop>
                <prop key="elideSetAutoCommits">false</prop>
                <prop key="maintainTimeStats">true</prop>
                <prop key="useLocalTransactionState">true</prop>
            </props>
        </property>
    </bean>

    <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource"
        destroy-method="close">
        <constructor-arg ref="hikariConfig" />
    </bean>

HikariCP version: 3.1.0 
JDK version : 1.8.0_162 
Database : MySQL InnoDB
MySQLJDBCDriver version : 5.1.31

Looking for some solutions. Thanks in advance.

Answer 1

How do you know that transactions are not being rolled back?

If autoCommit=false and the transaction state is dirty, HikariCP will automatically rollback on Connection.close(). If debug logging is enabled, you should see it logged. If you do not see the log message, that implies that Spring is explicitly calling rollback() (or commit()) before calling close() -- as I would expect with the annotation above.

Answer 2

Try setting useLocalTransactionState to false and see whether it makes a difference.

Attention: your configuration is currently setting this property twice! Remove one of the duplicate lines with <prop key="useLocalTransactionState">true</prop> and then change the remaining one to set the property to false instead of true.

Why I recommend this: I just spent a day trying to figure out why my transactions do not get rolled back. I found out that it's connected to the useLocalTransactionState property. If I set useLocalTransactionState to true my transactions do not get rolled back although, my debugging and p6spy clearly show that a rollback is performed by my transaction manager.

There is the MySQL Connector/J bug #75209 that describes something similar. Although that bug claims to be fixed with MySQL Connector/J v5.1.40, I can still see similar behaviour for v5.1.40 and v8.0.13.