Quartz调度程序产生“ResultSet已关闭”异常

Question

在将基础设施从JEE6迁移到Jee7之后，我在Quartz调度器中得到了一个“resultset is closed”异常。

• 是:9.0.0.10
• DB2:v11.1.4.4
• DB2 JDBC:11.1.3_4.22.38（与11.1.4.4的行为相同）
• jpa:hibernate-jpa-2.1-api:1.0.2.final
• JTA:1.2
• Spring:5.1.4
• Hibernate:5.2.9
• 石英:2.3.0

配置：

Quartz配置为使用非事务性数据源，并由Spring#ScheduerFactoryBean调用。

原始SQL语句：（只产生一条记录输出）

SELECT TRIGGER_NAME, TRIGGER_GROUP FROM CC.qrtz_TRIGGERS WHERE SCHED_NAME = 'Inbound' AND JOB_NAME = 'P_E_FAX:ftp:fax-event' AND JOB_GROUP = 'P_E_FAX'

计划程序配置：

<bean id="quartzScheduler"  class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
    <property name="applicationContextSchedulerContextKey" value="applicationContext" />
    <property name="autoStartup" value="false" />
    <property name="dataSource">
        <jee:jndi-lookup jndi-name="jdbc/BASEA" expected-type="javax.sql.DataSource" />
    </property>
    <property name="jobFactory"><null/></property>
    <property name="nonTransactionalDataSource">
        <jee:jndi-lookup jndi-name="jdbc/BASEA_NON_XA" expected-type="javax.sql.DataSource" />
    </property>
    <property name="schedulerName" value="INBOUND" />
    <property name="taskExecutor" ref="taskExecutor" />
    <property name="waitForJobsToCompleteOnShutdown" value="true" />
    <property name="quartzProperties">
        <props>
            <prop key="org.quartz.jobStore.driverDelegateClass">org.quartz.impl.jdbcjobstore.StdJDBCDelegate</prop>
            <prop key="org.quartz.jobStore.isClustered">true</prop>
            <prop key="org.quartz.jobStore.selectWithLockSQL">SELECT * FROM {0}LOCKS WHERE SCHED_NAME = {1} AND LOCK_NAME = ? FOR UPDATE WITH RS</prop>
            <prop key="org.quartz.jobStore.tablePrefix">CC.qrtz_</prop>
            <prop key="org.quartz.jobStore.txIsolationLevelReadCommitted">true</prop>
            <prop key="org.quartz.scheduler.instanceId">AUTO</prop>
            <prop key="org.quartz.scheduler.skipUpdateCheck">true</prop>
            <prop key="org.quartz.threadExecutor.class">de.project.common.scheduler.LocalTaskExecutorThreadExecutor</prop>
        </props>
    </property>
</bean>

org\quartz\impl\jdbcjobstore\**stdjdbcdelegate#selectTriggersforjob()**生成“结果集已关闭”jdbc异常：

PreparedStatement ps = null;
ResultSet rs = null;

try {
    ps = conn.prepareStatement(rtp(SELECT_TRIGGERS_FOR_JOB));
    ps.setString(1, jobKey.getName());
    ps.setString(2, jobKey.getGroup());
    rs = ps.executeQuery();

    while (rs.next()) {  // !!! exception here on the second call !!!
        OperableTrigger t = selectTrigger(conn,     triggerKey(rs.getString(COL_TRIGGER_NAME), rs.getString(COL_TRIGGER_GROUP)));
        if(t != null) {
            trigList.add(t);
        }
    }
} finally {
    closeResultSet(rs);
    closeStatement(ps);
}

DB2ResultSet.java的反编译部分：

