InvalidDataAccessResourceUsageException: Unexpected cursor position change

Question

Encountered the following exception when trying to read from Database in a Spring Batch Application. Any insight would be helpful. This is caused when using a JdbcCursorItemReader. The application works fine using a JdbcPagingItemReader.

Also is it needed to close up any resources (cursors etc.) explicitly when using a JdbcCursorItemReader? If so, how do we do it?

SEVERE: Encountered an error executing the step
    org.springframework.dao.InvalidDataAccessResourceUsageException: Unexpected cursor position change.
        at org.springframework.batch.item.database.AbstractCursorItemReader.verifyCursorPosition(AbstractCursorItemReader.java:365)
        at org.springframework.batch.item.database.AbstractCursorItemReader.doRead(AbstractCursorItemReader.java:449)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:601)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:695)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:132)
        at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:120)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:631)
        at com.testapp.springbatchtest.io.DataExtractCursorItemReader$$EnhancerByCGLIB$$748012e7.doRead(<generated>)
        at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.read(AbstractItemCountingItemStreamItemReader.java:83)
        at org.springframework.batch.core.step.item.SimpleChunkProvider.doRead(SimpleChunkProvider.java:91)
        at org.springframework.batch.core.step.item.SimpleChunkProvider.read(SimpleChunkProvider.java:155)
        at org.springframework.batch.core.step.item.SimpleChunkProvider$1.doInIteration(SimpleChunkProvider.java:114)
        at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:368)
        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.item.SimpleChunkProvider.provide(SimpleChunkProvider.java:108)
        at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:69)
        at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:395)
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:131)
        at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:267)
        at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:77)
        at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:368)
        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:253)
        at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:195)
        at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:137)
        at org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:380)
        at org.springframework.batch.core.job.SimpleJob.doExecute(SimpleJob.java:124)
        at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:301)
        at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:134)
        at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:49)
        at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:127)

Following is the Sample Code for bean declaration. DataExtractCursorItemReader extends JdbcCursorItemReader.

@Bean
@StepScope
public DataExtractCursorItemReader cursorReaderBean() throws Exception {
    DataExtractCursorItemReader dataExtractReader = new DataExtractCursorItemReader();
    dataExtractReader.setDataSource(dataSource);
    dataExtractReader.setSql("SELECT * FROM SAMPLETABLE");
    dataExtractReader.setFetchSize(500);
    dataExtractReader.setRowMapper(new DataExtractRowMapper());

    return dataExtractReader;
}

Answer 1

verifyCursorPosition property is(seems) just a check to prevent index menipulating by user when subclassing reader; maybe is your case or maybe on some DB platform properties doesn't work.

You can disable it and all should work fine - it's just a test to prevent subclassing.
Check your code if you modify cursor position or check if test fails due to database driver.

Answer 2

I saw this error in case where there are multiple threads acting on a database reader and reader is not thread-safe typically JDBCCursorItemReader vs JDBCPagingItemReader

Answer 3

verifyCursorPosition check depends on java.sql.ResultSet::getRow(). "Support for the getRow method is optional for ResultSets with a result set type of TYPE_FORWARD_ONLY" according to the JavaDoc

You could try to change driver, type of ResultSet (Reader), or follow above suggestion to disable verifyCursorPosition