commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ryunix <budianto.chu...@gmail.com>
Subject Re: Using DBCP with Hibernate - How to reconnect if the poolable connection is closed
Date Sun, 19 Apr 2009 03:57:07 GMT

Hi Phil,
"testOnBorrow tests the connection before only when it is borrowed from 
the pool.  I am not a Hibernate expert, but to understand exactly what 
is going on here, you need to determine when connections are being 
borrowed from the pool vs being reused by your application or Hibernate 
without being returned to the pool.  If a connection is reused without 
being returned and re-borrowed from the pool, dbcp cannot validate it."

That what we are currently suspecting of, we suspect the error occur in the
sequence of action as below :
1. Application open a new connection but failed to close it.
2. That particular connection is recognized by dbcp as 'active connection',
and hence cannot be cleared by idle object evictor.
3. as time goes by, the connection hit the oracle time out.
4. at the next webservice call the application used that particular
connection (which already closed), and hence the error occur.

do you think the above suspicion valid?, I was having the impression when
the item '3' took place, the dbcp could validate the connection before
return it to my application, but as you highlighted, 'if the connection is
never returned to the pool, it will not be validated'. but it is possible
for the dbcp to give that connection to my application (since it is still
accounted under 'activeConnection', though it is not in used)?



Phil Steitz wrote:
> 
> Ryunix wrote:
>> Hi Carman and Phil,
>> thanks for the effort put for this issue.
>> please excuse my lengthy explanation on the problem which lead me my
>> question.
>>
>> Our application provides webservices to be used by various application in
>> our client's environment and generally works fine. However, if there is a
>> firewall between our application and the database, some problem occur. at
>> certain webservice call, it will throw the error message 'no more data to
>> read from socket', and we found out that this is because of the firewall
>> will interrupt inactive connection between the app server and the DB
>> after
>> 120 minute.
>>
>> To resolve this, we suggested to update the idle time setting in the
>> oracle
>> to 90, with the hope that the oracle will terminate the connection before
>> the firewall, and let the 'testOnBorrow' of dbcp to validate the
>> connection
>> before it is used. However, this does not resolve our problem.
>> :(:confused:. 
>>   
> testOnBorrow tests the connection before only when it is borrowed from 
> the pool.  I am not a Hibernate expert, but to understand exactly what 
> is going on here, you need to determine when connections are being 
> borrowed from the pool vs being reused by your application or Hibernate 
> without being returned to the pool.  If a connection is reused without 
> being returned and re-borrowed from the pool, dbcp cannot validate it.
>> Our next action was to enable the 'removeAbandoned' of the dbcp,
>> suspecting
>> that the unclosed unconnection was the one contributing to the whole
>> issue.
>> Even though enabling this feature does not resolve our problem, it does
>> gives us some clue on the issue. My removeAbandoned does not take effect
>> because it did not meet the condition where numActive > maxActive -3,
>> hence
>> I tested in my local with a low 'maxActive' setting and found out that
>> there
>> is a function which indeed create unclosed connection on certain
>> occasion.
>> This had finally led me to the finding that when the poolable connection
>> is
>> closed, i am not able to obtain a new poolable connection within the same
>> webservice invocation.
>>
>> It is important to me to know how to obtain the new connection as we
>> would
>> like the webservice to be able to obtain a new connection if the current
>> connection is not active.
>>   
> As long as each time your web service needs a connection it is using 
> BasicDataSource's getConnection(), testOnBorrow=true should ensure that 
> the returned connection is valid.  It should not make a difference, but 
> you could also set testOnReturn to true to see if this improves the 
> situation.  That property triggers validation before connections are 
> returned to the pool.
> 
> Here is how things are supposed to work from the dbcp perspective (as of 
> 1.2.2):
> 
> 0) application calls getConnection.  If there is an idle connection in 
> the pool, the connection is returned.  If testOnBorrow is true, the 
> connection is validated first.  If validation fails, the connection is 
> destroyed and getConnection (actually pool's borrowObject) tries again.  
> If all of the idle connections are "bad" (as could be happening in your 
> case), they will all be examined and destroyed in this process and a new 
> one will be created, validated and returned.
> 
> 1) application calls close on the PoolableConnection that it got from 
> getConnection.  If testOnReturn is set to true, the connection is 
> validated before being returned to the pool.  If validation fails, it is 
> destroyed.
> 
> If removeAbandoned is set to true,  before getConnection tries to 
> retrieve a connection from the pool in 0), it checks
>  (getNumIdle() < 2) && (getNumActive() > getMaxActive() - 3) ) .  If 
> this is true, abandoned connections are destroyed.  Whether or not a 
> connection is considered "abandoned" is determined by whether or not it 
> has been used (checked out, checked in, prepared a statement, etc.) more 
> recently than the removeAbandonedTimeout.
> 
> What version of dbcp and pool are you running?
> 
> Phil
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/Using-DBCP-with-Hibernate---How-to-reconnect-if-the-poolable-connection-is-closed-tp23102913p23119192.html
Sent from the Commons - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org


Mime
View raw message