On 04/01/2013 20:34, Christopher Schultz wrote:
> Pascal,
>
> On 1/3/13 12:49 PM, Davoust, Pascal wrote:
>> 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.
>
> When returning a Connection to the pool, jdbc-pool should be following
> the JDBC spec and committing any uncommitted transaction. The problem
> is likely during/after check-out and not with a check-in. THere could
> be bugs, of course.
>
>> 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!
>
> Good to know, but surprising.
>
>> 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;
>
> You (may) have a resource leak: you are not closing your statement in
> the case of an exception. You need a 'finally' block.I think Pascal is saying that the above snippet is in the Tomcat JDBC code, not his. p > http://blog.christopherschultz.net/index.php/2009/03/16/properly-handling-pooled-jdbc-connections/ > >> 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. > > I would tend to agree. Can you check the status of > Connection.getAutoCommit after fetching a connection from the pool? > > Also, is your altered transaction isolation level not appropriate for > all transactions? > >> 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? > > It may be a bug. Please create a minimal test case and attach it to a > bug report. Try to use the pool outside of Tomcat to simplify the > test-case even further. > > -chris > > --------------------------------------------------------------------- > To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org > -- [key:62590808]
signature.asc
Description: OpenPGP digital signature
