Reflections on the testQuery property

The Atomikos connection pooling mechanism invalidates connections when there are any errors - just to be sure that later transactions are not corrupted by prior errors on the connection stream to the back-end. Also, sometimes connections simply time out in the back-end, and are closed without warning. So it may happen that you have some 'erroneous' connections in the pool at any given time, and you will only find out the next time you try to use one of these connections (i.e., in your application logic you will see exceptions related to this).

To avoid this (and have the pool proactively validate connections for you) just set a testQuery on the AtomikosDataSourceBean instance. The idea is that you supply a snippet of SQL code that can be used by the pool to test if the connection is still valid. If not, it will be replaced automatically - and you should never get any erroneous connection out of the pool.

The fact that the testQuery is optional has been confusing to some users. Consequently, we've been asked to make it required or default to something meaningful. We've seriously thought about this, but there are a few problems here:

• Making it required means breaking a lot of existing, working configurations when they upgrade to our newest release. That is because these existing configurations usually do not include any testQuery settings and the new release would fail to read the configuration and initialize correctly - thereby breaking backwards compatibility. We did not want to do this.
• Providing a reasonable default is equally difficult, if not even harder: it turns out that there is no known SQL statement that will work for all DBMS. So our default testQuery - whichever we choose - would always fail on some systems. We did not want to do this either.

So what did we do to improve this? After some input from our LinkedIn group we now have a serious warning message in the logs whenever you don't set the testQuery.

Note: this will be available in our very next release - due beginning of October.