阅读背景：

java thread在使用executorService执行oracle查询时挂起

来源：互联网  
"pool-11-thread-1" prio=10 tid=0x0a974c00 nid=0x7210 runnable [0x3f3ad000]
   java.lang.Thread.State: RUNNABLE
  at oracle.jdbc.driver.T2CStatement.t2cDefineExecuteFetch(Native Method)
  at oracle.jdbc.driver.T2CPreparedStatement.doDefineExecuteFetch(T2CPreparedStatement.java:878)
  at oracle.jdbc.driver.T2CPreparedStatement.executeForRows(T2CPreparedStatement.java:760)
  at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1062)
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1126)
  at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
  at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)
  - locked <0x69579fb0> (a oracle.jdbc.driver.T2CPreparedStatement)
  - locked <0x66157d68> (a oracle.jdbc.driver.T2CConnection)
  at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.execute(CachedPreparedStatement.java:216)
  at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:209)
  at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:180)
  at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:205)
  at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
  at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104)
  at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:561)
  at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:536)
  at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:93)
  at org.springframework.orm.ibatis.SqlMapClientTemplate
 
 
"pool-11-thread-1" prio=10 tid=0x0a974c00 nid=0x7210 runnable [0x3f3ad000]
   java.lang.Thread.State: RUNNABLE
  at oracle.jdbc.driver.T2CStatement.t2cDefineExecuteFetch(Native Method)
  at oracle.jdbc.driver.T2CPreparedStatement.doDefineExecuteFetch(T2CPreparedStatement.java:878)
  at oracle.jdbc.driver.T2CPreparedStatement.executeForRows(T2CPreparedStatement.java:760)
  at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1062)
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1126)
  at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
  at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)
  - locked <0x69579fb0> (a oracle.jdbc.driver.T2CPreparedStatement)
  - locked <0x66157d68> (a oracle.jdbc.driver.T2CConnection)
  at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.execute(CachedPreparedStatement.java:216)
  at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:209)
  at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:180)
  at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:205)
  at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
  at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104)
  at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:561)
  at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:536)
  at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:93)
  at org.springframework.orm.ibatis.SqlMapClientTemplate$1.doInSqlMapClient(SqlMapClientTemplate.java:273)
  at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)
  at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForObject(SqlMapClientTemplate.java:271)
  at com.alipay.bipgw.common.dal.bankchannel.ibatis.IbatisBipBusiOrderDAO.queryOrderOutTime(IbatisBipBusiOrderDAO.java:319)
  at sun.reflect.GeneratedMethodAccessor3333.invoke(Unknown Source)
  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:310)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
  at com.alipay.bipgw.common.dal.monitor.DalMonitorInterceptor.invoke(DalMonitorInterceptor.java:60)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
  at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
  at $Proxy79.queryOrderOutTime(Unknown Source)
  at com.alipay.bipgw.prodcore.repository.impl.BusiOrderRepositoryImpl.queryOrderOutTime(BusiOrderRepositoryImpl.java:402)
  at com.alipay.bipgw.prodcore.listener.ProdStatusChangeTimeoutTaskListener.execute(ProdStatusChangeTimeoutTaskListener.java:148)
  at com.alipay.bipgw.prodcore.listener.ProdStatusChangeTimeoutTaskListener.access$000(ProdStatusChangeTimeoutTaskListener.java:60)
  at com.alipay.bipgw.prodcore.listener.ProdStatusChangeTimeoutTaskListener$1.run(ProdStatusChangeTimeoutTaskListener.java:104)
  at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
  at java.lang.Thread.run(Thread.java:662)

 
 
the java programm like this :
 
  
  
 
像这样的java程序:
 
 
public void onUniformEvent(UniformEvent message, UniformEventContext uContext) {
    try {
        // single thread running
        service.execute(new Runnable() {
            public void run() {
                try{
                    execute();
                }catch(Exception e ){
                    logger.error("working error",e);
                }

            }
        });
    } catch (RejectedExecutionException e) {
        logger.error("ProdStatusChangeTimeoutTaskListener:error", e);
    } catch (Exception e) {
        logger.error("ProdStatusChangeTimeoutTaskListener:error", e);
    }

}
 //omit the body
 private void execute() {.....}

 
 
