I am using Custom DataSource for DB connection pool (apache DBCP2 and MySQL database version 8) for connection pooling and trying to delete DB objects (100k+ and takes more than 30 minutes) using Spring TransactionTemplate. But getting this error after 15 minutes.How to fix this error? Did I miss any other configuration?
Below are the values passing to create Connection. I have set MinEvictableIdleTimeMillis
value to 45 Minutes.
final BasicDataSource ds = new BasicDataSource();
ds.setPassword(password);
ds.setInitialSize(10);
ds.setMaxTotal(100);
ds.setMaxWaitMillis(20000);
ds.setMinIdle(5);
ds.setMaxIdle(20);
ds.setTimeBetweenEvictionRunsMillis(60000);
ds.setMinEvictableIdleTimeMillis(2400000L);
ds.setRemoveAbandonedOnMaintenance(true);
ds.setRemoveAbandonedTimeout(420);
ds.setTestWhileIdle(true);
ds.setTestOnBorrow(true);
ds.setTestOnReturn(true);
ds.setRemoveAbandonedOnBorrow(true);
ds.setValidationQuery("SELECT 1");
ds.setLogExpiredConnections(false);
ds.setDefaultQueryTimeout(2700);
Below is the code trying to execute:
transactionTemplate.setTimeout(3000);
transactionTemplate.execute(new TransactionCallbackWithoutResult() {
@Override
protected void doInTransactionWithoutResult(TransactionStatus
transactionStatus) {
final AnalysisJob analysisJob = ajRepo.findById(jobId).orElse(null);
if(analysisJob != null){
final AnalysisProfileLog profileLog = analysisJob.getProfileLog();
ruleViolationRepo.deleteByJob(analysisJob);
ruleLogRepo.deleteByJobId(analysisJob.getId());
if(profileLog != null){
if(profileLog.getAnalysisType() == AnalysisType.RISK) {
riskViolationRepo.deleteByJob(analysisJob);
riskLogRepo.deleteByJobId(jobId);
}
analysisProfileLogRepository.deleteById(profileLog.getId());
ajRepo.deleteById(jobId);
}
}
jdRepo.deleteJob(jobId);
}
});
Try by increasing the removeAbandonedTimeout value, currently it is set to 420 seconds (7 minutes). Increase this value more than 30 minutes(in seconds). A connection is considered abandoned and eligible for removal if it has not been used for longer than removeAbandonedTimeout.