Spring Batch : HikariPool-1 - Connection is not available, request timed out after 30000ms

user_14092914 0
Viewed 3857

I've the following error whith SpringBatch :

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.

Given that I've a lot of files to load, I think that the error is because of my TaskExecutor :

@Bean
    public TaskExecutor MyTE() {
             ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
             executor.setCorePoolSize(64);
             executor.setMaxPoolSize(64);
             executor.setQueueCapacity(64);
             executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
             executor.setThreadNamePrefix("MyTE-");
             return executor;
        }

I used "Integer.MAX_VALUE" for CorePoolSize, MaxPoolSize and QueueCapacity (instead of 64) but the error occurs all the same.

Edit : Eventually, the cause of my problem seems to be a call to a stored procedure in beforeJob. Indeed, when I comment this code below, it works.

@Override
public void beforeJob(JobExecution jobExecution) {
        
CallableStatement cs=null; 

cs=MyDataSource.getConnection().prepareCall("CALL my_proc(?,?)");
cs.setString(1, "my_arg1");
cs.setString(2,"my_arg2"); 
cs.execute();
cs.close();

However, I want to do this call and I don't understand why it generates a problem. Connection seems to be well closed with cs.close();

What is the problem ?

Regards.

Solution :

Connection con = null;
CallableStatement cs = null; 

con=MyDataSource.getConnection();
                         
cs=con.prepareCall("CALL my_proc(?,?)");
cs.setString(1, "my_arg1");
cs.setString(2,"my_arg2"); 
cs.execute();

cs.close();
con.close();
spring-batch
2 Answers

There are two key parameters here: the size of your worker threads pool and the size of your datatbase connection pool.

If each thread requests a database connection and you have more workers than available connections, then at some point worker threads will wait for connections to become available. This wait could timeout if no connections are released before the configured timeout value.

So you need to make sure you have enough connections for your worker threads, or increase the timeout in your connection pool.

user_50193860

You're exhausting your database connection pool because you're not closing connections to release them back into the pool. You're only calling close on the CallableStatement. Try something like this...

try (Connection conn = MyDataSource.getConnection()) {
  CallableStatement cs= conn.prepareCall("CALL my_proc(?,?)");
  cs.setString(1, "my_arg1");
  cs.setString(2,"my_arg2"); 
  cs.execute();
  cs.close();
}

That will autoclose the Connection object and release it back into the DataSource connection pool.

user_7408880
Related