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/[email protected]/msg45627.html
