Thread: jdbc pooling question
Hi, guys. We're suffering (probably) a connection leak problem while using Jdbc2PoolingDataSource that comes with jdbc drivers for postgres 7.3. Recently, a freeze problem was detected and the debugging showed that we may have some leaks. Before digging deeply into the driver's source code, we decided to analyze our own java sources and found a couple places where the statements and the connections were closed, but not the resultset. The question is, in this configuration (connection & statement closed), there is any possibility of the connection isn't being properly closed by the pool and, therefore, being leaked? thanks in advance.
> We're suffering (probably) a connection leak problem while > using Jdbc2PoolingDataSource that comes with jdbc drivers > for postgres 7.3. I posed a similar question to the forum a couple of weeks back and got no answer. I have similar problems, but my biggest bugbear is the fact that the Jdbc3PoolingDataSource (I use the JDBC3 drivers) does not throw any timeout exception if you use getConnection() and there are no available connections in the pool. The reason why there are no connections left in the pool is a matter I need to investigate further, but I could be experiencing a similar problem to you. My team is working on our own connection pool - which will provide a bit more transparency.
> > I posed a similar question to the forum a couple of weeks back and got > no answer. I have similar problems, but my biggest bugbear is the fact > that the Jdbc3PoolingDataSource (I use the JDBC3 drivers) does not throw > any timeout exception if you use getConnection() and there are no Yes. That's exactly what we found here. If there are no connections available, the getConnection will wait, wait and wait. The caller class has no way to set a timeout... > available connections in the pool. The reason why there are no > connections left in the pool is a matter I need to investigate further, > but I could be experiencing a similar problem to you. > I double checked the code and found a couple places where we did the following: if (statement != null) statement.close(); if (connection != null) connection.close(); but the ResultSet wasn't closed. This left me wandering if an opened resultset could be the leak source. > My team is working on our own connection pool - which will provide a bit > more transparency. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
On Sun, 11 Jan 2004, Marcus Andree S. Magalhaes wrote: > > Hi, guys. > > We're suffering (probably) a connection leak problem while > using Jdbc2PoolingDataSource that comes with jdbc drivers > for postgres 7.3. > > Recently, a freeze problem was detected and the debugging > showed that we may have some leaks. > > Before digging deeply into the driver's source code, we decided > to analyze our own java sources and found a couple places where > the statements and the connections were closed, but not the > resultset. > > The question is, in this configuration (connection & statement closed), > there is any possibility of the connection isn't being properly closed > by the pool and, therefore, being leaked? Closing the connection will return the connection to the pool regardless of the status of other related objects. The fact that closing the statement and the connection doesn't completely close the ResultSet is a bug, but not a terribly serious one. Adding a check for every single method to check some kind of isClosed flag is tedious and wasteful. Kris Jurka
On 11/01/2004 22:14 Kris Jurka wrote: > [snip] > Closing the connection will return the connection to the pool regardless > of the status of other related objects. The fact that closing the > statement and the connection doesn't completely close the ResultSet is a > bug, but not a terribly serious one. Adding a check for every single > method to check some kind of isClosed flag is tedious and wasteful. Plus there are a number of robust open-source connection pool implementations available. I use Apache dbcp (via Tomcat) and it all works very nicely and can also clean up resource leaks left by non-programmers who don't know how to correctly use a finally block :) OT, I can't really see the point of re-inventing the wheel by providing our own connection pooling. Does anyone else have any views on this? -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
On Sun, 11 Jan 2004, Paul Thomas wrote: > > On 11/01/2004 22:14 Kris Jurka wrote: > > [snip] > > Closing the connection will return the connection to the pool regardless > > of the status of other related objects. The fact that closing the > > statement and the connection doesn't completely close the ResultSet is a > > bug, but not a terribly serious one. Adding a check for every single > > method to check some kind of isClosed flag is tedious and wasteful. > > Plus there are a number of robust open-source connection pool > implementations available. I use Apache dbcp (via Tomcat) and it all works > very nicely and can also clean up resource leaks left by non-programmers > who don't know how to correctly use a finally block :) > > OT, I can't really see the point of re-inventing the wheel by providing > our own connection pooling. Does anyone else have any views on this? Well, I wouldn't go out and write a new pooling system, but the one we have at the moment only has what might be described as a very minor bug. Supporting setLoginTimeout in the pool is a very simple addition, but the problem is that what use is supporting it for the pool if the main driver doesn't support it? Given that, it may be more consistent that it doesn't work for the pooling case as well, although silently ignoring it may not be the best idea. Kris Jurka
Have you tried looking at the Apache db connection pool? See http://jakarta.apache.org/commons/dbcp/ It might save you some time and effort. John Sidney-Woollett Neil Murray said: >> We're suffering (probably) a connection leak problem while >> using Jdbc2PoolingDataSource that comes with jdbc drivers >> for postgres 7.3. > > I posed a similar question to the forum a couple of weeks back and got no > answer. I have similar problems, but my biggest bugbear is the fact that > the > Jdbc3PoolingDataSource (I use the JDBC3 drivers) does not throw any > timeout > exception if you use getConnection() and there are no available > connections > in the pool. The reason why there are no connections left in the pool is a > matter I need to investigate further, but I could be experiencing a > similar > problem to you. > > My team is working on our own connection pool - which will provide a bit > more transparency. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >
> Plus there are a number of robust open-source connection pool > implementations available. I use Apache dbcp (via Tomcat) and it all works > very nicely and can also clean up resource leaks left by non-programmers > who don't know how to correctly use a finally block :) > > OT, I can't really see the point of re-inventing the wheel by providing > our own connection pooling. Does anyone else have any views on this? I should have known that Apache would have had one (but I didn't). I forgot the cardinal rule ... when in doubt, visit Apache Jakarta. Many Thanks Neil