0
votes

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

  1. Step1
  2. Step2 is a split with [ Step2a, Step2b ] running in parallel
  3. Step 3
  4. Step4 is a split with [ Step4a , Step4b, Step 4c] running in parallel
  5. Step5 is a split with [ Step5a , Step5b, Step 5c] running in parallel
  6. Step6
  7. Step7
  8. Step8
  9. 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)
1
Can you provide your configuration? A full stack trace? What step it actually failed in? - Michael Minella
I realized that when I use a task-executor (SimpleAsyncTaskExecutor) I get this deadlock issue. When is it advisable to use an asynchronous task executor ? - Radhika

1 Answers

0
votes

I think , this issue might be specific to informix Database. the following code might be helpful

  IfxXADataSource xaDataSource=new IfxXADataSource();
           xaDataSource.setIfxIFX_LOCK_MODE_WAIT(dataSourceProperties.getLockWaitTime());
         xaDataSource.setUser(dataSourceProperties.getDbUsername());
         xaDataSource.setPassword(dataSourceProperties.getDbPassword());
         xaDataSource.setPortNumber(dataSourceProperties.getPort());
         xaDataSource.setServerName(dataSourceProperties.getServername());
         xaDataSource.setIfxPATH(dataSourceProperties.getDbUrl());
         xaDataSource.setIfxIFXHOST(dataSourceProperties.getHost());
         xaDataSource.setDatabaseName(dataSourceProperties.getDatabasename());
         org.apache.tomcat.jdbc.pool.DataSource dataSourceLOcal =new org.apache.tomcat.jdbc.pool.DataSource();
         dataSourceLOcal.setDataSource(xaDataSource);
         dataSourceLOcal.setMaxActive(dataSourceProperties.getMaxActive());
         dataSourceLOcal.setInitialSize(dataSourceProperties.getInitialSize());
         dataSourceLOcal.setTestOnBorrow(dataSourceProperties.getTestOnBorrow());

You need add LOCK_MODE_WAIT setting in IfxXADataSource data source object