How to get Squeryl to release closed connections back to C3P0?

Question

Sometimes I see the following error repeatedly in my logs:

com.mchange.v2.c3p0.impl.NewPooledConnection - [c3p0] A PooledConnection that has already signalled a Connection error is still in use!
com.mchange.v2.c3p0.impl.NewPooledConnection - [c3p0] Another error has occurred [ org.postgresql.util.PSQLException: This connection has been closed. ] which will not be reported to listeners!
org.postgresql.util.PSQLException: This connection has been closed.
        at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:822)
        at org.postgresql.jdbc2.AbstractJdbc2Connection.rollback(AbstractJdbc2Connection.java:839)
        at com.mchange.v2.c3p0.impl.NewProxyConnection.rollback(NewProxyConnection.java:855)
        at org.squeryl.dsl.QueryDsl$class._executeTransactionWithin(QueryDsl.scala:131)
        at org.squeryl.dsl.QueryDsl$class.transaction(QueryDsl.scala:78)
        at org.squeryl.PrimitiveTypeMode$.transaction(PrimitiveTypeMode.scala:40)

Below that is the trace of my own code up to the point of my transaction {} block.

My software repeats the transaction {} after it throws an exception, but it appears to use the same (closed) connection over again, so the next attempt also fails. Strangely this takes quite some time, sometimes 50 seconds, sometimes up to 2 minutes. One would think a closed connection would fail immediately.

How do I get Squeryl to release this connection to the pool and acquire a new one?

Answer 1

I found out why this was happening. I needed to use JDBC features not supported by Squeryl. So I got the connection from Squeryl and used it directly. But I had a bug where a prepared statement was not being closed. This resulted in the dead connection being reused over and over. I'm not sure how or why this happened. But as soon as I put the closing of the statement in a finally block everything started working. Now when Squeryl gets to the transaction block a second time it receives a fresh connection from c3p0.

For anyone else seeing the same errors, I also found out that you can get the c3p0 errors (top two errors in the question text above) even when nothing is wrong. If your thread holding the database connection is busy (a Thread.sleep() in my case, for testing) and c3p0 notices before you do that the connection is dead, then you can get the error about a dead connection still being in use. In that case it is a perfectly normal situation and just a question of which thread sees the problem first - nothing to worry about.