Обсуждение: Connection Pool Timeout

Hi,

I connect to the DB with pg73jdbc3.jar - and I make use of
Jdbc3PoolingDataSource().

Using the connection pool is no problem, but there seems to be a black hole
of access at the point where the getConnection() method is called. If
getConnection is called, and no DB connections are available, then
getConnection() waits forever without throwing an exception. The API seems
to indicate that there is a timeout that can be set, but setting it has made
no difference. getConnection() still waits forever.

Any ideas?

Neil

Neil Murray
nhm@mimecast.net

On Tue, 23 Dec 2003, Neil Murray wrote:

> Hi,
>
> I connect to the DB with pg73jdbc3.jar - and I make use of
> Jdbc3PoolingDataSource().
>
> Using the connection pool is no problem, but there seems to be a black hole
> of access at the point where the getConnection() method is called. If
> getConnection is called, and no DB connections are available, then
> getConnection() waits forever without throwing an exception. The API seems
> to indicate that there is a timeout that can be set, but setting it has made
> no difference. getConnection() still waits forever.

The setLoginTimeout method is just a stub and the comment for it indicates
that it is not implemented.  There are two issues with its implementation.
Regarding your problem that the pool is empty and it loops forever waiting
for a connection to be made available, it would be a pretty simple fix to
count the number of loops made and bail out once you've passed the
timeout.

The other problem is actually enforcing this connection timeout when
creating a real connection.  As far as I can see there is no means before
the 1.4 jvm to put a timeout value on creating a Socket connection.
Then for the whole connection and authentication process you would need to
call setSoTimeout on the Socket.  This would be incredibly ugly because to
enfore a setLoginTimeout of 4 seconds you couldn't simply use a 4 second
timeout on the socket's read and write calls because each one of many
could take 3.9 seconds.  You would have to continually monitor the time
elapsed and keep calling setSoTimeout with the remaing time left.

Kris Jurka

> The setLoginTimeout method is just a stub and the comment for it
> indicates that it is not implemented.  There are two issues with its
> implementation. Regarding your problem that the pool is empty and it
> loops forever waiting for a connection to be made available, it would be
> a pretty simple fix to count the number of loops made and bail out once
> you've passed the timeout.
>

Yup. That would be simple to do.

> The other problem is actually enforcing this connection timeout when
> creating a real connection.  As far as I can see there is no means
> before the 1.4 jvm to put a timeout value on creating a Socket
> connection. Then for the whole connection and authentication process you
> would need to call setSoTimeout on the Socket.  This would be incredibly
> ugly because to enfore a setLoginTimeout of 4 seconds you couldn't
> simply use a 4 second timeout on the socket's read and write calls
> because each one of many could take 3.9 seconds.  You would have to
> continually monitor the time elapsed and keep calling setSoTimeout with
> the remaing time left.
>
> Kris Jurka
>

Kris, checking Sun's javadoc pages, it seemes that setSoTimeout and
setSoLinger is abailable since jdk 1.1. My guess is, if anyone wants
to provide a solution to this "problem", the best answer would be to
use both the loop limit and socket timeouts.

I was thinking in something like the following:

 - a higher value to setSoTimeout when initializing/connecting the
   sockets

 - after the sockets are connected and available to the pooling
   structure, set a higher/infinite value to SO_TIMEOUT and,
   probably, suitable value to SO_LINGER

 - put a max value by counting the number of wait() calls inside the
   loop and maybe raise an exception.

This way, the client has the control to either log an error or
try to get a new connection over and over again.

Don't know if this can be easily done. Can't say for sure if
the org.postgresql.Driver provides the ways to control the sockets
there are used to connect to the backend.

> ---------------------------(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:
> > The other problem is actually enforcing this connection timeout when
> > creating a real connection.  As far as I can see there is no means
> > before the 1.4 jvm to put a timeout value on creating a Socket
> > connection. Then for the whole connection and authentication process you
> > would need to call setSoTimeout on the Socket.  This would be incredibly
> > ugly because to enfore a setLoginTimeout of 4 seconds you couldn't
> > simply use a 4 second timeout on the socket's read and write calls
> > because each one of many could take 3.9 seconds.  You would have to
> > continually monitor the time elapsed and keep calling setSoTimeout with
> > the remaing time left.
> >
> > Kris Jurka
> >
>
> Kris, checking Sun's javadoc pages, it seemes that setSoTimeout and
> setSoLinger is abailable since jdk 1.1. My guess is, if anyone wants
> to provide a solution to this "problem", the best answer would be to
> use both the loop limit and socket timeouts.

setSoTimeout is only for read() calls on the InputStream, it won't help us
during establishing a connection.

setSoLinger is only relevent for closing the connection which isn't
related to setLoginTimeout.

The idea of continually setting setSoTimeout with the remaining time left
is something I mentioned as the only way I saw to do it, not something I
would want to consider actually doing.

Kris Jurka