Hikari整合spring事务不回滚

首先看下：常见事务失效原因

情景1

今天遇到一个事务不回滚的问题：

Service中的A方法，开启了事务注解：

@Transactional(rollbackFor = Exception.class, transactionManager = "transactionManager")

业务是中带有循环然后去查询第三方，然后再插入数据库。这里用到了批量insert，类似：

出问题的就是一直不回滚，然后各种资料也查了，最后发现不是spring的tx问题，最后发现原因是Hikari连接池的一个属性prepStmtCacheSqlLimit导致的：

spring.datasource.hikari.pool-name=HikariPool
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.validation-timeout=25000
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.data-source-properties.cachePrepStmts=true
spring.datasource.hikari.data-source-properties.prepStmtCacheSize=250
spring.datasource.hikari.data-source-properties.prepStmtCacheSqlLimit=4096
spring.datasource.hikari.data-source-properties.useServerPrepStmts=true
spring.datasource.hikari.data-source-properties.useLocalSessionState=true
spring.datasource.hikari.data-source-properties.useLocalTransactionState=true
spring.datasource.hikari.data-source-properties.rewriteBatchedStatements=true
spring.datasource.hikari.data-source-properties.cacheResultSetMetadata=true
spring.datasource.hikari.data-source-properties.cacheServerConfiguration=true
spring.datasource.hikari.data-source-properties.elideSetAutoCommits=true
spring.datasource.hikari.data-source-properties.maintainTimeStats=false

中的
spring.datasource.hikari.data-source-properties.prepStmtCacheSqlLimit=4096

将4096改成10000，事务可以回滚了。气炸了，猜测可能是缓存的preStatement字符长度达到最大了。大家在有循环里面的sql调用，还是尽量不要开启事务吧，长时间占用一个连接，而且容器导致事务问题。
prepStmtCacheSqlLimit表示单个sql的最大长度限制：

https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration

数据源于mybatis配置如下：

@Configuration
@MapperScan(basePackages = "com.ainemo.examination.dao.mapper")
public class SqlSessionFactoryConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.hikari")
    public DataSource dataSource(@Qualifier("dataSourceProperties") DataSourceProperties dataSourceProperties) {
        return buildDataSource(dataSourceProperties);
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.config")
    public DataSourceProperties dataSourceProperties() {
        return new DataSourceProperties();
    }

    private DataSource buildDataSource(DataSourceProperties dataSourceProperties) {
        DataSourceBuilder factory = DataSourceBuilder.create(dataSourceProperties.getClassLoader())
                .driverClassName(dataSourceProperties.getDriverClassName())
                .url(dataSourceProperties.getUrl())
                .username(dataSourceProperties.getUsername())
                .password(dataSourceProperties.getPassword());
        if (dataSourceProperties.getType() != null) {
            factory.type(dataSourceProperties.getType());
        }
        return factory.build();
    }


    @Bean
    @Primary
    public SqlSessionFactoryBean createSqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) {

        try {
            SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
            PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            String packageXmlConfigPath = PathMatchingResourcePatternResolver.CLASSPATH_ALL_URL_PREFIX + "mybatis/mapper/*.xml";
            //mapper.xml
            sqlSessionFactoryBean.setMapperLocations(resolver.getResources(packageXmlConfigPath));
            //数据源
            sqlSessionFactoryBean.setDataSource(dataSource);
            //配置文件
            sqlSessionFactoryBean.setConfigLocation(new ClassPathResource("mybatis/mybatis-config.xml"));
            return sqlSessionFactoryBean;
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    @Bean(name = "transactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(@Qualifier("dataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

情景2

事务正常会回滚，但是如果出现sql报错，又不回滚了，SQL不报错的异常会回滚：

spring.datasource.hikari.data-source-properties.useLocalTransactionState=true

改成

spring.datasource.hikari.data-source-properties.useLocalTransactionState=false