I have a batch job with multiple steps. Each step involves reading from 5-6 tables joined, and writing into my report_table. In step 8, after reading and writing about 450000 records, it failed with the exception - Caused by: java.sql.SQLException: Could not do an indexed read to get the next row. I understand that this exception is caused by a deadlock. There is no other step running in parallel and it is the only step that's writing into the report_table. What could have caused this deadlock ? I am on a time crunch to finish this job and any help is greatly appreciated.
Structure of my job is like
- Step1
- Step2 is a split with [ Step2a, Step2b ] running in parallel
- Step 3
- Step4 is a split with [ Step4a , Step4b, Step 4c] running in parallel
- Step5 is a split with [ Step5a , Step5b, Step 5c] running in parallel
- Step6
- Step7
- Step8
- Step9
My configuration is as follows :
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.2.xsd">
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
lazy-init="true">
<property name="dataSource" ref="dataSource" />
</bean>
<bean id="batchDefaultSerializer" class="org.springframework.batch.core.repository.dao.DefaultExecutionContextSerializer" />
<bean id="informixIncrementer" class="com.bah.batch.informixsupport.InformixMaxValueIncrementerFactory">
<property name="dataSource" ref="dataSource" />
</bean>
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.informix.jdbc.IfxDriver"/>
<property name="url" value="<url>" />
<property name="username" value="<username>" />
<property name="password" value="<password>" />
<property name="initialSize" value="10" />
<property name="maxIdle" value="5" />
<property name="minIdle" value="5" />
<property name="maxActive" value="10" />
<property name="maxWait" value="3000" />
<property name="removeAbandonedTimeout" value="1800" />
<property name="logAbandoned" value="true" />
</bean>
<bean id="jobRepository" class="com.bah.batch.informixsupport.InformixJobRepositoryFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="databaseType" value="Informix"/>
<property name="incrementerFactory" ref="informixIncrementer"/>
<property name="transactionManager" ref="transactionManager"/>
<property name="tablePrefix" value="bhi:BATCH_" />
</bean>
</beans>
Here is my stack trace :
Encountered fatal error executing job
**org.springframework.batch.core.JobExecutionException: Flow execution ended unexpectedly
at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:140)**
at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:304)
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 com.bah.discrepancy.init.InitiatePurchaseReport.main(InitiatePurchaseReport.java:76)
**Caused by: org.springframework.batch.core.job.flow.FlowExecutionException: Ended flow=itemCodeSplit.2 at state=itemCodeSplit.2.itemlevelItems with exception
at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:174)**
at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:144)
at org.springframework.batch.core.job.flow.support.state.SplitState$1.call(SplitState.java:93)
at org.springframework.batch.core.job.flow.support.state.SplitState$1.call(SplitState.java:90)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at org.springframework.core.task.SimpleAsyncTaskExecutor$ConcurrencyThrottlingRunnable.run(SimpleAsyncTaskExecutor.java:251)
at java.lang.Thread.run(Thread.java:662)
Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [UPDATE bhi:BATCH_JOB_EXECUTION_CONTEXT SET SHORT_CONTEXT = ?, SERIALIZED_CONTEXT = ? **WHERE JOB_EXECUTION_ID = ?]; SQL state [IX000]; error code [-244]; Could not do a physical-order read to fetch next row.; nested exception is java.sql.SQLException: Could not do a physical-order read to fetch next row.**
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:909)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:970)
at org.springframework.batch.core.repository.dao.JdbcExecutionContextDao.persistSerializedContext(JdbcExecutionContextDao.java:233)
at org.springframework.batch.core.repository.dao.JdbcExecutionContextDao.updateExecutionContext(JdbcExecutionContextDao.java:146)
at org.springframework.batch.core.repository.support.SimpleJobRepository.updateExecutionContext(SimpleJobRepository.java:210)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at $Proxy2.updateExecutionContext(Unknown Source)
at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:159)
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:165)
... 7 more
Caused by: java.sql.SQLException: Could not do a physical-order read to fetch next row.
at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3453)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3770)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2576)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2492)
at com.informix.jdbc.IfxSqli.executeCommand(IfxSqli.java:940)
at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:300)
at com.informix.jdbc.IfxStatement.c(IfxStatement.java:1272)
at com.informix.jdbc.IfxPreparedStatement.executeUpdate(IfxPreparedStatement.java:415)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:916)
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:909)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)
... 29 more
**Caused by: java.sql.SQLException: ISAM error: record is locked.**
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:407)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3775)