oracle 非活动时间后连接挂起
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1888923/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Connection hangs after time of inactivity
提问by sinuhepop
In my application, Spring manages connection pool for database access. Hibernate uses these connections for its queries. At first glance, I have no problems with the pool: it works correctly with concurrent clients and a pool with only one connection. I can execute a lot of queries, so I think that I (or Spring) don't leave open connections.
在我的应用程序中，Spring 管理用于数据库访问的连接池。Hibernate 使用这些连接进行查询。乍一看，我对池没有任何问题：它与并发客户端和只有一个连接的池一起正常工作。我可以执行很多查询，所以我认为我（或 Spring）不会留下打开的连接。
My problem appears after some time of inactivity (sometimes 30 minutes, sometimes more than 2 hours). Then, when Hibernate does some search, it lasts too much. Setting log4j level to TRACE, I get this logs:
我的问题在一段时间不活动（有时 30 分钟，有时超过 2 小时）后出现。然后，当 Hibernate 进行一些搜索时，它会持续太多时间。将 log4j 级别设置为 TRACE，我得到以下日志：
...
18:27:01 DEBUG nsactionSynchronizationManager - Retrieved value [org.springframework.orm.hibernate3.SessionHolder@99abd7] for key [org.hibernate.impl.SessionFactoryImpl@7d2897] bound to thread [http-8080-Processor24]
18:27:01 DEBUG HibernateTransactionManager - Found thread-bound Session [org.hibernate.impl.SessionImpl@8878cd] for Hibernate transaction
18:27:01 DEBUG HibernateTransactionManager - Using transaction object [org.springframework.orm.hibernate3.HibernateTransactionManager$HibernateTransactionObject@1b2ffee]
18:27:01 DEBUG HibernateTransactionManager - Creating new transaction with name [com.acjoventut.service.GenericManager.findByExample]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
18:27:01 DEBUG HibernateTransactionManager - Preparing JDBC Connection of Hibernate Session [org.hibernate.impl.SessionImpl@8878cd]
18:27:01 TRACE SessionImpl - setting flush mode to: AUTO
18:27:01 DEBUG JDBCTransaction - begin
18:27:01 DEBUG ConnectionManager - opening JDBC connection
Here it gets frozen for about 2 - 10 minutes. But then continues:
在这里它会冻结大约 2 - 10 分钟。但接着继续：
18:30:11 DEBUG JDBCTransaction - current autocommit status: true
18:30:11 DEBUG JDBCTransaction - disabling autocommit
18:30:11 TRACE JDBCContext - after transaction begin
18:30:11 DEBUG HibernateTransactionManager - Exposing Hibernate transaction as JDBC transaction [jdbc:oracle:thin:@212.31.39.50:30998:orcl, UserName=DEVELOP, Oracle JDBC driver]
18:30:11 DEBUG nsactionSynchronizationManager - Bound value [org.springframework.jdbc.datasource.ConnectionHolder@843a9d] for key [org.apache.commons.dbcp.BasicDataSource@7745fd] to thread [http-8080-Processor24]
18:30:11 DEBUG nsactionSynchronizationManager - Initializing transaction synchronization
...
After that, it works with no problems, until another period of inactivity. IMHO, it seems like connection pool returns an invalid/closed connection, and when Hibernate realizes that, ask another connection to the pool.
在那之后，它可以正常工作，直到另一段不活动时间。恕我直言，似乎连接池返回了一个无效/关闭的连接，当 Hibernate 意识到这一点时，向池中请求另一个连接。
I don't know how can I solve this problem or things I can do for delimiting it. Any help achieving this will be appreciate.
我不知道如何解决这个问题或我可以做些什么来界定它。任何帮助实现这一点将不胜感激。
Thanks.
谢谢。
EDIT: Well, it finally was due a firewall rule. Database detects the connection is lost, but pool (dbcp or c3p0) not. So, it tries to query the database with no success. What is still strange for me is that timeout period is very variable. Maybe the rule is specially strange or firewall doesn't work correctly. Anyway, I have no access to that machine and I can only wait for an explanation. :(
编辑：嗯，最后是由于防火墙规则。数据库检测到连接丢失，但池（dbcp 或 c3p0）没有。因此，它尝试查询数据库但没有成功。对我来说仍然奇怪的是超时时间非常可变。也许规则特别奇怪或者防火墙不能正常工作。无论如何，我无法访问那台机器，我只能等待解释。:(
采纳答案by Joe
I've had problems like this before when the database is on a seperate box and there's a firewall in between which is set to timeout idle connections.
我以前遇到过这样的问题，当数据库在一个单独的盒子上并且中间有一个防火墙设置为超时空闲连接时。
In some circumstances the firewall cuts off the connection in such a way that the JDBC end doesn't detect, and attempting to use it results in an indefinite block.
在某些情况下，防火墙会以 JDBC 端无法检测到的方式切断连接，尝试使用它会导致无限期阻塞。
In my case it was a custom connection pool which sent a test query down the connection before returning it from the pool. I configured this test query to have a timeout (using Statement.setQueryTimeout) so that it didn't block indefinitely.
在我的例子中，它是一个自定义连接池，它在从池中返回之前通过连接发送了一个测试查询。我将此测试查询配置为具有超时（使用 Statement.setQueryTimeout），以便它不会无限期地阻塞。
回答by Jayadp
One way to resolve idle time out issue is to have dual connection pools, one is active and other one is standby (no connections created yet). Have a timer with trigger time much less than FIREWALL_IDLE_TIMEOUT and switch between connection pools. I tried this and ITS WORKING.
解决空闲超时问题的一种方法是使用双连接池，一个是活动的，另一个是备用的（尚未创建连接）。有一个触发时间远小于 FIREWALL_IDLE_TIMEOUT 的计时器并在连接池之间切换。我试过这个并且它正在工作。
回答by MIJAEL
you have to add some parameters in your DataSource:
你必须在你的数据源中添加一些参数：
more important add testOnBorrow and validationQuery
更重要的是添加 testOnBorrow 和 validationQuery
回答by kman
We solved problem with similar symptoms which also turned out to be caused by a firewall.
我们解决了具有类似症状的问题，这些问题也被证明是由防火墙引起的。
We were able to work around the problem by changing the testWhileIdle connection pool property which prevents the connection from going idle and from the firewall shutting down the connection. See Apache commons dbcp BasicDataSource. Here is an exert from the configuration file, persistentce-context.xml that fixed the problem:
我们能够通过更改 testWhileIdle 连接池属性来解决此问题，该属性可防止连接闲置和防火墙关闭连接。请参阅Apache 公共 dbcp BasicDataSource。这是配置文件persistencece-context.xml 中的一个解决问题的应用：
<property name="testWhileIdle">
<value>true</value>
</property>
<property name="minEvictableIdleTimeMillis">
<value>600000</value>
</property>
<property name="timeBetweenEvictionRunsMillis">
<value>600000</value>
</property>
Most likely we only need to add the testWhileIdle (false by default), but added the other two properties for good measure.
很可能我们只需要添加 testWhileIdle（默认为 false），但添加了其他两个属性以获得良好的度量。
In our case, here are some of the logs which we were seeing. Notice in this debug logs, it would take 16 minutes to open the connection before the connection could be used and this is what caused everything to hang. There were no errors which made it hard to track down.
在我们的例子中，这里是我们看到的一些日志。请注意，在此调试日志中，打开连接需要 16 分钟才能使用连接，这就是导致一切挂起的原因。没有任何错误难以追查。
09-06-13 @ 16:36:34 [DEBUG] HibernateTransactionManager - Preparing JDBC Connection of Hibernate Session [org.hibernate.impl.SessionImpl@db17ab]
09-06-13 @ 16:36:34 [DEBUG] ConnectionManager - opening JDBC connection
09-06-13 @ 16:52:00 [DEBUG] DataSourceUtils - Setting JDBC Connection
09-06-13 @ 16:52:00 [DEBUG] JDBCTransaction - begin
09-06-13 @ 16:52:00 [DEBUG] JDBCTransaction - current autocommit status: true
回答by Aaron Digulla
Check the config of your pool implementation. Usually, it's Apache DBCPwhich has a timeoutfor each connection after it will close it.
检查池实现的配置。通常，它是Apache DBCP，它在关闭每个连接后都会超时。
In your code, you shouldn't keep connections around. Get one, use it, close it immediately. The pool will make sure that this doesn't cost too much.
在您的代码中，您不应该保持连接。得到一个，使用它，立即关闭它。游泳池将确保这不会花费太多。
