Thread: jdbc pooling question

jdbc pooling question

From
"Marcus Andree S. Magalhaes"
Date:
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.




Re: jdbc pooling question

From
"Neil Murray"
Date:
> 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.



Re: jdbc pooling question

From
"Marcus Andree S. Magalhaes"
Date:
>
> 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




Re: jdbc pooling question

From
Kris Jurka
Date:

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


Re: jdbc pooling question

From
Paul Thomas
Date:
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   |
+------------------------------+---------------------------------------------+

Re: jdbc pooling question

From
Kris Jurka
Date:

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



Re: jdbc pooling question

From
"John Sidney-Woollett"
Date:
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
>


Re: jdbc pooling question

From
"Neil Murray"
Date:
> 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