title: Spring batch - oracle connection closed
body: <p>I am facing a weird issue in my spring boot application and searched a lot but no luck for the answer. Basically, I have configured a very simple spring batch job with reader, processor and writer and I also have own data source configuration. </p>
  
  <p>I have only one job configured and there are will be multiple invocations of this same job with different parameters. That is, same job url will be triggered with (10) different inputs at the same time and everything would be running in parallel. Every jobStep has a throttle of 4 threads and hence all 10 job instances will span 4 giving rise to 40 threads in parallel. </p>
  
  <p>There are two problems I am encountering with this approach on daily basis and couldn't figure out the issue,</p>
  
  <ol>
  <li>Once the application starts and when the jobs are triggered for first time, all 10 job instances (with different inputs) are getting completed successfully. No issues with that. But then, after 10 hours, if we try to trigger same job instance with another 10 inputs at the same time, all the jobs are getting failed with the below reason. </li>
  </ol>
  
  <blockquote>
    <p>o.s.b.c.l.support.SimpleJobLauncher : Job: [FlowJob:
    [name=marketing-job]] completed with the following parameters:
    [{time=1513023822300, jobInput="CustomerA"] and the following status:
    [FAILED] 
    o.s.batch.core.step.AbstractStep : Encountered an error
    executing step jobStep in job marketing-job
    o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 17008, SQLState:
    08003 o.h.engine.jdbc.spi.SqlExceptionHelper : Closed Connection</p>
  </blockquote>
  
  <p>It just errors with connection closed. I have set testOnBorrow which should kind of ensure the pool connections are valid for the job, but that doesnt seem to work. Tried with testOnIdle as well but no luck...</p>
  
  <ol start="2">
  <li>Sometimes, even during the first run, obtaining the DB connection in reader is getting stuck for about 10-15 mins before it actually gets a connection. No clue on why is this happening.</li>
  </ol>
  
  <p>Have anyone faced these issues ? Could anyone help me in getting this resolved please?</p>
  
  <p>Have given the configurations for job and datasource below.</p>
  
  <p><strong>JOBConfig:</strong></p>
  
  <pre><code>@Configuration
  public class JobConfiguration implements BatchConfigurer {
  
    @Autowired
    public JobBuilderFactory jobBuilderFactory;
  
    @Autowired
    public StepBuilderFactory stepBuilderFactory;
  
    @Autowired
    Reader reader;
  
    @Autowired
    Processor processor;
  
    @Autowired
    Writer writer;
  
    @Autowired
    DataSource dataSourcePrimary;
  
    @Autowired
    private PlatformTransactionManager transactionManager;
  
    @Bean
    public Job job() {
      return jobBuilderFactory.get("marketing-job").incrementer(new RunIdIncrementer()).flow(jobStep()).end()
          .build();
    }
  
    @Bean
    public Step jobStep() {
      return stepBuilderFactory.get("jobStep")
          .&lt;Object, Object&gt;chunk(1).reader(reader)
          .processor(processor).writer(writer).taskExecutor(taskExecutor())
          .throttleLimit(4).build();
    }
  
    @Override
    public JobLauncher getJobLauncher() throws Exception {
      SimpleJobLauncher jobLauncher = new SimpleJobLauncher();
      jobLauncher.setJobRepository(getJobRepository());
      jobLauncher.setTaskExecutor(taskExecutor());
      return jobLauncher;
    }
  
    @Bean
    public TaskExecutor taskExecutor() {
      ThreadPoolTaskExecutor taskExecutor = new ThreadPoolTaskExecutor();
      taskExecutor.setCorePoolSize(100);
      taskExecutor.setMaxPoolSize(120);
      return taskExecutor;
    }
  
    @Override
    public JobRepository getJobRepository() throws Exception {
  
      JobRepositoryFactoryBean jobRepositoryFactoryBean = new JobRepositoryFactoryBean();
      jobRepositoryFactoryBean.setTransactionManager(oracleTransactionManager);
      jobRepositoryFactoryBean.setDatabaseType("ORACLE");
      jobRepositoryFactoryBean.setIsolationLevelForCreate("ISOLATION_READ_COMMITTED");
      jobRepositoryFactoryBean.setDataSource(dataSourcePrimary);
      jobRepositoryFactoryBean.afterPropertiesSet();
      return jobRepositoryFactoryBean.getObject();
    }
  
    @Override
    public PlatformTransactionManager getTransactionManager() throws Exception {
      return transactionManager;
    }
  
    @Override
    public JobExplorer getJobExplorer() throws Exception {
      return null;
    }
  }
  </code></pre>
  
  <p><strong>DataSourceConfig:</strong></p>
  
  <pre><code>@Configuration
  @EnableTransactionManagement
  @EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactoryPrimary",
      transactionManagerRef = "transactionManager",
      basePackages = "com.sample")
  public class DatasourceConfig {
  
    @Primary
    @Bean(name = "dataSourcePrimary", destroyMethod = "")
    public DataSource dataSource() {
      BasicDataSource dataSource = new BasicDataSource();
  
        dataSource.setInitialSize(50);
        dataSource.setMaxIdle(50);
        dataSource.setMaxTotal(60);
        dataSource.setValidationQuery("select 1 from dual");
        dataSource.setValidationQueryTimeout(10);
        dataSource.setTestOnBorrow(true);
        dataSource.setTestWhileIdle(true);
        //dataSource.setMinEvictableIdleTimeMillis(600000);
        //dataSource.setTimeBetweenEvictionRunsMillis(600000);
  
      return dataSource;
  
    }
  
    @Primary
    @Bean(name = "entityManagerFactoryPrimary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
        EntityManagerFactoryBuilder builder, @Qualifier("dataSourcePrimary") DataSource dataSource) {
      return builder.dataSource(dataSource).packages("com.sample.entity")
          .persistenceUnit("primary").build();
    }
  
    @Primary
    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager(
        @Qualifier("entityManagerFactoryPrimary") EntityManagerFactory entityManagerFactoryPrimary) {
      return new JpaTransactionManager(entityManagerFactoryPrimary);
    }
  
    @Primary
    @Bean(name = "entityManagerPrimary")
    public EntityManager entityManager(
        @Qualifier("entityManagerFactoryPrimary") EntityManagerFactory entityManagerFactoryPrimary) {
      return entityManagerFactoryPrimary.createEntityManager();
    }
  }
  </code></pre>
  
  <p><strong>Update: Added Reader &amp; Writer as per the request</strong></p>
  
  <pre><code>@Component
  @StepScope
  @RefreshScope
  public class Reader
      implements ItemReader&lt;A&gt;, StepExecutionListener {
  
    private List&lt;A&gt; a;
  
    @Override
    public void beforeStep(StepExecution stepExecution) {
      List&lt;A&gt; aList =
          new ArrayList&lt;A&gt;();
       // code to populate the data list 'a' from DB.
    }
  
    @Override
    public ExitStatus afterStep(StepExecution stepExecution) {
      // TODO Auto-generated method stub
      return null;
    }
  
    @Override
    public synchronized A read() {
      if (a.size() &gt; 0) {
        return a.remove(0);
      }
      return null;
    }
  
  }
  
  
  @Component
  public class Writer implements ItemWriter&lt;A&gt; {
  
    @Override
    public void write(List&lt;? extends A&gt; a) {
  
      // get 'a' and write 
    }
  
  }
  </code></pre>