Connection hangs after time of inactivity

Question

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

Answer 1

Check the config of your pool implementation. Usually, it's Apache DBCP which has a timeout for each connection after it will close it.

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.

Answer 2

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.

Answer 3

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.