连接在闲置时间后挂起 [英] Connection hangs after time of inactivity

问题描述

在我的应用程序中，Spring管理数据库访问的连接池。 Hibernate使用这些连接进行查询。乍一看，我对池没有任何问题：它对并发客户端和只有一个连接的池正常工作。我可以执行很多查询，所以我认为我（或Spring）不会留下开放的连接。

我的问题在一段时间不活动后出现（有时是30分钟，有时超过2小时）。然后，当Hibernate进行一些搜索时，它会持续太久。设置log4j级别为TRACE，我得到这个日志：

  ... 
 18:27:01 DEBUG nsactionSynchronizationManager  - 绑定到线程[http-8080-Processor24] 
 18:27:01的密钥[org.hibernate.impl.SessionFactoryImpl@7d2897]的检索值[org.springframework.orm.hibernate3.SessionHolder@99abd7] DEBUG HibernateTransactionManager  - 发现线程绑定Session [org.hibernate.impl.SessionImpl@8878cd]用于Hibernate事务
 18:27:01 DEBUG HibernateTransactionManager  - 使用事务对象[org.springframework.orm.hibernate3.HibernateTransactionManager$HibernateTransactionObject@1b2ffee] 
 18:27:01 DEBUG HibernateTransactionManager  - 使用名称[com.acjoventut.service.GenericManager.findByExample]创建新事务：PROPAGATION_REQUIRED，ISOLATION_DEFAULT 
 18:27:01 DEBUG HibernateTransactionManager  - 准备Hibernate连接的JDBC会话[ org.hibernate.impl.SessionImpl@8878cd] 
 18:27:01 TRACE SessionImpl - 将刷新模式设置为：AUTO 
 18:27:01 DEBUG JDBCTransaction  -  begin 
 18:27:01 DEBUG ConnectionManager  - 打开JDBC连接
  

在这里它会被冻结2-10分钟。但继续：

  18:30:11 DEBUG JDBCTransaction  - 当前自动提交状态：true 
 18:30:11 DEBUG JDBCTransaction  - 禁用自动提交
 18:30:11 TRACE JDBCContext  - 事务开始后
 18:30:11 DEBUG HibernateTransactionManager  - 将Hibernate事务公开为JDBC事务[jdbc：oracle：thin：@ 212.31.39.50： 30998：orcl，UserName = DEVELOP，Oracle JDBC驱动程序] 
 18:30:11 DEBUG nsactionSynchronizationManager  - 绑定值[org.springframework.jdbc.datasource.ConnectionHolder@843a9d]，其中键为[org.apache.commons.dbcp。 BasicDataSource @ 7745fd]到线程[http-8080-Processor24] 
 18:30:11 DEBUG nsactionSynchronizationManager  - 初始化事务同步
 ... 
  

之后，它可以毫无问题地工作，直到另一段时间不活动。恕我直言，它似乎像连接池返回一个无效/关闭连接，当Hibernate意识到这一点时，要求另一个连接池。

我不知道我该如何解决这个问题，或者我可以做些什么来分隔它。任何帮助实现这一点将不胜感激。

谢谢。

编辑：由于防火墙规则。数据库检测到连接丢失，但池（dbcp或c3p0）不是。所以，它试图查询数据库没有成功。对我来说，仍然很奇怪的是，超时时间非常多变。也许这个规则特别奇怪，或者防火墙无法正常工作。无论如何，我无法访问该机器，我只能等待解释。 ：（）

解决方案

在数据库位于单独的盒子上时，防火墙之间设置为超时闲置连接。

在某些情况下，防火墙会切断连接，使JDBC端不会检测到并尝试在我的情况下，它是一个自定义连接池，它在从连接池返回它之前向连接发送测试查询。我配置了此测试查询有一个超时（使用Statement.setQueryTimeout），以便它不会无限期地阻止。

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.

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:

...
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:

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.

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. :(

解决方案

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.

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.

这篇关于连接在闲置时间后挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！