Spring批处理 - 导致可能的死锁

我有一个包含多个步骤的批处理作业 . 每个步骤都涉及从已加入的5-6个表中读取,并写入我的report_table . 在步骤8中,在读取和写入大约450000条记录之后,它失败并出现异常 - Caused by: java.sql.SQLException: Could not do an indexed read to get the next row. 我知道此异常是由死锁引起的 . 没有其他步骤并行运行,这是写入report_table的唯一步骤 . 什么可能导致这种僵局?我正忙着完成这项工作,非常感谢任何帮助 .

我的工作结构就像

  • Step1

  • Step2是与[Step2a,Step2b]并行运行的分割

  • 第3步

  • Step4是与[Step4a,Step4b,步骤4c]并行运行的分割

  • Step5是并行运行[Step5a,Step5b,步骤5c]的分割

  • Step6

  • Step7

  • Step8

  • Step9


我的配置如下:

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

这是我的堆栈跟踪:

执行作业时遇到致命错误

**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)

2 years ago

我认为,这个问题可能是informix数据库特有的 . 以下代码可能会有所帮助

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());

您需要在IfxXADataSource数据源对象中添加LOCK_MODE_WAIT设置