Hello,

I am having a problem with database connections from a C3P0 connection pool (being used with Hibernate) not ever being closed. I have determined the cause of the issue but have not determined the best way to solve or workaround it. I would welcome any pointers or suggestions.

First, some basic information on my environment. I have a standalone J2SE application (i.e. not running in a J2EE container) which connects to a MySQL database. I am therefore using normal JDBCTransactions when it comes to Hibernate. The C3P0 configuration portion of my application is as follows:

<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="c3p0.acquire_increment">1</property>
<!-- connections in pool will be tested every 5 minutes -->
<property name="c3p0.idle_test_period">300</property> <property name="c3p0.max_size">10</property>
<property name="c3p0.max_statements">0</property>
<property name="c3p0.min_size">0</property>
<!-- connections in pool will be disconnected from database after 500 seconds of not being checked out -->
<property name="c3p0.timeout">500</property>

As you can see, I have a minimum pool size of zero. This is because my application goes through long periods where it is parsing through files and has no need for the database. Therefore, there is no need to keep open connections.

We also use lazy loading with Hibernate because our database objects have a larger hierarchy of child/grandchild tables and we don't necessarily need all of them.

When the application is running, I will make a call to a DAO to load a header level object from the database. Because lazy loading is employed, just the header level data is loaded (which is what I want). The DAO method is wrapped in a Hibernate transaction. Upon the beginning of a transaction, the database connection is obtained from C3P0's connection pool, used to load the header data. When the Hibernate transaction is committed the database connection is returned to the C3P0 pool where it can potentially disconnect it later if it is not reused within the allocated time. Good so far.

The problem is this. After getting the object back from the DAO, I might have to load the data from one or more child tables by accessing the Set object which is a member variable of my header object. If the Set has not already been populated (if it is the first time it is accessed it will not be, thanks to lazy loading), Hibernate will query the database to get the data. It grabs a connection from the C3P0 connection pool to do this. However, because the code that accessed the Set member variable is NOT within a Hibernate transaction, there is no commit call. What this means is that the database connection is never returned to the C3P0 connection pool. If this happens enough times, you can lose all your connections in the pool and the application could hang.

There are several possible workarounds to this, but none of the ones I have come up with so far are particularly appealing:

1) Disable lazy loading. This would work, but could impact performance as the entire object graph would be loaded unneccessarily since just a handful of tables might be used.

2) Make sure that all data the is required is "pre-loaded" within the DAO (or at least somewhere within a Hibernate transaction). The is actually the workaround I am using right now. The problem is that it requires the DAO to know the details of what its caller is requiring, which is not usually practical for a data access library function.

3) Whenever tables are accessed that might not be loaded, ensure this code is wrapped in a Hibernate transaction. This is not all that desirable because it forces the overhead of a transaction when it might not be required (if the data has already been loaded). More importantly, it requires a certain level of Hibernate/database awareness outside of a DAO library, which is not desirable.


I am curious if anyone has any other suggestions. It is certainly possible that my Hibernate configuration is not set up correctly for how I am using this.

Thanks in advance!

so you never close your sessions ?...sounds more like a session handling issue than connection pooling issue

Thanks for replying, scarface. Unfortunately, if you close your Session and then try to access part of the object graph (i.e. data from a child table) that has not been lazily loaded, you will get an exception thrown your way from Hibernate (since you no longer have a session). Lazy-loading assumes that you have a session open.

Like many other people, I use the "session-per-thread" session context, so each thread needing to do database work will have its own Session. Given this, you should not really need to close your session unless some sort of error occurs. This way, the same thread can access the session later without need to open a new one.

I have been giving this some more thought, and I think I have come up with a better solution/workaround. I believe the answer is that (assuming that lazy-loading is utilized) you have to wrap any call that could potentially access the database (i.e. lazy load calls) in a Hibernate transaction. Therefore, I built an interface called TransactionDao that serves as a superclass for all my interface DAOs. This interface has the API for beginning, committing, and rolling back a transction. Then I created an class called HibernateDAO that implements TransactionDao and implements the details for Hibernate transactions. It also serves as the superclass for all my DAO classes that pull data from database tables via Hibernate. For other DAO implementations (such as those that read/write to files), these will extend a NoOpTransactionDAO class that will have an empty implementation for using a data source without transactions.

This way, my business logic that needs to wrap code several DAO calls within a single transaction can make somewhat "generic" calls to the TransactionDAO methods. It's not super elegant but better than having the business logic be database and Hibernate aware.

Given my admittedly limited knowledge of Hibernate, I think the solution is a change to their interceptors so that when a lazy load needs to call the database, a check is made to see if the session is currently within a transaction. If not, a transaction is started and then committed once the lazy load returns.

Ahh.. now i see what you are saying ...

I would say you might want to set the
hibernate.connection.release_mode
parameter to after statement

that way after loading the Lazy collection through the PS it would release the connection ..

you might also want to check out

http://www.hibernate.org/hib_docs/v3/reference/en/html/transactions.html#transactions-connection-release


Can i get a credit now pls :)

Thanks again, scarface. Certainly a find deserving of a credit, but it has some drawbacks that may not make it feasible. Namely, the connection release mode applies to the Hibernate environment as a whole. So if your application involves multiple update/delete statements that need to be treated as a transaction (as part of my application does), you could possiblity get a different database connection from the pool for each statement you run, which could lead to problems.

Thanks again, scarface. Certainly a find deserving of a credit, but it has some drawbacks that may not make it feasible. Namely, the connection release mode applies to the Hibernate environment as a whole. So if your application involves multiple update/delete statements that need to be treated as a transaction (as part of my application does), you could possiblity get a different database connection from the pool for each statement you run, which could lead to problems.

how about manually supplying a connection with auto commit turned on

I do not understand why you need more than one session/connection per thread. IMO, you're exactly using the OpenSessionInView pattern.

Obviously, for each object provided by any method of yours, if you want to enable lazy-loading, it will keep one connection. So, if you've got 1000 library calls, you'll need having 1000 connections open. Do I understand well ?

http://blog.hibernate.org/cgi-bin/blosxom.cgi/Christian%20Bauer/java/hibernateswing.html

thats an example of why one would need a more than one session per thread
. Not everybody is on web :)