tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christopher Schultz <ch...@christopherschultz.net>
Subject Re: [jdbc-pool] Transaction left open by the connection validation mechanism ?
Date Fri, 04 Jan 2013 20:34:51 GMT
-----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-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Mime
View raw message