boolean nextX() throws SQLException {
    this.agent_.beforeExecution(this.statement_);

    boolean var8;
    try {
        if (this.returnFalseOnNextForClosedRS_ && !this.openOnClient_) {
            this.agent_.checkForDeferredExceptions();
            var8 = false;
            return var8;
        }
    this.checkForClosedResultSet();  // raise an exception
    this.clearWarningsX();
    this.moveToCurrentRowX();
    //....
}

上述反编译的简短调试：

1. 首次调用

rs.next（）：openOnClient==true,returnFalseOnNextForClosedRS==false,返回true

rs.next（）：openOnClient==false,returnFalseOnNextForClosedRS==false

引发异常（在checkForClosedResultSet上）

完整StackTrace：

org.quartz.JobPersistenceException: Couldn't obtain triggers for job: [jcc][t4][10120][10898][4.22.38] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null [See nested exception: com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][4.22.38] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null]:.java.lang.IllegalStateException: org.quartz.JobPersistenceException: Couldn't obtain triggers for job: [jcc][t4][10120][10898][4.22.38] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null [See nested exception: com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][4.22.38] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null]
    at de.project.inbound.execution.batch.BatchExecutorManager.deleteJobsForConfiguration(BatchExecutorManager.java:100)
    at de.project.amo.trigger.server.handler.BatchExecutorHelper.deleteJobs(BatchExecutorHelper.java:60)
    at de.project.amo.trigger.server.handler.PersistTriggerConfigHandler.execute(PersistTriggerConfigHandler.java:63)
    at de.project.amo.trigger.server.handler.PersistTriggerConfigHandler.execute(PersistTriggerConfigHandler.java:22)
    at sun.reflect.GeneratedMethodAccessor192.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:55)
    at java.lang.reflect.Method.invoke(Method.java:508)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.transaction.interceptor.TransactionInterceptor$$Lambda$263.0000000029FE7610.proceedWithInvocation(Unknown Source)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
    at com.sun.proxy.$Proxy101.execute(Unknown Source)
    at net.customware.gwt.dispatch.server.AbstractDispatch.doExecute(AbstractDispatch.java:81)
    at net.customware.gwt.dispatch.server.AbstractDispatch.execute(AbstractDispatch.java:68)
    at de.project.amo.common.server.security.SecureSpringDispatch.execute(SecureSpringDispatch.java:89)
    at net.customware.gwt.dispatch.server.secure.AbstractSecureDispatchServlet.execute(AbstractSecureDispatchServlet.java:27)
    at sun.reflect.GeneratedMethodAccessor191.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:55)
    at java.lang.reflect.Method.invoke(Method.java:508)
    at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:587)
    at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:333)
    at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:303)
    at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:373)
    at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1235)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:779)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:478)
    at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:178)
    at com.ibm.ws.webcontainer.filter.WebAppFilterChain.invokeTarget(WebAppFilterChain.java:143)
    at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:96)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:158)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:74)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:357)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:270)
    at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:197)
    at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:90)
    at de.project.amo.common.server.filter.BrowserCacheFilter.doFilter(BrowserCacheFilter.java:248)
    at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:197)
    at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:90)
    at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:969)
    at com.ibm.ws.webcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:1109)
    at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:82)
    at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:963)
    at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1817)
    at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:382)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:465)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest(HttpInboundLink.java:532)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest(HttpInboundLink.java:318)
    at com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:88)
    at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:175)
    at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
    at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
    at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138)
    at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204)
    at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775)
    at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905)
    at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1909)
    Caused by: org.quartz.JobPersistenceException: Couldn't obtain triggers for job: [jcc][t4][10120][10898][4.22.38] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null [See nested exception: com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][4.22.38] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null]
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.getTriggersForJob(JobStoreSupport.java:2190)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport$29.execute(JobStoreSupport.java:2176)
    at org.quartz.impl.jdbcjobstore.JobStoreCMT.executeInLock(JobStoreCMT.java:245)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeWithoutLock(JobStoreSupport.java:3785)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.getTriggersForJob(JobStoreSupport.java:2173)
    at org.quartz.core.QuartzScheduler.getTriggersOfJob(QuartzScheduler.java:1445)
    at org.quartz.core.QuartzScheduler.deleteJob(QuartzScheduler.java:958)
    at org.quartz.impl.StdScheduler.deleteJob(StdScheduler.java:301)
    at de.project.inbound.execution.batch.BatchExecutorManager.deleteJobsInGroup(BatchExecutorManager.java:165)
    at de.project.inbound.execution.batch.BatchExecutorManager.deleteJobsForConfiguration(BatchExecutorManager.java:97)
    ... 90 more
    Caused by: com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][4.22.38] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
    at com.ibm.db2.jcc.am.ld.a(ld.java:794)
    at com.ibm.db2.jcc.am.ld.a(ld.java:66)
    at com.ibm.db2.jcc.am.ld.a(ld.java:116)
    at com.ibm.db2.jcc.am.ResultSet.checkForClosedResultSet(ResultSet.java:4753)
    at com.ibm.db2.jcc.am.ResultSet.nextX(ResultSet.java:394)
    at com.ibm.db2.jcc.am.ResultSet.next(ResultSet.java:373)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.next(WSJdbcResultSet.java:3124)
    at org.quartz.impl.jdbcjobstore.StdJDBCDelegate.selectTriggersForJob(StdJDBCDelegate.java:1703)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.getTriggersForJob(JobStoreSupport.java:2187)
    ... 99 more

Answer 1

我想我发现问题是关于事务管理器的不正确配置。将其改为JTA后，问题消失了。