and the execute method will not start any thread.
 
 
并且execute方法不会启动任何线程。
 
 
in two days i dump several thread dump 2013-03-04 16:54:12
 
 
在两天内我转储几个线程转储2013-03-04 16:54:12
 
 
- locked <0x695f91f0> (a oracle.jdbc.driver.T2CPreparedStatement)
- locked <0x6615a2d0> (a oracle.jdbc.driver.T2CConnection)  

 
 
2013-03-04 17:20:53
 
 
- locked <0x695f91f0> (a oracle.jdbc.driver.T2CPreparedStatement)
- locked <0x6615a2d0> (a oracle.jdbc.driver.T2CConnection)

 
  
  
 
2013-03-05 10:58:30
 
 
- locked <0x6957bec8> (a oracle.jdbc.driver.T2CPreparedStatement)
- locked <0x66157e90> (a oracle.jdbc.driver.T2CConnection)

 
 
2013-03-05 17:16:31
 
 
- locked <0x69579fb0> (a oracle.jdbc.driver.T2CPreparedStatement)
- locked <0x66157d68> (a oracle.jdbc.driver.T2CConnection)

 
 
seems like the lock hold by jdbc client has changed, but the first two in 2013-03-04 16:54:12 and 2013-03-04 17:20:53, they are the same
 
 
似乎jdbc客户端的锁持有变化,但前两个在2013-03-04 16:54:12和2013-03-04 17:20:53,它们是一样的
 
 
I am using a Excutors.newSingleThreadExecutor() doing a query job in backgroud, and the following task will be submit to this executor Service in 20 minutes interval, but the work thread seems to hangs while executing the query , so the following task will not be executed. It last for several days , no exception occur and no log output at all, somebody can help me ? thanks
 
 
我正在使用Excutors.newSingleThreadExecutor()在backgroud中执行查询作业,并且以下任务将在20分钟间隔内提交给此执行程序服务,但工作线程似乎在执行查询时挂起,因此以下任务不会被执行。它持续了好几天,没有异常发生,根本没有日志输出,有人可以帮助我吗?谢谢
 
  
  
 
2 个解决方案
 
 
 
  
#1
 
  
 
  
 
   
0  
 
   
The problem is likely to lie in your actual query - is it a long running task? Do you know how long on average it takes to complete?
 
   
问题可能在于您的实际查询 - 这是一项长期运行的任务吗?你知道完成平均需要多长时间吗?
 
   
Its possible (but this is dependent on the query itself) that a previous query is locking tables which in turn block later ones.
 
   
它可能(但这取决于查询本身)先前的查询是锁定表,而后者又阻塞了后续的表。
 
   
The first thing I would do it to verify that the query does indeed complete within 20 minutes, and does so consistently. After you know that it does you can then investigate other possible causes for the hanging behavior you're seeing.
 
   
我要做的第一件事是验证查询确实在20分钟内完成,并且一直这样做。在您知道它之后,您可以调查您所看到的悬挂行为的其他可能原因。
 
   
When the query is running I'd also suggest that you check the explain plan for your query (see what its doing - whats taking the most time etc). I'd also check that the relevant statistics are up to date (perhaps less of an issue on newer versions of Oracle)
 
   
当查询运行时,我还建议您检查查询的解释计划(看看它在做什么 - 最多花些时间等等)。我还要检查相关的统计信息是否是最新的(可能不是新版Oracle的问题)
 
  
 
 
 
 
 
  
#2
 
  
 
  
 
   
0  
 
   
This problem has been solved~ after several real time executes , out dba monitor the db server side, and find that the query time is 210s which execeeds the query-out-time(180s), but the cancel request of the stamentent may not be effective and the db server may not response to that request . so the thread which porform the original query hangs. the similar situation see [When I call PreparedStatement.cancel() in a JDBC application, does it actually kill it in an Oracle database?
 
   
这个问题已经解决了〜几次实时执行后,dba监视数据库服务器端,发现​​查询时间是210s,超出了查询输出时间(180s),但stamentent的取消请求可能不是有效且数据库服务器可能无法响应该请求。所以对原始查询进行处理的线程会挂起。类似的情况见[当我在JDBC应用程序中调用PreparedStatement.cancel()时,它是否真的在Oracle数据库中将其杀掉?
 
   
the solution can been done by following: add sqlprofile to the target table，and make sql using skip scan to short the query time. 
 
   
解决方案可以通过以下方式完成:将sqlprofile添加到目标表,并使用跳过扫描生成sql以缩短查询时间。
 
   
but the deepest reason why hanging will occur is still unknown. 
 
   
但悬挂将发生的最深层原因仍然未知。
 
  
 
 
 


.doInSqlMapClient(SqlMapClientTemplate.java:273)
  at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)
  at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForObject(SqlMapClientTemplate.java:271)
  at com.alipay.bipgw.common.dal.bankchannel.ibatis.IbatisBipBusiOrderDAO.queryOrderOutTime(IbatisBipBusiOrderDAO.java:319)
  at sun.reflect.GeneratedMethodAccessor3333.invoke(Unknown Source)
  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:310)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
  at com.alipay.bipgw.common.dal.monitor.DalMonitorInterceptor.invoke(DalMonitorInterceptor.java:60)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
  at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
  at $Proxy79.queryOrderOutTime(Unknown Source)
  at com.alipay.bipgw.prodcore.repository.impl.BusiOrderRepositoryImpl.queryOrderOutTime(BusiOrderRepositoryImpl.java:402)
  at com.alipay.bipgw.prodcore.listener.ProdStatusChangeTimeoutTaskListener.execute(ProdStatusChangeTimeoutTaskListener.java:148)
  at com.alipay.bipgw.prodcore.listener.ProdStatusChangeTimeoutTaskListener.access
 
 
