Hi all,
I'm using the tomcat jdbc connection pool (7.0.34) to connect against a
PostgreSQL database (version 8.4 - jdbc driver 9.0.801) and I'm having a
potentially dodgy situation with the validation mechanism.
The code I'm using does change the transaction isolation level of a
connection that has been newly borrowed from the pool (before doing
anything else) but sometimes gets an error while doing so, such as:
org.postgresql.util.PSQLException: Cannot change transaction isolation
level in the middle of a transaction.
I've been investigating the code for a while to check whether any
transaction was left open (read: without any commit or rollback) before
being returned to the pool, but no chance, everything looks clean.
Then I suspected the validation mechanism which validates the connection.
Mine was set to validate the connection every 30 s while idle, using a
"SELECT 1" statement.
Indeed, I disabled the validation mechanism and tadaa, no error at all!
I then had a look at the corresponding code into the tomcat jdbc pool
(source 7.0.34), and more specifically in
class org.apache.tomcat.jdbc.pool.PooledConnection, method public boolean
validate(int validateAction,String sql) at line 452 (excerpt below):
Statement stmt = null;
try {
stmt = connection.createStatement();
stmt.execute(query);
stmt.close();
this.lastValidated = now;
return true;
} catch (Exception ex) {
if (getPoolProperties().getLogValidationErrors()) {
log.warn("SQL Validation error", ex);
} else if (log.isDebugEnabled()) {
log.debug("Unable to validate object:",ex);
}
if (stmt!=null)
try { stmt.close();} catch (Exception ignore2){/*NOOP*/}
}
return false;
It looks to me that there is a mistake here: if the connection to validate
does not have autoCommit set to true, then the transaction is started and
left open by this code.
Which explains the errors I was experiencing, since once the connection has
been validated, then the transaction isolation level cannot be changed
since a transaction is still ongoing!
Did I miss something or is this a real bug which I should file?
Thanks in advance
Regards
