On Demand Validation

[marschall]

We are currently looking for a new connection pool. We are unhappy with our existing connection pool due to the validation overhead. We configured our exiting connection pool to validate on borrow. We are using Spring JdbcTemplate and as a consequence that happens quite frequently. Our application is a batch application. The validation behaviour we want is the ability to explicitly get a validated connection out of the pool at the start of a batch job. During the batch job the connection no longer needs to be validated, if it becomes invalid throwing an exception is fine as there is no recovery possible.

[simeonmalchev]

@marschall, is it possible that using a GetConnection hook may help you for what you need, see: https://github.com/vibur/vibur-dbcp/blob/master/src/main/java/org/vibur/dbcp/pool/Hook.java#L96 ?

You can register such hook and then when the hook is invoked you can perform custom validation based on, for example, the value of a ThreadLocal variable (or similar), which value you'll need to set as part of your batch process start-up phase. This hook will be still invoked each time a connection is taken/borrowed from the pool but it will perform an if-statement check in memory which will be much faster then performing a round-trip to the database for validation.

Alternatively, if you don't want to have such hook in your main connection pool that will be invoked many times during the normal lifecycle of your app (the non-batch part of it), you can create a separate pool for the batch part of your application with, for example, poolInitialSize=0 and poolMaxSize=4 params, and then register this hook for this pool only. If you really want to perform the validation only once at the beginning of the batch process (assuming that the batch process may take connection form the pool multiple times) you still need to implement some logic in the GetConnection hook which logic is based on flipping the value of a volatile boolean or AtomicBoolean variable, or something similar. This is what I can think of at the moment...

Note that if you decide to go this route with having a secondary pool for your batch app part, the Vibur pool will self shrink back to zero connections some time after the batch process has finished, so you won't have connections that are kept but staying unused (alive) in this pool.

[marschall]

Thank you for your answer, yes that would work. How would we signal from the hook that the connection is invalid and should therefore be removed from the pool and not handed out? Is simply throwing a SQLException enough?

I believe we can live with the overhead of a thread local, compared to the current overhead this should be negligible, especially considering that we're going to do a database call anyway.

[simeonmalchev]

Yes, if you throw an SQLException from the hook it will be (re-)thrown from the getConnection() call.

If you figure out that the ThreadLocal adds too much time you can always in future decide to go the other route with having two separate connection pools.

[marschall]

Thank you for the response.