"pool-11-thread-1" prio=10 tid=0x0a974c00 nid=0x7210 runnable [0x3f3ad000]
   java.lang.Thread.State: RUNNABLE
  at oracle.jdbc.driver.T2CStatement.t2cDefineExecuteFetch(Native Method)
  at oracle.jdbc.driver.T2CPreparedStatement.doDefineExecuteFetch(T2CPreparedStatement.java:878)
  at oracle.jdbc.driver.T2CPreparedStatement.executeForRows(T2CPreparedStatement.java:760)
  at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1062)
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1126)
  at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
  at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)
  - locked <0x69579fb0> (a oracle.jdbc.driver.T2CPreparedStatement)
  - locked <0x66157d68> (a oracle.jdbc.driver.T2CConnection)
  at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.execute(CachedPreparedStatement.java:216)
  at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:209)
  at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:180)
  at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:205)
  at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
  at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104)
  at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:561)
  at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:536)
  at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:93)
  at org.springframework.orm.ibatis.SqlMapClientTemplate$1.doInSqlMapClient(SqlMapClientTemplate.java:273)
  at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)
  at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForObject(SqlMapClientTemplate.java:271)
  at com.alipay.bipgw.common.dal.bankchannel.ibatis.IbatisBipBusiOrderDAO.queryOrderOutTime(IbatisBipBusiOrderDAO.java:319)
  at sun.reflect.GeneratedMethodAccessor3333.invoke(Unknown Source)
  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:310)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
  at com.alipay.bipgw.common.dal.monitor.DalMonitorInterceptor.invoke(DalMonitorInterceptor.java:60)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
  at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
  at $Proxy79.queryOrderOutTime(Unknown Source)
  at com.alipay.bipgw.prodcore.repository.impl.BusiOrderRepositoryImpl.queryOrderOutTime(BusiOrderRepositoryImpl.java:402)
  at com.alipay.bipgw.prodcore.listener.ProdStatusChangeTimeoutTaskListener.execute(ProdStatusChangeTimeoutTaskListener.java:148)
  at com.alipay.bipgw.prodcore.listener.ProdStatusChangeTimeoutTaskListener.access$000(ProdStatusChangeTimeoutTaskListener.java:60)
  at com.alipay.bipgw.prodcore.listener.ProdStatusChangeTimeoutTaskListener$1.run(ProdStatusChangeTimeoutTaskListener.java:104)
  at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
  at java.lang.Thread.run(Thread.java:662)

 
 
the java programm like this :
 
  
  
 
像这样的java程序:
 
 
public void onUniformEvent(UniformEvent message, UniformEventContext uContext) {
    try {
        // single thread running
        service.execute(new Runnable() {
            public void run() {
                try{
                    execute();
                }catch(Exception e ){
                    logger.error("working error",e);
                }

            }
        });
    } catch (RejectedExecutionException e) {
        logger.error("ProdStatusChangeTimeoutTaskListener:error", e);
    } catch (Exception e) {
        logger.error("ProdStatusChangeTimeoutTaskListener:error", e);
    }

}
 //omit the body
 private void execute() {.....}

 
 
