Spring batch commit interval rollback issue for duplicate record in database

Question

I am trying to read rows from CSV files and persist into database. I am using MultiResourceItemReader delegated with FlatFileItemReader and JdbcBatchItemWriter for the read/persist operations. I configured the commit-interval of 50(for example)and skip policy. I am using spring-batch-3.0.8, oracle database.

To be simple to understand,In the CSV file, I have 2 rows and commit-interval is 2.

Here, ROLLNO-201 is the record already present in DB.

Observation:

1.If the 1st row is duplicate of the record present in DB, and 2nd row is new record. I see the new record is inserted into DB, skipping the 1st row as it is duplicate.[working fine as expected].

ROLLNO  NAME    CLASS   CITY

201    JOHN     4       MADISON

202    STEPHEN  5       MADISON

2.If the 1st row is new record, and 2nd row is duplicate of the record present in DB. I see the new record is not inserted into DB.[Issue].

ROLLNO  NAME    CLASS   CITY

202    STEPHEN  5       MADISON

201    JOHN     4       MADISON

I see the transaction rollback is based on the commit-interval's records and not based on chunkSize. If commit-interval is 10, then the 10th record should not be a duplicate for transaction to commit.

Can anyone help me on this as I'm clueless here?

<bean id="jobRepository"    class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean">
    <property name="transactionManager" ref="transactionManager" />
    <property name="dataSource" ref="cisDataSource" />
    <property name="lobHandler" ref="lobHandler" />
    <property name="isolationLevelForCreate" value="ISOLATION_READ_COMMITTED" />
</bean>

<bean id="transactionManager"
    class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="cisDataSource" />
</bean>

<bean id="cisDataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="initialSize" value="1" />
    <property name="maxActive" value="${db.connection.pool.size}" />
    <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
    <property name="url" value="${cisdb.connection.string}" />
    <property name="username" value="${cisdb.username}" />
    <property name="password" value="${cisdb.password}" />
</bean>

<batch:job id="mdtJob1">
<batch:step id="mdtJob1Step1">
    <batch:tasklet ref="fileTransferToProcessingFolderTasklet" />
    <batch:next on="COMPLETED" to="mdtJob1Step2" />
</batch:step>
<batch:step id="mdtJob1Step2">
    <batch:tasklet>
        <batch:chunk reader="multiResourceReader" writer="naxAddressSqlItemWriter"
            commit-interval="5">
            <batch:skip-policy>
                <bean class="org.springframework.batch.core.step.skip.AlwaysSkipItemSkipPolicy" scope="step"/>
            </batch:skip-policy>
            <batch:retry-policy>
                <bean class="org.springframework.retry.policy.NeverRetryPolicy" scope="step"/>
            </batch:retry-policy>
        </batch:chunk>
        <batch:no-rollback-exception-classes>
            <batch:include class="java.sql.SQLException"/>
            <batch:include class="org.springframework.dao.DuplicateKeyException"/>
            <batch:include class="java.sql.SQLIntegrityConstraintViolationException"/>
        </batch:no-rollback-exception-classes>
    </batch:tasklet>
    <batch:next on="COMPLETED" to="mdtJob1Step3" />
</batch:step>
<batch:step id="mdtJob1Step3">
    <batch:tasklet ref="fileTransferToArchiveFolderTasklet" />
</batch:step>
</batch:job>

<bean id="multiResourceReader"
    class="org.springframework.batch.item.file.MultiResourceItemReader"
    scope="step">
    <property name="delegate" ref="flatFileItemReader" />
    <property name="resources" value="${batch.processing.files}" />
</bean>

<bean id="flatFileItemReader" class="org.springframework.batch.item.file.FlatFileItemReader">
    <property name="lineMapper">
<bean class="org.springframework.batch.item.file.mapping.DefaultLineMapper">
            <property name="lineTokenizer">
<bean
                    class="org.springframework.batch.item.file.transform.DelimitedLineTokenizer">
                    <property name="names"
                        value="${csv.fields.in.order}" />
                </bean>
            </property>
            <property name="fieldSetMapper">
<bean               class="org.springframework.batch.item.file.mapping.BeanWrapperFieldSetMapper">
                    <property name="prototypeBeanName" value="addressDto" />
                </bean>
            </property>
        </bean>
    </property>
</bean>

<bean id="naxAddressSqlItemWriter"
    class="org.springframework.batch.item.database.JdbcBatchItemWriter">
    <property name="dataSource" ref="cdmDataSource" />
    <property name="sql" value="${nax.address.insertion.query}" />
    <property name="itemSqlParameterSourceProvider">
<bean class="org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider" />
    </property>
</bean>

Hi and welcome to StackOverflow. Please refer to stackoverflow.com/help/how-to-ask on how to ask a proper question and improve yours according the guidelines. For now some code would be nice so we can have a closer look into the problem and its origins. — Fabian Schöner
@FabianSchöner Add the requested code for better analysis. Thanks in Advance. — ashokmarappan
what was chunk size for commit-interval= 2? Also, show a bit of your config and writer code . — Sabir Khan
we have the commit-interval as 50 in application. The writer code is available in the above code which is shared. i have used org.springframework.batch.item.database.JdbcBatchItemWriter. — ashokmarappan
In your data source configuration, can you try setting defaultAutoCommit to false to see if you see change in behavior? — Sabir Khan

Amit Banger Amit Banger · Accepted Answer · 2020-02-12T06:58:29

I hope you would have got the solution by now. Just In case if you haven't

This solution had worked for me.

If you try to make commit-interval = 1, this will make sure one row will get inserted at once and duplicate row would not have new entry into DB.

For example: chunk(1)

Spring batch commit interval rollback issue for duplicate record in database

1 Answers