Spring批处理：java.sql.SqlRecoverableException：IO错误：套接字读取超时

Question

在Spring批处理中，对数据库的读写操作最多可完成31 000条记录。在31 000条记录之后，会出现以下异常：

java.sql.SQLRecoverableException:
 Encountered an error executing the step
org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLRecoverableException: IO Error: Socket read timed out
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:240)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:127)
    at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:264)
    at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:76)
    at org.springframework.batch.repeat.support.TaskExecutorRepeatTemplate$ExecutingRunnable.run(TaskExecutorRepeatTemplate.java:258)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLRecoverableException: IO Error: Socket read timed out
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:458)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:546)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
    at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:280)
    at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:207)
    at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:157)
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:202)
    ... 8 more
Caused by: oracle.net.ns.NetException: Socket read timed out
    at oracle.net.ns.Packet.receive(Packet.java:339)
    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:296)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:320)
    ... 16 more

DataSource:
    <bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource"
        destroy-method="close">
        <property name="explicitCachingEnabled" value="true" />
        <property name="URL" value="${batch.jdbc.url}"></property>
        <property name="password" value="${batch.jdbc.password}"></property>
        <property name="user" value="${batch.jdbc.user}"></property>
        <property name="connectionCachingEnabled" value="true" />
        <property name="connectionCacheName" value="ImplicitCache01" />
        <property name="maxStatements" value="2000000" />
        <property name="connectionCacheProperties">
            <props merge="default">
                <!-- <prop key="MinLimit">5</prop> -->
                <!-- <prop key="MaxLimit">50</prop> -->
                <prop key="InitialLimit">1</prop>
                <prop key="MinLimit">1</prop>
                <prop key="MaxLimit">2000</prop>            
                <prop key="ConnectionWaitTimeout">11800000</prop>
                <prop key="InactivityTimeout">11800000</prop>
                <prop key="TimeToLiveConnectionTimeout">18000</prop>
                <prop key="ValidateConnection">true</prop>
                <prop key="PropertyCheckInterval">300000</prop>
            </props>
        </property>
    </bean>

即使我在Connection属性中引发了超时，我仍然会得到相同的异常。

select e.id, e.tpin, e.res_blob.getClobVal() clobvalue from ecrnt e where e.id  >= 688370 AND e.id <= 788370

 - org.springframework.dao.DataAccessResourceFailureException: Attempt
   to process next row failed; SQL [select e.id, e.tpin, e.res_blob.getClobVal() 
   clobvalue from ecrnt e where e.id  >= 688370 AND e.id <= 788370];
   **ORA-00028: your session has been killed ORA-00028: your session has
   been killed ORA-00028: your session has been killed ORA-04036: PGA
   memory used by the instance exceeds PGA_AGGREGATE_LIMIT ORA-06512: at
   "SYS.XMLTYPE", line 138 ;** 

Answer 1

增加超时不会解决问题，因为实际上没有超时。您需要将连接生存时间设置为一个较高的值，以便oracle db server不会关闭您的连接。请参阅此处:https://docs.oracle.com/cd/b28359_01/java.111/e10788/optimize.htm#cfhbjbci