You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by James Carman <ja...@carmanconsulting.com> on 2009/04/17 20:13:22 UTC
Re: Using DBCP with Hibernate - How to reconnect if the poolable
connection is closed
You can turn on the connection validation [1] by supplying a
validation query. Then, DBCP can test if the connection is okay when
it tries to let you borrow it.
[1] http://commons.apache.org/dbcp/configuration.html
On Fri, Apr 17, 2009 at 2:07 PM, Ryunix <bu...@gmail.com> wrote:
>
> Dear All,
> we have an application which using hibernate with DBCP.
> and the application will access the database using hibernate session which
> obtain the connection from the dbcp pool. The application will provide
> webservices to be used by other application.
>
> In some cases where the 'poolable connection' obtained by the hibernate
> session is closed, our application will throw error saying the connection is
> closed to the webservice client, and this error will only occur at that
> particular web service call. At the next call, the webservice can return
> result to the caller successfully.
>
> To prevent the error, we had added an intermediate 'loop' function to test
> the connection obtained by the session, if the connection (poolable
> connection) is closed, we will issue conn.close() and release the session .
> However, it seems that the connection obtained through
> session.getConnection() at every loop is always the same, and hence would
> have the same 'connection is closed' issue.
>
> May I know if there is any way for me to force the session.getConnection to
> return me a new 'valid' connection?
>
> Thanks guys in advance
>
> --
> View this message in context: http://www.nabble.com/Using-DBCP-with-Hibernate---How-to-reconnect-if-the-poolable-connection-is-closed-tp23102913p23102913.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
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: Using DBCP with Hibernate - How to reconnect if the poolable
connection is closed
Posted by James Carman <ja...@carmanconsulting.com>.
So, make sure you're closing your connections in your code. Make sure
you clean up your hibernate sessions. Are you using spring?
On Sat, Apr 18, 2009 at 11:57 PM, Ryunix <bu...@gmail.com> 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.
>
> 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
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: Using DBCP with Hibernate - How to reconnect if the poolable
connection is closed
Posted by Phil Steitz <ph...@gmail.com>.
Ryunix wrote:
> "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."
>
> I believe yes, the connection is obtained using the statement
> session.getConnection() -> session = hibernate session.
> But I am not sure if the connection returned is the connection which is in
> the list of 'dbcp.getActiveConnection()'.
>
> "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."
> how would dbcp recognize the client? if the client is an application which
> uses the webservice, would dbcp recognized all the webservice call as the
> same client? (and thus return the active connection to the client).
>
Sorry I was not clear. What I meant is that dbcp will not return a
connection via getConnection that is already checked out of the pool.
Phil
>
>
>
>
> Phil Steitz wrote:
>
>> 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
>>
>>
>>
>>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: Using DBCP with Hibernate - How to reconnect if the poolable
connection is closed
Posted by Ryunix <bu...@gmail.com>.
"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."
I believe yes, the connection is obtained using the statement
session.getConnection() -> session = hibernate session.
But I am not sure if the connection returned is the connection which is in
the list of 'dbcp.getActiveConnection()'.
"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."
how would dbcp recognize the client? if the client is an application which
uses the webservice, would dbcp recognized all the webservice call as the
same client? (and thus return the active connection to the client).
Phil Steitz wrote:
>
> 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
>
>
>
--
View this message in context: http://www.nabble.com/Using-DBCP-with-Hibernate---How-to-reconnect-if-the-poolable-connection-is-closed-tp23102913p23119730.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
Re: Using DBCP with Hibernate - How to reconnect if the poolable
connection is closed
Posted by Phil Steitz <ph...@gmail.com>.
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
Re: Using DBCP with Hibernate - How to reconnect if the poolable
connection is closed
Posted by Ryunix <bu...@gmail.com>.
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
Re: Using DBCP with Hibernate - How to reconnect if the poolable
connection is closed
Posted by Phil Steitz <ph...@gmail.com>.
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
Re: Using DBCP with Hibernate - How to reconnect if the poolable
connection is closed
Posted by Ryunix <bu...@gmail.com>.
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:.
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.
Thanks million for your help guys:working:
--
View this message in context: http://www.nabble.com/Using-DBCP-with-Hibernate---How-to-reconnect-if-the-poolable-connection-is-closed-tp23102913p23114238.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
Re: Using DBCP with Hibernate - How to reconnect if the poolable
connection is closed
Posted by Phil Steitz <ph...@gmail.com>.
Ryunix wrote:
> Hi Carman,
> I had already set the validation query and enable the removeAbandoned to
> true
Do you have testOnBorrow set to true? That is the property that
determines whether or not connections are validated on checkout.
>
>
> It seems that on some occasion when the removeAbandoned closes the
> connection, it will close the underlying poolable connection, and when my
> application uses the connection, i can see that the connection object
> returned by session.getConnection() would have the value isClosed=false, but
> when I expand the connection object to the poolable connection, i can see
> that isClosed=true.
>
If your application/Hibernate is properly closing connections, you do
not need to set removeAbandoned = true. If you do use this, you need to
make sure that the removeAbandonedTimeout is longer than the maximum
amount of time that your application will hold onto a connection that
has been checked out from the pool without using it.
Phil
>
>
>
> jwcarman wrote:
>
>> You can turn on the connection validation [1] by supplying a
>> validation query. Then, DBCP can test if the connection is okay when
>> it tries to let you borrow it.
>>
>> [1] http://commons.apache.org/dbcp/configuration.html
>>
>>
>> On Fri, Apr 17, 2009 at 2:07 PM, Ryunix <bu...@gmail.com> wrote:
>>
>>> Dear All,
>>> we have an application which using hibernate with DBCP.
>>> and the application will access the database using hibernate session
>>> which
>>> obtain the connection from the dbcp pool. The application will provide
>>> webservices to be used by other application.
>>>
>>> In some cases where the 'poolable connection' obtained by the hibernate
>>> session is closed, our application will throw error saying the connection
>>> is
>>> closed to the webservice client, and this error will only occur at that
>>> particular web service call. At the next call, the webservice can return
>>> result to the caller successfully.
>>>
>>> To prevent the error, we had added an intermediate 'loop' function to
>>> test
>>> the connection obtained by the session, if the connection (poolable
>>> connection) is closed, we will issue conn.close() and release the session
>>> .
>>> However, it seems that the connection obtained through
>>> session.getConnection() at every loop is always the same, and hence would
>>> have the same 'connection is closed' issue.
>>>
>>> May I know if there is any way for me to force the session.getConnection
>>> to
>>> return me a new 'valid' connection?
>>>
>>> Thanks guys in advance
>>>
>>> --
>>> View this message in context:
>>> http://www.nabble.com/Using-DBCP-with-Hibernate---How-to-reconnect-if-the-poolable-connection-is-closed-tp23102913p23102913.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
>>>
>>>
>>>
>> ---------------------------------------------------------------------
>> 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
Re: Using DBCP with Hibernate - How to reconnect if the poolable
connection is closed
Posted by James Carman <ja...@carmanconsulting.com>.
Can we see how you're configuring things?
On Fri, Apr 17, 2009 at 3:33 PM, Ryunix <bu...@gmail.com> wrote:
>
> Hi Carman,
> I had already set the validation query and enable the removeAbandoned to
> true.
> It seems that on some occasion when the removeAbandoned closes the
> connection, it will close the underlying poolable connection, and when my
> application uses the connection, i can see that the connection object
> returned by session.getConnection() would have the value isClosed=false, but
> when I expand the connection object to the poolable connection, i can see
> that isClosed=true.
>
>
>
>
> jwcarman wrote:
>>
>> You can turn on the connection validation [1] by supplying a
>> validation query. Then, DBCP can test if the connection is okay when
>> it tries to let you borrow it.
>>
>> [1] http://commons.apache.org/dbcp/configuration.html
>>
>>
>> On Fri, Apr 17, 2009 at 2:07 PM, Ryunix <bu...@gmail.com> wrote:
>>>
>>> Dear All,
>>> we have an application which using hibernate with DBCP.
>>> and the application will access the database using hibernate session
>>> which
>>> obtain the connection from the dbcp pool. The application will provide
>>> webservices to be used by other application.
>>>
>>> In some cases where the 'poolable connection' obtained by the hibernate
>>> session is closed, our application will throw error saying the connection
>>> is
>>> closed to the webservice client, and this error will only occur at that
>>> particular web service call. At the next call, the webservice can return
>>> result to the caller successfully.
>>>
>>> To prevent the error, we had added an intermediate 'loop' function to
>>> test
>>> the connection obtained by the session, if the connection (poolable
>>> connection) is closed, we will issue conn.close() and release the session
>>> .
>>> However, it seems that the connection obtained through
>>> session.getConnection() at every loop is always the same, and hence would
>>> have the same 'connection is closed' issue.
>>>
>>> May I know if there is any way for me to force the session.getConnection
>>> to
>>> return me a new 'valid' connection?
>>>
>>> Thanks guys in advance
>>>
>>> --
>>> View this message in context:
>>> http://www.nabble.com/Using-DBCP-with-Hibernate---How-to-reconnect-if-the-poolable-connection-is-closed-tp23102913p23102913.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
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> 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-tp23102913p23104475.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
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: Using DBCP with Hibernate - How to reconnect if the poolable
connection is closed
Posted by Ryunix <bu...@gmail.com>.
Hi Carman,
I had already set the validation query and enable the removeAbandoned to
true.
It seems that on some occasion when the removeAbandoned closes the
connection, it will close the underlying poolable connection, and when my
application uses the connection, i can see that the connection object
returned by session.getConnection() would have the value isClosed=false, but
when I expand the connection object to the poolable connection, i can see
that isClosed=true.
jwcarman wrote:
>
> You can turn on the connection validation [1] by supplying a
> validation query. Then, DBCP can test if the connection is okay when
> it tries to let you borrow it.
>
> [1] http://commons.apache.org/dbcp/configuration.html
>
>
> On Fri, Apr 17, 2009 at 2:07 PM, Ryunix <bu...@gmail.com> wrote:
>>
>> Dear All,
>> we have an application which using hibernate with DBCP.
>> and the application will access the database using hibernate session
>> which
>> obtain the connection from the dbcp pool. The application will provide
>> webservices to be used by other application.
>>
>> In some cases where the 'poolable connection' obtained by the hibernate
>> session is closed, our application will throw error saying the connection
>> is
>> closed to the webservice client, and this error will only occur at that
>> particular web service call. At the next call, the webservice can return
>> result to the caller successfully.
>>
>> To prevent the error, we had added an intermediate 'loop' function to
>> test
>> the connection obtained by the session, if the connection (poolable
>> connection) is closed, we will issue conn.close() and release the session
>> .
>> However, it seems that the connection obtained through
>> session.getConnection() at every loop is always the same, and hence would
>> have the same 'connection is closed' issue.
>>
>> May I know if there is any way for me to force the session.getConnection
>> to
>> return me a new 'valid' connection?
>>
>> Thanks guys in advance
>>
>> --
>> View this message in context:
>> http://www.nabble.com/Using-DBCP-with-Hibernate---How-to-reconnect-if-the-poolable-connection-is-closed-tp23102913p23102913.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
>>
>>
>
> ---------------------------------------------------------------------
> 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-tp23102913p23104475.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