ExecutorService Callable thread mybatis insert 1M+ records throws CannotGetJdbcConnectionException Failed to obtain JDBC Connection

Below is Hikari connection settings

spring.datasource.hikari.connection-timeout=600000
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.maximum-pool-size=15
spring.datasource.hikari.minimum-idle=5

Below is the code, assign thread pool size as same as max connections, split 1M+ records list by 10k and inserting in to DB, using mybatis batch session

ExecutorService executorService = Executors.newFixedThreadPool(15);
List<List<Employee>> listOfEmployees = new ArrayList<>(IntStream.range(0, totalEmployeeList.size()).boxed().collect(
Collectors.groupingBy(e -> e / 10000, Collectors.mapping(totalEmployeeList::get, Collectors.toList()))).values());
List<Callable<Void>> callables = listOfEmployees.stream().map(sublist ->
(Callable<Void>) () -> {
     dao.insertEmployees(sublist);
      return null;
    }).collect(Collectors.toList());
try {
   executorService.invokeAll(callables);
   executorService.shutdown();
} catch (InterruptedException e) {
  log.error("Exception in executing thread save", e);
}

Batch session code Configuration:

@Bean(value = "batchSqlSession")
@Autowired
public SqlSessionTemplate batchSqlSession(SqlSessionFactory sqlSessionFactory) {       
   return new SqlSessionTemplate(sqlSessionFactory, ExecutorType.BATCH);
}

DAO:

public class EmployeeDAO {
private final SqlSessionTemplate sqlSessionTemplate;
private final EmployeeMapper employeeMapper;
    public EmployeeDAO (EmployeeMapper employeeMapper,
                     @Qualifier("batchSqlSession") SqlSessionTemplate sqlSessionTemplate) {
       this.sqlSessionTemplate = sqlSessionTemplate;
       this.employeeMapper=  sqlSessionTemplate.getMapper(EmployeeMapper.class);;
    }

@Transactional
public void insertEmployees(List<Employee> employeeList) {
        employeeList.stream().parallel().forEach(employee-> {
            try{
                employeeMapper.insertEmployees(employee);
            } catch(Exception ex){
                log.error("Exception - {} while inserting data {}",ex,employee.toString());
            }
        });
        sqlSessionTemplate.flushStatements();
        sqlSessionTemplate.clearCache();
    }

When i run this, i am getting

Caused by: org.apache.ibatis.exceptions.PersistenceException:

Error updating database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to

obtain JDBC Connection

The error may exist in mapper/EmployeeMapper.xml

The error may involve com.fmr.qrit.datasync.mapper.EmployeeMapper.insertEmployees

The error occurred while executing an update

Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection at

org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:84) at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:80) at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:67) at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:348) at org.apache.ibatis.executor.BatchExecutor.doUpdate(BatchExecutor.java:70) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ... 22 more Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms (total=10, active=10, idle=0, waiting=6) at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:686) at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:179) at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:144) at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:127) at org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource.getConnection(AbstractRoutingDataSource.java:213) at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:160) at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:118) at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81) ... 29 more

I tried increasing connection pool size as 15 from 10 and then connection-timeout as 600000 from 300000, but same error

I think the issue lies in the employeeList.stream().parallel().forEach() statement in your DAO method. Internally, it splits the list and executes concurrently, finally joining the results. This might have caused too many concurrent database connection requests, leading to the issue.

Parallel streams are ideally suitable for CPU-intensive operations, but they are not recommended for I/O-bound operations, such as database interactions.

Please try using a simple employeeList.stream().forEach operation instead