[DISCUSS] Reactively responding to a stale connection in a JDBC connection pool

[Kasper Sørensen <i....@gmail.com>]

Hi all,

The other day I ran into this issue for the Nth time in my coding career...
A stale connection was lying around in my JDBC connection pool. It was
there because the DB server was restarted but the application (using
MetaModel) was not.

Since the pool was a commons-dbcp BasicDataSource pool, I am aware of a few
ways to "solve" the issue. One known way is to set a "validation query"
(typically something like "SELECT 1") which will be called by the pool to
check the connections availability BEFORE borrowing the connection. That
means borrowing every connection comes with a significant (remove call)
overhead. I did write a suggestion to the commons-dbcp dev list about
making some improvement that would retry the transaction on a different
connection instead of just failing [1].

Trouble is that from a DBCP perspective they don't know how the client is
going to use the borrowed Connection object. So implementing it in DBCP is
very tricky, if not impossible. So they didn't really like the idea as
such...

But in MetaModel we always know how we use connections. Either we use them
for a Query or for running an UpdateScript. Both of these actions are
repeatable and idempotent (since a crash in a UpdateScript yields a
transaction rollback). So I have a suggestion: I would like to have a
(optional) mechanism in MetaModel-jdbc where we can make the connection
handling deal with stale connections in a reactive way. The idea is to "let
it crash" but to retry while applying a validation query only when a crash
has happened. So in pseudo code like this:

Borrow a connection
Attempt to do the action with the connection
If Exception 'ex' is thrown:
Attempt to run the validation query on the connection
If the validation query passes, rethrow 'ex'.
Else discard the connection (not sure if we can tell the pool somehow that
it is invalid?) and retry.

What do you guys think? The configurable elements would IMO be:
What should the validation query be like?
How many retries may MetaModel transitively do?

Best regards,
Kasper

[1] https://www.mail-archive.com/dev@commons.apache.org/msg45627.html

Re: [DISCUSS] Reactively responding to a stale connection in a JDBC connection pool

[Kasper Sørensen <i....@gmail.com>]

Hmm yes I quite quickly ran into this issue actually... The connection for
a query is kept open until the point where the DataSet is closed. I would
say that DataSets are usually short-lived enough, but it's certainly not
guaranteed.

Whatever solution we might come up with will not be completely failure
proof (but neither is the "testOnBorrow" strategy). Makes me thing that
there's not so much point in over-engineering here. Maybe we should not try
to make a generic solution for all uses of a connection, but just do an
explicit check in case of a failure to execute a Query or execute an
UpdateScript (2 specific situations, not all usages of a Connection).

Or maybe this invalidates the idea altogether ... Hmm, I am just really NOT
happy about implementing "testOnBorrow" in the particular application that
I am looking at :-)

2014-09-30 18:40 GMT+02:00 Henry Saputra <he...@gmail.com>:

> But are we opening active connection to database all the time from
> MetaModel side?
>
> This stale connections happen when we have active connection open even
> after the query is done from metamodel side, don't they?
>
> - Henry
>
> On Mon, Sep 29, 2014 at 2:38 AM, Kasper Sørensen
> <i....@gmail.com> wrote:
> > Hi all,
> >
> > The other day I ran into this issue for the Nth time in my coding
> career...
> > A stale connection was lying around in my JDBC connection pool. It was
> > there because the DB server was restarted but the application (using
> > MetaModel) was not.
> >
> > Since the pool was a commons-dbcp BasicDataSource pool, I am aware of a
> few
> > ways to "solve" the issue. One known way is to set a "validation query"
> > (typically something like "SELECT 1") which will be called by the pool to
> > check the connections availability BEFORE borrowing the connection. That
> > means borrowing every connection comes with a significant (remove call)
> > overhead. I did write a suggestion to the commons-dbcp dev list about
> > making some improvement that would retry the transaction on a different
> > connection instead of just failing [1].
> >
> > Trouble is that from a DBCP perspective they don't know how the client is
> > going to use the borrowed Connection object. So implementing it in DBCP
> is
> > very tricky, if not impossible. So they didn't really like the idea as
> > such...
> >
> > But in MetaModel we always know how we use connections. Either we use
> them
> > for a Query or for running an UpdateScript. Both of these actions are
> > repeatable and idempotent (since a crash in a UpdateScript yields a
> > transaction rollback). So I have a suggestion: I would like to have a
> > (optional) mechanism in MetaModel-jdbc where we can make the connection
> > handling deal with stale connections in a reactive way. The idea is to
> "let
> > it crash" but to retry while applying a validation query only when a
> crash
> > has happened. So in pseudo code like this:
> >
> > Borrow a connection
> > Attempt to do the action with the connection
> > If Exception 'ex' is thrown:
> > Attempt to run the validation query on the connection
> > If the validation query passes, rethrow 'ex'.
> > Else discard the connection (not sure if we can tell the pool somehow
> that
> > it is invalid?) and retry.
> >
> > What do you guys think? The configurable elements would IMO be:
> > What should the validation query be like?
> > How many retries may MetaModel transitively do?
> >
> > Best regards,
> > Kasper
> >
> > [1] https://www.mail-archive.com/dev@commons.apache.org/msg45627.html
>