and the execute method will not start any thread.
 
 
并且execute方法不会启动任何线程。
 
 
in two days i dump several thread dump 2013-03-04 16:54:12
 
 
在两天内我转储几个线程转储2013-03-04 16:54:12
 
 
- locked <0x695f91f0> (a oracle.jdbc.driver.T2CPreparedStatement)
- locked <0x6615a2d0> (a oracle.jdbc.driver.T2CConnection)  

 
 
2013-03-04 17:20:53
 
 
- locked <0x695f91f0> (a oracle.jdbc.driver.T2CPreparedStatement)
- locked <0x6615a2d0> (a oracle.jdbc.driver.T2CConnection)

 
  
  
 
2013-03-05 10:58:30
 
 
- locked <0x6957bec8> (a oracle.jdbc.driver.T2CPreparedStatement)
- locked <0x66157e90> (a oracle.jdbc.driver.T2CConnection)

 
 
2013-03-05 17:16:31
 
 
- locked <0x69579fb0> (a oracle.jdbc.driver.T2CPreparedStatement)
- locked <0x66157d68> (a oracle.jdbc.driver.T2CConnection)

 
 
seems like the lock hold by jdbc client has changed, but the first two in 2013-03-04 16:54:12 and 2013-03-04 17:20:53, they are the same
 
 
似乎jdbc客户端的锁持有变化,但前两个在2013-03-04 16:54:12和2013-03-04 17:20:53,它们是一样的
 
 
I am using a Excutors.newSingleThreadExecutor() doing a query job in backgroud, and the following task will be submit to this executor Service in 20 minutes interval, but the work thread seems to hangs while executing the query , so the following task will not be executed. It last for several days , no exception occur and no log output at all, somebody can help me ? thanks
 
 
我正在使用Excutors.newSingleThreadExecutor()在backgroud中执行查询作业,并且以下任务将在20分钟间隔内提交给此执行程序服务,但工作线程似乎在执行查询时挂起,因此以下任务不会被执行。它持续了好几天,没有异常发生,根本没有日志输出,有人可以帮助我吗?谢谢
 
  
  
 
2 个解决方案
 
 
 
  
#1
 
  
 
  
 
   
0  
 
   
The problem is likely to lie in your actual query - is it a long running task? Do you know how long on average it takes to complete?
 
   
问题可能在于您的实际查询 - 这是一项长期运行的任务吗?你知道完成平均需要多长时间吗?
 
   
Its possible (but this is dependent on the query itself) that a previous query is locking tables which in turn block later ones.
 
   
它可能(但这取决于查询本身)先前的查询是锁定表,而后者又阻塞了后续的表。
 
   
The first thing I would do it to verify that the query does indeed complete within 20 minutes, and does so consistently. After you know that it does you can then investigate other possible causes for the hanging behavior you're seeing.
 
   
我要做的第一件事是验证查询确实在20分钟内完成,并且一直这样做。在您知道它之后,您可以调查您所看到的悬挂行为的其他可能原因。
 
   
When the query is running I'd also suggest that you check the explain plan for your query (see what its doing - whats taking the most time etc). I'd also check that the relevant statistics are up to date (perhaps less of an issue on newer versions of Oracle)
 
   
当查询运行时,我还建议您检查查询的解释计划(看看它在做什么 - 最多花些时间等等)。我还要检查相关的统计信息是否是最新的(可能不是新版Oracle的问题)
 
  
 
 
 
 
 
  
#2
 
  
 
  
 
   
0  
 
   
This problem has been solved~ after several real time executes , out dba monitor the db server side, and find that the query time is 210s which execeeds the query-out-time(180s), but the cancel request of the stamentent may not be effective and the db server may not response to that request . so the thread which porform the original query hangs. the similar situation see [When I call PreparedStatement.cancel() in a JDBC application, does it actually kill it in an Oracle database?
 
   
这个问题已经解决了〜几次实时执行后,dba监视数据库服务器端,发现​​查询时间是210s,超出了查询输出时间(180s),但stamentent的取消请求可能不是有效且数据库服务器可能无法响应该请求。所以对原始查询进行处理的线程会挂起。类似的情况见[当我在JDBC应用程序中调用PreparedStatement.cancel()时,它是否真的在Oracle数据库中将其杀掉?
 
   
the solution can been done by following: add sqlprofile to the target table，and make sql using skip scan to short the query time. 
 
   
解决方案可以通过以下方式完成:将sqlprofile添加到目标表,并使用跳过扫描生成sql以缩短查询时间。
 
   
but the deepest reason why hanging will occur is still unknown. 
 
   
但悬挂将发生的最深层原因仍然未知。
 
  
 
 
 


0(ProdStatusChangeTimeoutTaskListener.java:60)
  at com.alipay.bipgw.prodcore.listener.ProdStatusChangeTimeoutTaskListener
 
 
"pool-11-thread-1" prio=10 tid=0x0a974c00 nid=0x7210 runnable [0x3f3ad000]
   java.lang.Thread.State: RUNNABLE
  at oracle.jdbc.driver.T2CStatement.t2cDefineExecuteFetch(Native Method)
  at oracle.jdbc.driver.T2CPreparedStatement.doDefineExecuteFetch(T2CPreparedStatement.java:878)
  at oracle.jdbc.driver.T2CPreparedStatement.executeForRows(T2CPreparedStatement.java:760)
  at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1062)
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1126)
  at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
  at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)
  - locked <0x69579fb0> (a oracle.jdbc.driver.T2CPreparedStatement)
  - locked <0x66157d68> (a oracle.jdbc.driver.T2CConnection)
  at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.execute(CachedPreparedStatement.java:216)
  at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:209)
  at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:180)
  at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:205)
  at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
  at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104)
  at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:561)
  at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:536)
  at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:93)
  at org.springframework.orm.ibatis.SqlMapClientTemplate$1.doInSqlMapClient(SqlMapClientTemplate.java:273)
  at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)
  at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForObject(SqlMapClientTemplate.java:271)
  at com.alipay.bipgw.common.dal.bankchannel.ibatis.IbatisBipBusiOrderDAO.queryOrderOutTime(IbatisBipBusiOrderDAO.java:319)
  at sun.reflect.GeneratedMethodAccessor3333.invoke(Unknown Source)
  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:310)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
  at com.alipay.bipgw.common.dal.monitor.DalMonitorInterceptor.invoke(DalMonitorInterceptor.java:60)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
  at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
  at $Proxy79.queryOrderOutTime(Unknown Source)
  at com.alipay.bipgw.prodcore.repository.impl.BusiOrderRepositoryImpl.queryOrderOutTime(BusiOrderRepositoryImpl.java:402)
  at com.alipay.bipgw.prodcore.listener.ProdStatusChangeTimeoutTaskListener.execute(ProdStatusChangeTimeoutTaskListener.java:148)
  at com.alipay.bipgw.prodcore.listener.ProdStatusChangeTimeoutTaskListener.access$000(ProdStatusChangeTimeoutTaskListener.java:60)
  at com.alipay.bipgw.prodcore.listener.ProdStatusChangeTimeoutTaskListener$1.run(ProdStatusChangeTimeoutTaskListener.java:104)
  at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
  at java.lang.Thread.run(Thread.java:662)

 
 
the java programm like this :
 
  
  
 
像这样的java程序:
 
 
public void onUniformEvent(UniformEvent message, UniformEventContext uContext) {
    try {
        // single thread running
        service.execute(new Runnable() {
            public void run() {
                try{
                    execute();
                }catch(Exception e ){
                    logger.error("working error",e);
                }

            }
        });
    } catch (RejectedExecutionException e) {
        logger.error("ProdStatusChangeTimeoutTaskListener:error", e);
    } catch (Exception e) {
        logger.error("ProdStatusChangeTimeoutTaskListener:error", e);
    }

}
 //omit the body
 private void execute() {.....}

 
 
and the execute method will not start any thread.
 
 
并且execute方法不会启动任何线程。
 
 
in two days i dump several thread dump 2013-03-04 16:54:12
 
 
在两天内我转储几个线程转储2013-03-04 16:54:12
 
 
- locked <0x695f91f0> (a oracle.jdbc.driver.T2CPreparedStatement)
- locked <0x6615a2d0> (a oracle.jdbc.driver.T2CConnection)  

 
 
2013-03-04 17:20:53
 
 
- locked <0x695f91f0> (a oracle.jdbc.driver.T2CPreparedStatement)
- locked <0x6615a2d0> (a oracle.jdbc.driver.T2CConnection)

 
  
  
 
