Close/Remove Leaked Connections

[Jonathan Pierce]

First off, from what I've seen so far HikariCP is an excellent library.  Thanks for all the great work.

I would assume you considered it, but is there a possibility to add a configuration option to remove a connection from the pool once you determine it has been leaked (some configurable time period)?  Similar to the removeAbandoned/removeAbandonedTimeout options in tomcat-jdbc.

Obviously, you always want to detect connection leaks in the test/pre-prod environment, but if a leak does make it into production I think it would be helpful to close leaked connections and remove them from the pool to prevent the pool from being drained of connections due to the leak.

Thoughts?

Jonathan

[Brett Wooldridge]

Glad you find the library useful.  Yes, we have considered it (removing abandoned connections), but ultimately we decided to pass.  The problem with closing leaked connections is several fold.  Some thread is possibly using that connection, and its going to blow-up (in production) somewhere if we close it.  Or nothing is using that connection, and closing it has no negative impact, but now we've just covered up a leak that will cause constant cycling of connections in the pool.

Applications are responsible for cleaning up resources.  Java developers tend to get lazy compared to C/C++ programmers.  This is leak just like a memory leak, and both can and rightfully should eventually kill your application.  How else would you 1) know a problem exist, and 2) be motivated to track it down and fix it.

We do appreciate all input, even if not adopted.  In this case, users looking for a library to defensively cover-up coding errors should probably look to tomcat-jdbc.

Note, leak detection can be run in production, and can be enabled at runtime through a JMX console, so there's not a lot of justification for adding proactive connection reclamation.

[David Harrigan]

Hi,

There is one other case. I'm seeing this a lot with Spring. I use the LazyconnectionDataSource with Spring so that until something happens, the connection is not established. When I boot up the application, after a short period of time I get this:

[2014-06-09 11:50:08.640][               ][DEVELOPMENT][WARN ][com.zaxxer.hikari.proxy.LeakTask        ][45  ] - Connection leak detection triggered, stack trace follows

java.lang.Exception: null

at org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy$LazyConnectionInvocationHandler.getTargetConnection(LazyConnectionDataSourceProxy.java:403) ~[spring-jdbc-4.0.5.RELEASE.jar:4.0.5.RELEASE]

at org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy$LazyConnectionInvocationHandler.invoke(LazyConnectionDataSourceProxy.java:376) ~[spring-jdbc-4.0.5.RELEASE.jar:4.0.5.RELEASE]

at com.sun.proxy.$Proxy23.getMetaData(Unknown Source) ~[na:na]

at org.jadira.usertype.spi.shared.AbstractUserTypeHibernateIntegrator.use42Api(AbstractUserTypeHibernateIntegrator.java:80) ~[usertype.spi-3.2.0.GA.jar:na]

at org.jadira.usertype.spi.shared.AbstractUserTypeHibernateIntegrator.integrate(AbstractUserTypeHibernateIntegrator.java:61) ~[usertype.spi-3.2.0.GA.jar:na]

at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:312) ~[hibernate

Other than that, the application appears to work okay.

btw...no 1.3.19 yet :-) ?

-=david=-

[Brett Wooldridge]

I'm not sure what the benefit of LazyconnectionDataSource is.  It appears to be taking a connection and hanging onto it, not a particularly good idea for connection pools.  For example, what happens if the Connection expires or is disconnected while LazyconnectionDataSource is holding it?  If you don't want any Connections initiated to the database until the first use, just set the minimumIdle value to 0 in HikariCP.  Though I question the benefit of this whole enterprise.  Presumably your application will access the database eventually, so what is the benefit of delaying the Connection creation?

I just published 1.3.9 to the Maven Central repository, it will take a few hours to appear.  Once it does, I will post a formal announcement here.

[Brett Wooldridge]

I just read the documentation on LazyConnectionDataSource, and it states:

Connection initialization properties like auto-commit mode, transaction isolation and read-only mode will be kept and applied to the actual JDBC Connection as soon as an actual Connection is fetched.

I can guarantee this is going to be much more inefficient than just accessing HikariCP.  Developers are often too clever for their own good, creating artifice like this in the name of efficiency without actually understanding what goes on under the covers between JDBC and the database.  Whenever a call to setTransactionIsolation() is made, a roundtrip to the database is required.  Same for setAutocommit() and setReadOnly().  Some JDBC drivers cache the current values for a connection and only roundtrip if the new value is different that the old value, but many do not.

In the absence of HikariCP this would be a terrible idea, IMHO.  Luciky, because HikariCP proxies the Connection object it is able to implement this caching behavior for all drivers and avoid unnecessary roundtrips to the database.  But wrapping LazyConnectionDataSource around HikariDataSource is adding no benefit that I can see, and actually adding overhead (even though eventually mitigated by HikariCP).

[David Harrigan]

Hi Brett,

Thank you very much for your very clear reply! One learns something everyday, and it is very interesting what you have put forth. I'm pleased I raised the issue, so that I can learn and improve the way I connect to the database.

You rock! :-)

Thanks for 1.3.9 as well :-)

-=david=-