Re: [DISCUSS] Reactively responding to a stale connection in a JDBC connection pool

[Henry Saputra <he...@gmail.com>]

But are we opening active connection to database all the time from
MetaModel side?

This stale connections happen when we have active connection open even
after the query is done from metamodel side, don't they?

- Henry

On Mon, Sep 29, 2014 at 2:38 AM, Kasper Sørensen
<i....@gmail.com> wrote:
> Hi all,
>
> The other day I ran into this issue for the Nth time in my coding career...
> A stale connection was lying around in my JDBC connection pool. It was
> there because the DB server was restarted but the application (using
> MetaModel) was not.
>
> Since the pool was a commons-dbcp BasicDataSource pool, I am aware of a few
> ways to "solve" the issue. One known way is to set a "validation query"
> (typically something like "SELECT 1") which will be called by the pool to
> check the connections availability BEFORE borrowing the connection. That
> means borrowing every connection comes with a significant (remove call)
> overhead. I did write a suggestion to the commons-dbcp dev list about
> making some improvement that would retry the transaction on a different
> connection instead of just failing [1].
>
> Trouble is that from a DBCP perspective they don't know how the client is
> going to use the borrowed Connection object. So implementing it in DBCP is
> very tricky, if not impossible. So they didn't really like the idea as
> such...
>
> But in MetaModel we always know how we use connections. Either we use them
> for a Query or for running an UpdateScript. Both of these actions are
> repeatable and idempotent (since a crash in a UpdateScript yields a
> transaction rollback). So I have a suggestion: I would like to have a
> (optional) mechanism in MetaModel-jdbc where we can make the connection
> handling deal with stale connections in a reactive way. The idea is to "let
> it crash" but to retry while applying a validation query only when a crash
> has happened. So in pseudo code like this:
>
> Borrow a connection
> Attempt to do the action with the connection
> If Exception 'ex' is thrown:
> Attempt to run the validation query on the connection
> If the validation query passes, rethrow 'ex'.
> Else discard the connection (not sure if we can tell the pool somehow that
> it is invalid?) and retry.
>
> What do you guys think? The configurable elements would IMO be:
> What should the validation query be like?
> How many retries may MetaModel transitively do?
>
> Best regards,
> Kasper
>
> [1] https://www.mail-archive.com/dev@commons.apache.org/msg45627.html

Re: [DISCUSS] Reactively responding to a stale connection in a JDBC connection pool

[Kasper Sørensen <i....@gmail.com>]

I don't hear any objections nor any applauses :-) But I'll go and make a
draft solution - then we can maybe review it... I think the breaker might
be the last point: "not sure if we can tell the pool somehow that it is
invalid?"

2014-09-29 11:38 GMT+02:00 Kasper Sørensen <i....@gmail.com>:

> Hi all,
>
> The other day I ran into this issue for the Nth time in my coding
> career... A stale connection was lying around in my JDBC connection pool.
> It was there because the DB server was restarted but the application (using
> MetaModel) was not.
>
> Since the pool was a commons-dbcp BasicDataSource pool, I am aware of a
> few ways to "solve" the issue. One known way is to set a "validation query"
> (typically something like "SELECT 1") which will be called by the pool to
> check the connections availability BEFORE borrowing the connection. That
> means borrowing every connection comes with a significant (remove call)
> overhead. I did write a suggestion to the commons-dbcp dev list about
> making some improvement that would retry the transaction on a different
> connection instead of just failing [1].
>
> Trouble is that from a DBCP perspective they don't know how the client is
> going to use the borrowed Connection object. So implementing it in DBCP is
> very tricky, if not impossible. So they didn't really like the idea as
> such...
>
> But in MetaModel we always know how we use connections. Either we use them
> for a Query or for running an UpdateScript. Both of these actions are
> repeatable and idempotent (since a crash in a UpdateScript yields a
> transaction rollback). So I have a suggestion: I would like to have a
> (optional) mechanism in MetaModel-jdbc where we can make the connection
> handling deal with stale connections in a reactive way. The idea is to "let
> it crash" but to retry while applying a validation query only when a crash
> has happened. So in pseudo code like this:
>
> Borrow a connection
> Attempt to do the action with the connection
> If Exception 'ex' is thrown:
> Attempt to run the validation query on the connection
> If the validation query passes, rethrow 'ex'.
> Else discard the connection (not sure if we can tell the pool somehow that
> it is invalid?) and retry.
>
> What do you guys think? The configurable elements would IMO be:
> What should the validation query be like?
> How many retries may MetaModel transitively do?
>
> Best regards,
> Kasper
>
> [1] https://www.mail-archive.com/dev@commons.apache.org/msg45627.html
>