2013-03-05 10:58:30
 
 
- locked <0x6957bec8> (a oracle.jdbc.driver.T2CPreparedStatement)
- locked <0x66157e90> (a oracle.jdbc.driver.T2CConnection)

 
 
2013-03-05 17:16:31
 
 
- locked <0x69579fb0> (a oracle.jdbc.driver.T2CPreparedStatement)
- locked <0x66157d68> (a oracle.jdbc.driver.T2CConnection)

 
 
seems like the lock hold by jdbc client has changed, but the first two in 2013-03-04 16:54:12 and 2013-03-04 17:20:53, they are the same
 
 
似乎jdbc客户端的锁持有变化,但前两个在2013-03-04 16:54:12和2013-03-04 17:20:53,它们是一样的
 
 
I am using a Excutors.newSingleThreadExecutor() doing a query job in backgroud, and the following task will be submit to this executor Service in 20 minutes interval, but the work thread seems to hangs while executing the query , so the following task will not be executed. It last for several days , no exception occur and no log output at all, somebody can help me ? thanks
 
 
我正在使用Excutors.newSingleThreadExecutor()在backgroud中执行查询作业,并且以下任务将在20分钟间隔内提交给此执行程序服务,但工作线程似乎在执行查询时挂起,因此以下任务不会被执行。它持续了好几天,没有异常发生,根本没有日志输出,有人可以帮助我吗?谢谢
 
  
  
 
2 个解决方案
 
 
 
  
#1
 
  
 
  
 
   
0  
 
   
The problem is likely to lie in your actual query - is it a long running task? Do you know how long on average it takes to complete?
 
   
问题可能在于您的实际查询 - 这是一项长期运行的任务吗?你知道完成平均需要多长时间吗?
 
   
Its possible (but this is dependent on the query itself) that a previous query is locking tables which in turn block later ones.
 
   
它可能(但这取决于查询本身)先前的查询是锁定表,而后者又阻塞了后续的表。
 
   
The first thing I would do it to verify that the query does indeed complete within 20 minutes, and does so consistently. After you know that it does you can then investigate other possible causes for the hanging behavior you're seeing.
 
   
我要做的第一件事是验证查询确实在20分钟内完成,并且一直这样做。在您知道它之后,您可以调查您所看到的悬挂行为的其他可能原因。
 
   
When the query is running I'd also suggest that you check the explain plan for your query (see what its doing - whats taking the most time etc). I'd also check that the relevant statistics are up to date (perhaps less of an issue on newer versions of Oracle)
 
   
当查询运行时,我还建议您检查查询的解释计划(看看它在做什么 - 最多花些时间等等)。我还要检查相关的统计信息是否是最新的(可能不是新版Oracle的问题)
 
  
 
 
 
 
 
  
#2
 
  
 
  
 
   
0  
 
   
This problem has been solved~ after several real time executes , out dba monitor the db server side, and find that the query time is 210s which execeeds the query-out-time(180s), but the cancel request of the stamentent may not be effective and the db server may not response to that request . so the thread which porform the original query hangs. the similar situation see [When I call PreparedStatement.cancel() in a JDBC application, does it actually kill it in an Oracle database?
 
   
这个问题已经解决了〜几次实时执行后,dba监视数据库服务器端,发现​​查询时间是210s,超出了查询输出时间(180s),但stamentent的取消请求可能不是有效且数据库服务器可能无法响应该请求。所以对原始查询进行处理的线程会挂起。类似的情况见[当我在JDBC应用程序中调用PreparedStatement.cancel()时,它是否真的在Oracle数据库中将其杀掉?
 
   
the solution can been done by following: add sqlprofile to the target table，and make sql using skip scan to short the query time. 
 
   
解决方案可以通过以下方式完成:将sqlprofile添加到目标表,并使用跳过扫描生成sql以缩短查询时间。
 
   
but the deepest reason why hanging will occur is still unknown. 
 
   
但悬挂将发生的最深层原因仍然未知。
 
  
 
 
 


.run(ProdStatusChangeTimeoutTaskListener.java:104)
  at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
  at java.lang.Thread.run(Thread.java:662)
"pool-11-thread-1" prio=10 tid=0x0a974c00 nid=0


你的当前访问异常,请进行认证后继续阅读剩余内容。

分享到：
你可能喜欢:
相关阅读:
随便看看: