Spring HikariCP在做批量插入的时候，如果autoCommit=false, 会插入失败

本是古典何须时尚 2022-05-28 20:13 216阅读 0赞

Spring HikariCP在做大数据插入的时候，如果autoCommit=false, 会插入失败：  
现在的场景是：有一个表需要初始化数据，insert几千条数据，把数据按照每200条插入数据库，最后在数据库中发现并没有插入成功，发现其中有个错误：  
SqlSession \[org.apache.ibatis.session.defaults.DefaultSqlSession@5470d4c9\] was not registered for synchronization because synchronization is not active。  
其实刚开始是怀疑有可能是sql limit的问题，但是没有确定，于是在网上找了很多资料：  
spring 源码：  
[https://www.programcreek.com/java-api-examples/index.php?source\_dir=MybatisSpring2.5.X-master/src/main/java/org/mybatis/spring/SqlSessionUtils.java][https_www.programcreek.com_java-api-examples_index.php_source_dir_MybatisSpring2.5.X-master_src_main_java_org_mybatis_spring_SqlSessionUtils.java]

[https://stackoverflow.com/questions/12302598/spring-mybatis-tomcat-transactions-not-using-spring][https_stackoverflow.com_questions_12302598_spring-mybatis-tomcat-transactions-not-using-spring]

[https://github.com/brettwooldridge/HikariCP/issues/263][https_github.com_brettwooldridge_HikariCP_issues_263]

[https://github.com/brettwooldridge/HikariCP/issues/1030][https_github.com_brettwooldridge_HikariCP_issues_1030]

在看了上面的资料，发现更是一头雾水，于是在debug的时候，无意中，重复调了这个接口两次，居然在调第二次接口insert动作之前，在database里看到了调第一次接口中inser批量插入的数据，然后看到log中有：  
SELECT queries execute roll back due to dirty commit state on close()

于是想到，这样居然能看到数据，就在insert批量插入之后，再随便加一个select操作，这样居然也神奇的看到了database中数据，但是这种方式并不好，虽然能解决当前的问题，但是不具有普遍性，也就是说每个代码都要改，这样维护成本太高了，于是再次回到原点，想到是不是sql limit的问题，查阅HikariCP的config配置信息：[https://github.com/brettwooldridge/HikariCP\#configuration-knobs-baby][https_github.com_brettwooldridge_HikariCP_configuration-knobs-baby]

[https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration][https_github.com_brettwooldridge_HikariCP_wiki_MySQL-Configuration]

看到了这个属性：

> prepStmtCacheSqlLimit  
> This is the maximum length of a prepared SQL  
> statement that the driver will cache. The MySQL default is 256. In our  
> experience, especially with ORM frameworks like Hibernate, this  
> default is well below the threshold of generated statement lengths.  
> Our recommended setting is 2048.

于是把这个属性增加了原来的100倍，也就是：

prepStmtCacheSqlLimit=204800

运行程序，批量insert数据成功！

[https_www.programcreek.com_java-api-examples_index.php_source_dir_MybatisSpring2.5.X-master_src_main_java_org_mybatis_spring_SqlSessionUtils.java]: https://www.programcreek.com/java-api-examples/index.php?source_dir=MybatisSpring2.5.X-master/src/main/java/org/mybatis/spring/SqlSessionUtils.java
[https_stackoverflow.com_questions_12302598_spring-mybatis-tomcat-transactions-not-using-spring]: https://stackoverflow.com/questions/12302598/spring-mybatis-tomcat-transactions-not-using-spring
[https_github.com_brettwooldridge_HikariCP_issues_263]: https://github.com/brettwooldridge/HikariCP/issues/263
[https_github.com_brettwooldridge_HikariCP_issues_1030]: https://github.com/brettwooldridge/HikariCP/issues/1030
[https_github.com_brettwooldridge_HikariCP_configuration-knobs-baby]: https://github.com/brettwooldridge/HikariCP#configuration-knobs-baby
[https_github.com_brettwooldridge_HikariCP_wiki_MySQL-Configuration]: https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration