I have a problem using c3p0. In the following code snippet (simplified from what I am really doing) I create a statement that will not be closed when a transaction is committed (the parameter ResultSet.HOLD_CURSORS_OVER_COMMIT). In the code, I iterate over the result set and periodically commit the transaction and start a new one. I do this because of a memory leak in the database driver. In reality the memory leak only becomes a problem around 25,000 records, which is when I commit in the production code.

This code works fine as long as I do not use c3p0. But when I do use c3p0, executing the results.next() statement after the first commitTransaction() inside the while loop, I receive a SqlException stating that the result set has been closed.



hibUtil in the code is an implenmentation of the HibernateUtility class pretty much like the one in the CaveatEmptor example application. It uses a threadLocal variable to hold the hibernate Session. Using this paradigm only one transaction per thread is supported.

My questions are:

- Does anyone know of a way to get c3p0 to correctly recognize the HOLD_CURSORS_OVER_COMMIT parameter?
- Might another connection pool work correctly? I don't want to spend a lot of time on other connection pools if they will all have this same issue.
- Is there another way to construct the code to avoid this problem and still use c3p0?
(My thoughts were to manage transactions explicitly, i.e. not use HibernateUtility, so I could use different transactions for the query and update statements; or to run the update, that is the saveObject() code, in a separate thread so that even using HibernateUtil it will have a different Session.)

I am using hibernate 3.2, DB2 version 8.2, java 1.5. Here are my c3p0 statements in hibernate.cfg.xml:

Hi,

Have you tried a simpler, JDBC-only test of this? I don't see why c3p0 would fail to support ResultSet.HOLD_CURSORS_OVER_COMMIT at the JDBC-level. But I'm not sure that at the Connection level, the code in your example isn't doing more than that. Could Hibernate check-in and then check-out a Connection between commitTransaction() and beginTransaction()? hibernate could always reuse the same Connection between transaction boundaries in the unpooled scenario, but switch-out Connections in the pooled scenario, creating the discrepancy in behavior that you observe.

The only time I can think of when c3p0 closes ResultSets out from under you is when the Connections that created them are close()ed [checked back into the pool]. Either I'm missing something (very possible), or somehow that is happening. [You might test Connection identity with Session.connection(), and see if it ever changes within your loop? I feel like I should know whether or under what conditions multiple Connection instances could be associated with the same hibernate Session, but I don't. Others here I'm sure can tell you that exactly.]

Good luck!

Steve

Thanks for the suggestions!

I ran my code in the debugger and looked at the object ids as you suggested. The session and its connection remain the same over the commit. I did notice that the connection has a BorrowedConnection, which has a ConnectionManager, which has a connection (a NewProxyConnection) and this last variable actually is nulled out by the commit and re-instantiated by the begin transaction. I have no idea what this connection is for and how it could affect the result set.

I also downloaded the c3p0 pre-11 jar and replaced the one that came with hibernate 3.2. This did not solve the problem though.

Finally I downloaded the pre-11 source code and tried to drill down into the resultSet.next() method which is where the failure occurs. The implementation class is NewProxyResultSet. But I discovered that the source code does not include this class ;-(.

Do you have any more suggestions.?

Larry

larry,

so, the NewProxyConnection is c3p0's version of a Connection. if hibernate is closing and reacquiring that on commit, that explains the behavior you're seeing.

i think you could alter this by changing the "connection release mode" to "on_close". (see http://www.hibernate.org/hib_docs/v3/reference/en/html/transactions.html [sec 11.5]) this is "discouraged", but your code implicitly relies on the Connection object remaining open and unchanged throughout the life of your session, so unless you change that, "on_close" may be the right solution.

if you want to see the c3p0's NewProxyXXX classes in a debugger, build the distribution, and then add the build/codegen directory to your debugger's source path. if you just want to review the source without building, type ant codegen at the top level of c3p0's source distribution, and it will appear in build codegen. if you want to review the "meta source" -- the code of the code generator -- for the proxy classes it is com.mchange.v2.c3p0.codegen.JdbcProxyGenerator.

smiles,
steve