title: Spring Batch- ItemWriter - DataIntegrityViolationException - Skip Record - Retry - Not working
body: <p>I am stuck with this problem from sometime.</p>
  
  <p>I am using spring-batch 3.0.7</p>
  
  <p>Problem is in case of <b>org.springframework.dao.DataIntegrityViolationException</b> in one record in ItemWriter, rest of the records in chunk(chunk size = 10 ) are also not inserting in DB, even after providing skipPolicy(returning true for all exceptions).</p>
  
  <p>My understanding is, if there will be be any exception(which is skipped via skipPolicy) while committing the whole chunk, current transaction will be rolled-back.Chuck size(commit-interval) will be reset to 1 and each record of chunk will be retried individually to find the record with problem and only that record will not go in DB. Rest of the record will go in DB.</p>
  
  <p>But this is not happening. Chunk transaction is getting rolled back, but individual records are not retry.</p>
  
  <p>I put debug point as well in skipPolicy(shouldSkip method) and skipListener(onSkipInWrite method), but flow is not coming to debug Point.</p>
  
  <p>I am using CrudRepository with Hibernate for DAO layer (calling the same from writer)</p>
  
  <p>Below is my code:</p>
  
  <pre><code>/**
       * @return processCRMRecords
       */
      @Bean
      public Step processCRMRecordsStep() {
          return stepBuilderFactory.get("crm-sr-files-processing-step").&lt;CRMSrRecord, SRRecordEO&gt; chunk(10)
                  .reader(crmSrMultiFileReader(null, null)).processor(crmSrRecordProcessor()).writer(crmRecordWriter())
          .faultTolerant().skipPolicy(skipSRPolicy()).listener(listener).build();
      }
  
      /**
       * @return SkipPolicy
       */
      @Bean
      public SkipPolicy skipSRPolicy() {
          return new RecordVerificationSkipper();
      }
  </code></pre>
  
  <p>SkipPolicy :</p>
  
  <pre><code>public class RecordVerificationSkipper implements SkipPolicy {
  
      Logger logger = LoggerFactory.getLogger(RecordVerificationSkipper.class);
  
      /**
       *
       */
      public RecordVerificationSkipper() {
          //
      }
  
      /* (non-Javadoc)
       * @see org.springframework.batch.core.step.skip.SkipPolicy#shouldSkip(java.lang.Throwable, int)
       */
      @Override
      public boolean shouldSkip(Throwable t, int skipCount) {
  
          return true;
      }
  
  }
  </code></pre>
  
  <p>SkipListener :</p>
  
  <pre><code>@Override
  public void onSkipInWrite(S item, Throwable t) {
      if (item instanceof IRecordString) {
          logger.info(((IRecordString) item).getRecordAsString());
      }
  }
  </code></pre>
  
  <p>Exception which is skipped :</p>
  
  <pre><code>org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
      at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:278)
      at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)
      at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:521)
      at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:761)
      at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:730)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:497)
      at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
      at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
      at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
      at com.sun.proxy.$Proxy70.commit(Unknown Source)
      at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:150)
      at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:271)
      at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:81)
      at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:374)
      at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)
      at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144)
      at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:257)
      at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:200)
      at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148)
      at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:64)
      at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:67)
      at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:169)
      at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:144)
      at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:134)
      at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:306)
      at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135)
      at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50)
      at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:128)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:497)
      at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
      at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
      at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
      at com.sun.proxy.$Proxy73.run(Unknown Source)
      at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.execute(JobLauncherCommandLineRunner.java:214)
      at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.executeLocalJobs(JobLauncherCommandLineRunner.java:231)
      at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.launchJobFromProperties(JobLauncherCommandLineRunner.java:123)
      at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.run(JobLauncherCommandLineRunner.java:117)
      at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:732)
      at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:716)
      at org.springframework.boot.SpringApplication.afterRefresh(SpringApplication.java:703)
      at org.springframework.boot.SpringApplication.run(SpringApplication.java:304)
      at org.springframework.boot.SpringApplication.run(SpringApplication.java:1118)
      at org.springframework.boot.SpringApplication.run(SpringApplication.java:1107)
      at com.dbs.app.batch.BatchAplication.main(BatchAplication.java:19)
  Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
      at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:59)
      at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
      at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
      at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
      at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:207)
      at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)
      at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2949)
      at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3449)
      at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:89)
      at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:582)
      at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:456)
      at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:337)
      at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
      at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1282)
      at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:465)
      at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:2963)
      at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2339)
      at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:485)
      at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:147)
      at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$100(JdbcResourceLocalTransactionCoordinatorImpl.java:38)
      at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:231)
      at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:65)
      at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:61)
      at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:517)
      ... 53 common frames omitted
  Caused by: java.sql.SQLIntegrityConstraintViolationException: (conn:554184) Cannot add or update a child row: a foreign key constraint fails (`domain`.`table_dummy`, CONSTRAINT `DUMMY_CCA_PR_FK9` FOREIGN KEY (`RT_ID`) REFERENCES `type_table_dummy` (`RT_ID`))
  Query is: insert into table_dummy (is_new, otherparameo_ot_id, rt_id, status, req_sub_type, created_by, created_datetime, updated_by, updated_datetime, unique_ref_num, rst_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), parameters [0,&lt;null&gt;,'test123','A','aaaa','qqqq','2017-10-06 17:20:51.845',&lt;null&gt;,&lt;null&gt;,'test123','test123']
      at org.mariadb.jdbc.internal.util.ExceptionMapper.get(ExceptionMapper.java:133)
      at org.mariadb.jdbc.internal.util.ExceptionMapper.getException(ExceptionMapper.java:101)
      at org.mariadb.jdbc.internal.util.ExceptionMapper.throwAndLogException(ExceptionMapper.java:77)
      at org.mariadb.jdbc.MariaDbStatement.executeQueryEpilog(MariaDbStatement.java:226)
      at org.mariadb.jdbc.MariaDbServerPreparedStatement.executeInternal(MariaDbServerPreparedStatement.java:413)
      at org.mariadb.jdbc.MariaDbServerPreparedStatement.execute(MariaDbServerPreparedStatement.java:362)
      at org.mariadb.jdbc.MariaDbServerPreparedStatement.executeUpdate(MariaDbServerPreparedStatement.java:351)
      at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
      ... 72 common frames omitted
  Caused by: org.mariadb.jdbc.internal.util.dao.QueryException: Cannot add or update a child row: a foreign key constraint fails (`appid`.`table_dummy`, CONSTRAINT `DUMMY_CCA_PR_FK9` FOREIGN KEY (`RT_ID`) REFERENCES `type_table_dummy` (`RT_ID`))
  Query is: insert into table_dummy (is_new, otherparameo_ot_id, rt_id, status, req_sub_type, created_by, created_datetime, updated_by, updated_datetime, unique_ref_num, rst_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), parameters [0,&lt;null&gt;,'test123','A','aaaa','qqqq','2017-10-06 17:20:51.845',&lt;null&gt;,&lt;null&gt;,'test123','test123']
      at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1144)
      at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1076)
      at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1031)
      at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executePreparedQuery(AbstractQueryProtocol.java:617)
      at org.mariadb.jdbc.MariaDbServerPreparedStatement.executeInternal(MariaDbServerPreparedStatement.java:401)
      ... 75 common frames omitted
  </code></pre>
  
  <p>Spring batch executes the writer but after writer execution i get the exception below</p>
  
  <pre><code>18:07:06 [main] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 1452, SQLState: 23000
  18:07:06 [main] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - (conn:554369) Cannot add or update a child row: a foreign key constraint fails (`domain`.`table_dummy`, CONSTRAINT `Test_CCA_PR_FK9` FOREIGN KEY (`RT_ID`) REFERENCES `table_type_dummy` (`RT_ID`))
  Query is: insert into table_dummy (is_new, otherparameo_ot_id, rt_id, status, req_sub_type, created_by, created_datetime, updated_by, updated_datetime, unique_ref_num, rst_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), parameters [0,&lt;null&gt;,'test123','A','Test','group','2017-10-06 18:07:04.726',&lt;null&gt;,&lt;null&gt;,'test','testtext']
  18:07:06 [main] INFO  o.h.e.j.b.internal.AbstractBatchImpl - HHH000010: On release of batch it still contained JDBC statements
  18:07:06 [main] INFO  o.s.b.core.step.tasklet.TaskletStep - Commit failed while step execution data was already updated. Reverting to old version.
  </code></pre>
  
  <p>I would appreciate help here </p>