A reactive way to deal with stale connections

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

Hi,

In many projects I have been working on we're using commons-dbcp, and in
particular the BasicDataSource. A common set-up there is to use
"testOnBorrow" and a validation query to ensure that borrowed JDBC
connections are working when we get them.

The downside of this approach is that it's pretty heavy-weight to have to
test each borrowed connection when there's a high load on the system.

So I wanted to bounce an idea I have for improving the way we validate
connections. Maybe it can even become a contribution to commons-dbcp, or
maybe it's a application-specific improvement.

My idea is inspired by the "let it crash" (and retry) way of thinking.
Every time a connection would throw an exception, we would retry the latest
command. As such, each querying or updation operation would thus be wrapped
in a command, so that there's a central executor object that would manage
the retry mechanism etc.

Here's a small example:

-------

Command c = new Command() {
  public void doWithConnection(Connection c) {
    // do some querying or some updation stuff with the connection
  }
}

CommandExecutor executor = new CommandExecutor(dataSource);
executor.execute(c);

-------

We see issues with the connections very rarely, but we need to be able to
overcome it. I think this approach would archieve that. It could even
ensure that a transaction is created before executing the command, and
committed after the command. That way a failing command would not leave
behind side-effects.

Or do you guys see reasons why this would not work properly?
Would it make sense to put into commons-dbcp you think?
(I am working also on Apache MetaModel (incubating) and would consider
doing it there, if you don't think it's good for commons-dbcp).

Best regards,
Kasper Sørensen