Spring Batch- ItemWriter - DataIntegrityViolationException - Skip Record - Retry - Not working

Question

I am stuck with this problem from sometime.

I am using spring-batch 3.0.7

Problem is in case of org.springframework.dao.DataIntegrityViolationException 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).

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.

But this is not happening. Chunk transaction is getting rolled back, but individual records are not retry.

I put debug point as well in skipPolicy(shouldSkip method) and skipListener(onSkipInWrite method), but flow is not coming to debug Point.

I am using CrudRepository with Hibernate for DAO layer (calling the same from writer)

Below is my code:

/**
     * @return processCRMRecords
     */
    @Bean
    public Step processCRMRecordsStep() {
        return stepBuilderFactory.get("crm-sr-files-processing-step").<CRMSrRecord, SRRecordEO> 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();
    }

SkipPolicy :

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;
    }

}

SkipListener :

@Override
public void onSkipInWrite(S item, Throwable t) {
    if (item instanceof IRecordString) {
        logger.info(((IRecordString) item).getRecordAsString());
    }
}

Exception which is skipped :

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,<null>,'test123','A','aaaa','qqqq','2017-10-06 17:20:51.845',<null>,<null>,'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,<null>,'test123','A','aaaa','qqqq','2017-10-06 17:20:51.845',<null>,<null>,'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

Spring batch executes the writer but after writer execution i get the exception below

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,<null>,'test123','A','Test','group','2017-10-06 18:07:04.726',<null>,<null>,'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.

I would appreciate help here

Answer 1

As "user8740552" mentioned, I was able to resolve the issue via same approach.

"Injected entity manager in writer and use flush after the chunk list of items are saved in writer i.e. after for loop in write() method itself"

Answer 2

inject entity manager in writer and use flush after save call as commit happens beyond scope of item writer. you can flush after all the chunk list save of writer as it will not have performance impact.