HikariCP连接池在spring batch 4.0中挂起

Question

我们已将基于Spring Batch的应用程序更新为Spring Batch 4的最新版本。x和Boot 2.0。并且HikariCP MySQL连接池存在一些问题。

如果我使用10个连接的默认maxPoolsize，并且我使用5个线程，每个线程都启动一个简单的虚拟作业，这个工作正常。（使用SimpleJobLauncher）。所有作业立即启动，之后HikariCP池已返回所有使用的连接。

当我做同样的事情时，问题就开始了，但是有11个线程。然后我看到应用程序挂起，直到连接超时，然后作业被启动。我预计前10个工作将开始，在第一个完成第11个工作后...

在作业开始之前，我看到：

HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)

现在我开始11个作业，应用程序将挂起30秒。。。

2019-01-21 11:25:01.256  INFO 10194 --- [ver (CliServer)] c.n.r.i.batch.cli.CliClientHandler       : Start 11 jobs
2019-01-21 11:25:30.928 DEBUG 10194 --- [l-1 housekeeper] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Pool stats (total=10, active=10, idle=0, waiting=2)
2019-01-21 11:25:35.990 DEBUG 10194 --- [      Thread-10] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Timeout failure stats (total=10, active=10, idle=0, waiting=1)
2019-01-21 11:25:35.992 DEBUG 10194 --- [       Thread-6] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Timeout failure stats (total=10, active=10, idle=0, waiting=1)
org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:305)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:378)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:474)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:289)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:181)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
    at com.sun.proxy.$Proxy74.createJobExecution(Unknown Source)
    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:131)
    at com.xx.yy.zz.batch.cli.commands.DummyCliCommand$1.run(DummyCliCommand.java:54)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
    at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:697)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:196)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:161)
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:262)
    ... 12 more

在此超时之后，第一个作业将启动。。。

2019-01-21 11:25:36.061  INFO 10194 --- [      Thread-11] c.n.r.i.b.u.c.BlockingThreadPoolExecutor : [sftp-executor] 3 running jobs, 0 queued jobs, 2000 jobs allowed
2019-01-21 11:25:36.062  INFO 10194 --- [       Thread-5] c.n.r.i.b.u.c.BlockingThreadPoolExecutor : [sftp-executor] 3 running jobs, 0 queued jobs, 2000 jobs allowed
2019-01-21 11:25:36.061  INFO 10194 --- [       Thread-3] c.n.r.i.b.u.c.BlockingThreadPoolExecutor : [sftp-executor] 3 running jobs, 0 queued jobs, 2000 jobs allowed
2019-01-21 11:25:36.063  INFO 10194 --- [pool-1-thread-2] o.s.b.c.l.support.SimpleJobLauncher      : Job: [SimpleJob: [name=dummyJob]] launched with the following parameters: [{dummy=f5c864d9-8a74-4d51-9a53-e3fc5c0b731d}]

版本：-HIKARIP2.7。9-Spring批芯4.0。1-Hibernate Core 5.2。17.最终-Spring护套2.0。5.

我不明白为什么游泳池挂着？（虚拟作业只Hibernate一秒钟）

allowPoolSuspension.............false
autoCommit......................true
catalog.........................none
connectionInitSql...............none
connectionTestQuery.............none
connectionTimeout...............30000
dataSource......................none
dataSourceClassName.............none
dataSourceJNDI..................none
dataSourceProperties............{usePipelineAuth=false, password=<masked>, prepStmtCacheSqlLimit=2048, rewriteBatchedStatements=false, useSSL=false, cachePrepStmts=true, useServerPrepStmts=true, prepStmtCacheSize=250}
driverClassName................."com.myhtml" target="_blank">sql.jdbc.Driver"
healthCheckProperties...........{}
healthCheckRegistry.............none
idleTimeout.....................600000
initializationFailTimeout.......1
isolateInternalQueries..........false
jdbcUrl.........................jdbc:mysql://localhost:3306/development
leakDetectionThreshold..........60000
maxLifetime.....................1800000
maximumPoolSize.................10
metricRegistry..................none
metricsTrackerFactory...........none
minimumIdle.....................10
password........................<masked>
poolName........................"HikariPool-1"
readOnly........................false
registerMbeans..................true
scheduledExecutor...............none
schema..........................none
threadFactory...................internal
transactionIsolation............default
username........................"user"
validationTimeout...............5000

Answer 1

线程限制需要小于spring batch与其jobrepository一起使用的数据源的最大池大小。因此，对于您的情况，由于您的数据源池大小是10，所以您应该使用不超过9个线程。

对于异步taskExecutor（taskExecutor），可以限制并发tasklet执行的数量（超出线程池提供的任何限制）。节流限制应小于此步骤作业存储库中使用的数据源池大小。

org.springframework.batch.core.step.builder.AbstractTaskletStepBuilder