Re­flec­tions on the testQuery prop­er­ty

The Atomikos con­nec­tion pool­ing mech­a­nism in­val­i­dates con­nec­tions when there are any er­rors - just to be sure that lat­er trans­ac­tions are not cor­rupt­ed by pri­or er­rors on the con­nec­tion stream to the back-end. Also, some­times con­nec­tions sim­ply time out in the back-end, and are closed with­out warn­ing. So it may hap­pen that you have some 'er­ro­neous' con­nec­tions in the pool at any giv­en time, and you will only find out the next time you try to use one of these con­nec­tions (i.e., in your ap­pli­ca­tion log­ic you will see ex­cep­tions re­lat­ed to this).

To avoid this (and have the pool proac­tive­ly val­i­date con­nec­tions for you) just set a testQuery on the AtomikosDataSourceBean in­stance. The idea is that you sup­ply a snip­pet of SQL code that can be used by the pool to test if the con­nec­tion is still valid. If not, it will be re­placed au­to­mat­i­cal­ly - and you should nev­er get any er­ro­neous con­nec­tion out of the pool.

The fact that the testQuery is op­tion­al has been con­fus­ing to some users. Con­se­quent­ly, we've been asked to make it re­quired or de­fault to some­thing mean­ing­ful. We've se­ri­ous­ly thought about this, but there are a few prob­lems here:

• Mak­ing it re­quired means break­ing a lot of ex­ist­ing, work­ing con­fig­u­ra­tions when they up­grade to our newest re­lease. That is be­cause these ex­ist­ing con­fig­u­ra­tions usu­al­ly do not in­clude any testQuery set­tings and the new re­lease would fail to read the con­fig­u­ra­tion and ini­tial­ize cor­rect­ly - there­by break­ing back­wards com­pat­i­bil­i­ty. We did not want to do this.
• Pro­vid­ing a rea­son­able de­fault is equal­ly dif­fi­cult, if not even hard­er: it turns out that there is no known SQL state­ment that will work for all DBMS. So our de­fault testQuery - whichev­er we choose - would al­ways fail on some sys­tems. We did not want to do this ei­ther.

So what did we do to im­prove this? After some in­put from our LinkedIn group we now have a se­ri­ous warn­ing mes­sage in the logs when­ev­er you don't set the testQuery.

Note: this will be avail­able in our very next re­lease - due be­gin­ning of Oc­to­ber.