如何在超时后重新建立JDBC连接？ [英] How to reestablish a JDBC connection after a timeout?

问题描述

我有一个长期运行的方法，它通过EntityManager（TopLink Essentials）执行大量本机SQL查询。每个查询只需几毫秒即可运行，但其中有数千个查询。这发生在单个EJB事务中。 15分钟后，数据库关闭连接，导致以下错误：

I have a long-running method which executes a large number of native SQL queries through the EntityManager (TopLink Essentials). Each query takes only milliseconds to run, but there are many thousands of them. This happens within a single EJB transaction. After 15 minutes, the database closes the connection which results in following error:

Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.1 (Build b02-p04 (04/12/2010))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Closed Connection
Error Code: 17008
Call: select ...
Query: DataReadQuery()
at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:319)
.
.
.
RAR5031:System Exception.
javax.resource.ResourceException: This Managed Connection is not valid as the phyiscal connection is not usable
at com.sun.gjc.spi.ManagedConnection.checkIfValid(ManagedConnection.java:612)

在JDBC连接池中，我设置 is-connection-validation-required =true和 connection-validation-method =table但这没有帮助。

In the JDBC connection pool I set is-connection-validation-required="true" and connection-validation-method="table" but this did not help .

我假设JDBC连接验证正是为了处理这种错误。我还查看了TopLink扩展（http://www.oracle.com/technetwork/middleware/ias/toplink-jpa-extensions-094393.html）的某些超时设置，但一无所获。还有TopLink会话配置文件（http://download.oracle.com/docs/cd/B14099_19/web.1012/b15901/sessions003.htm），但我认为其中没有任何有用的东西。

I assumed that JDBC connection validation is there to deal with precisely this kind of errors. I also looked at TopLink extensions (http://www.oracle.com/technetwork/middleware/ias/toplink-jpa-extensions-094393.html) for some kind of timeout settings but found nothing. There is also the TopLink session configuration file (http://download.oracle.com/docs/cd/B14099_19/web.1012/b15901/sessions003.htm) but I don't think there is anything useful there either.

我无法访问Oracle DBA表，但我认为Oracle根据CONNECT_TIME配置文件变量中的设置在15分钟后关闭连接。

I don't have access to the Oracle DBA tables, but I think that Oracle closes connections after 15 minutes according to the setting in CONNECT_TIME profile variable.

是否有其他方法可以使TopLink或JDBC池重新建立已关闭的连接？

Is there any other way to make TopLink or the JDBC pool to reestablish a closed connection?

数据库是Oracle 10g，应用程序服务器是Sun Glassfish 2.1.1。

The database is Oracle 10g, application server is Sun Glassfish 2.1.1.

推荐答案

除非您有某种RAC故障转移，否则当连接终止时，它将结束会话和交易。

Unless you've got some sort of RAC failover, when the connection is terminated, it will end the session and transaction.

管理员可能设置了一些限制，以防止失控的交易或单个作业占用池中的连接。您通常不希望长时间锁定池中的连接。

The admins may have set into some limits to prevent runaway transactions or a single job 'hogging' a connection in a pool. You generally don't want to lock a connection in a pool for an extended period.

如果这些查询不一定是同一交易的一部分，那么您可以尝试终止并重新启动新连接。

If these queries aren't necessarily part of the same transaction, then you could try terminating and restarting a new connection.

您是否能够重新构建代码，以便在15分钟内完成。后台存储过程可能比通过网络拖动数千个操作的结果更快地完成工作。

Are you able to restructure your code so that it completes in under 15 minutes. A stored procedure in the background may be able to do the job a lot quicker than dragging the results of thousands of operations over the network.

这篇关于如何在超时后重新建立JDBC连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！