Re: [Proxool-developer] Proxool hang when there's a connection problem to database

[Bill H. <bi...@lo...>]

Hi Ivan,

On 08/03/2007 04:56 AM, Ivan Yiu wrote:

>  I've start a simple program with a single thread getting connection
>  from the pool, execute a query and close the connection within an
>  infinite loop. Then I try to disable the connection from the server
>  host to the database, the application hang until all connection are
>  consumed and the Oracle driver throw the "The Network Adaptor could
>  not establish the connection ... " exception. It takes more than 5
>  minutes
>
>  Seems that the housekeep will not kill the connection and pass the
>  control back to the application until the network connection timeout
>  occur.I wonder if there's any way for me to solve this problem, why
>  the housekeeper not working? It's expected the housekeep will kill
>  the connection since maximum-active-time is 20 seconds only. Same
>  problem occur when i disconnect the DB before getting the connection
>  from pool using the getConnection() method or before executing the
>  query.

The house keeper seems to be working as designed, although I can see why 
this isn't what you want. Every minute (as you have configured it) the 
house keeper will look at each connection and see whether it is still 
working. I'm guessing that each connection eventually times out and 
throws an exception, at which point it will be discarded. That could 
take a while. Actually, it doesn't run every minute - more correctly it 
sleeps for one minute between runs. So if each run takes a long time 
then it won't run very often.

The maximum-active-time only relates to how long a connection can be 
borrowed from the pool for. It doesn't relate to how long the house 
keeper will wait for it to respond to the test SQL. True, if a 
connection is being used and the housekeeper notices that it has been 
used for over 20 seconds it will discard it immediately, but that can 
take a while to happen (maybe the house keeper isn't running or it's 
waiting for a timeout whilst testing a different connection).

One improvement we could make to the house keeper is to be less patient 
when testing a connection. If it doesn't run the test SQL within a 
configured time (default to 10 seconds?) then the connection is dumped.

Even when the house keeper has dumped all the connections then every 
time you ask for a new one then it will attempt to make one. If that 
also causes a time out (rather than an immediate exception) then that 
will continue to happen. Proxool doesn't really know the database has 
been disconnected and it doesn't know whether exceptions are immediate 
or because of a timeout.

If you can write a bit of code that will test a connection but get 
impatient if it takes too long then I can see about incorporating that 
into the house keeper. FYI, the current code looks like this 
(HouseKeeper.java:76-83):

                           boolean testResult = false;
                           try {
                               testResult = testStatement.execute(sql);
                           } finally {
                               if (log.isDebugEnabled() && 
definition.isVerbose()) {
                                   
log.debug(connectionPool.displayStatistics() + " - Testing connection " 
+ proxyConnection.getId() + (testResult ? ": True" : ": False"));
                               }
                           }

The only way to be impatient about executing a statement is to kick it 
off in a separate thread and the reason we haven't done this so far is 
partly because we try not to create too many threads. But I suppose if 
it only did it if a property called "house-keeper-patience-time" was set 
then it would be OK.

Cheers,
Bill