-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256 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. 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 -----BEGIN PGP SIGNATURE----- Version: GnuPG/MacGPG2 v2.0.17 (Darwin) Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with undefined - http://www.enigmail.net/ iEYEAREIAAYFAlDnPOsACgkQ9CaO5/Lv0PBFDwCgmkKmjhadwgLBvZlZwuhTBFRR gnkAoI3/FyDsXiqtFxWoZFcsV8KrZnfX =wr5g -----END PGP SIGNATURE----- --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org