commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Phil Steitz <phil.ste...@gmail.com>
Subject Re: Using DBCP with Hibernate - How to reconnect if the poolable connection is closed
Date Sun, 19 Apr 2009 04:02:55 GMT
Ryunix wrote:
> 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.
>   
How does the application get the connection in 4?  If it gets it from 
the pool, it will be validated before the application gets it.
> 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)?
>   
No, dbcp will not hand out multiple handles to the same connection.  If 
a client has the connection checked out, it will not be given to another 
client.

Phil
>
>
> 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
>>
>>
>>
>>     
>
>   


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


Mime
View raw message