You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by "Davoust, Pascal" <pa...@eptica.com> on 2013/01/03 18:49:14 UTC
[jdbc-pool] Transaction left open by the connection validation
mechanism ?
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
Re: [jdbc-pool] Transaction left open by the connection validation
mechanism ?
Posted by "Davoust, Pascal" <pa...@eptica.com>.
Sorry, I probably did not make myself clear.
I certainly do not suggest to issue the rollback on each connection
checkout, but only as part of the validation process - because this is
where the problem lies and only there - which I would imagine is triggered
only occasionally (when connection is idle for example): I doubt someone
would actually validate connections each time it's checked or out in. But I
might be wrong.
In any case, I suppose that the pool must not hand over a connection at
check-out time with an open transaction triggered by the internal
validation mechanism of the pool - irrespective of what you do with the
connection.
Am I wrong?
Regards
Le 5 janv. 2013 00:20, "Christopher Schultz" <ch...@christopherschultz.net>
a écrit :
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA256
>
> Pascal,
>
> On 1/4/13 5:56 PM, Davoust, Pascal wrote:
> > Indeed I was referring to the tomcat jdbc-pool code, not mine :-).
> >
> > Now, I would suggest to consider that this problem is more common
> > than you could imagine at first sight. First because this is
> > randomly occuring, but frequently enough so that it shows up in
> > quite a few areas. A few examples where this problem occurred and
> > was filed (but not analyzed down to the root cause):
> >
> > Spring DataSource Txn Mgr :
> >
> https://jira.springsource.org/browse/SPR-8870?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel
> >
> > Hyperic : https://jira.hyperic.com/browse/HHQ-5054
> >
> > If I'm correct, then I suggest that the behavior is corrected by
> > default, not requiring additional configuration.
>
> I disagree: issuing a ROLLBACK on every connection-checkout seems
> heavy-handed if it's not necessary. You are talking about a fairly
> rare set of circumstances (e.g. changing tx isolation level for only
> some uses of the connection-pool) and punishing everyone (performance)
> for that circumstance is IMHO unnecessary.
>
> - -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/
>
> iEYEAREIAAYFAlDnY5oACgkQ9CaO5/Lv0PARVgCgvPLCsydboLrgcLiQUg9wD/zI
> g/MAn0ZakKw6QgXd7n47UZ3CRtaYrRex
> =j8ND
> -----END PGP SIGNATURE-----
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: [jdbc-pool] Transaction left open by the connection validation
mechanism ?
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
Pascal,
On 1/4/13 5:56 PM, Davoust, Pascal wrote:
> Indeed I was referring to the tomcat jdbc-pool code, not mine :-).
>
> Now, I would suggest to consider that this problem is more common
> than you could imagine at first sight. First because this is
> randomly occuring, but frequently enough so that it shows up in
> quite a few areas. A few examples where this problem occurred and
> was filed (but not analyzed down to the root cause):
>
> Spring DataSource Txn Mgr :
> https://jira.springsource.org/browse/SPR-8870?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel
>
> Hyperic : https://jira.hyperic.com/browse/HHQ-5054
>
> If I'm correct, then I suggest that the behavior is corrected by
> default, not requiring additional configuration.
I disagree: issuing a ROLLBACK on every connection-checkout seems
heavy-handed if it's not necessary. You are talking about a fairly
rare set of circumstances (e.g. changing tx isolation level for only
some uses of the connection-pool) and punishing everyone (performance)
for that circumstance is IMHO unnecessary.
- -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/
iEYEAREIAAYFAlDnY5oACgkQ9CaO5/Lv0PARVgCgvPLCsydboLrgcLiQUg9wD/zI
g/MAn0ZakKw6QgXd7n47UZ3CRtaYrRex
=j8ND
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: [jdbc-pool] Transaction left open by the connection validation
mechanism ?
Posted by "Davoust, Pascal" <pa...@eptica.com>.
Indeed I was referring to the tomcat jdbc-pool code, not mine :-).
Now, I would suggest to consider that this problem is more common than you
could imagine at first sight. First because this is randomly occuring, but
frequently enough so that it shows up in quite a few areas.
A few examples where this problem occurred and was filed (but not analyzed
down to the root cause):
Spring DataSource Txn Mgr :
https://jira.springsource.org/browse/SPR-8870?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel
Hyperic :
https://jira.hyperic.com/browse/HHQ-5054
If I'm correct, then I suggest that the behavior is corrected by default,
not requiring additional configuration. Especially so that most people use
the provided sample and documentation, which enables the validation
mechanism. Or maybe do it the other way round, have a configuration key
which allows to disable the rollback after validation... One last way would
have been to test against the auto commit state of the connection to decide
whether a rollback was necessary, but I believe that the pool does not
maintain the connection state for performance reasons (unless an
interceptor is configured to do it) and therefore would be an even more
detrimental hit on performance since would require a round-trip to the db
server...
In any case, let me know if you need me to provide more details or to file
this problem into the bug tracking system!
Regards
Pascal
2013/1/4 Christopher Schultz <ch...@christopherschultz.net>
> > I think Pascal is saying that the above snippet is in the Tomcat
> > JDBC code, not his.
>
> Whoops, I think you're right. That's a minor bug, but definitely one
> that should be fixed IMO.
>
> Also, if the validation query succeeds and the connection is in
> auto-commit mode, we may want to issue a rollback. I wouldn't want to
> do that without any additional configuration, though, because it would
> needlessly send ROLLBACK queries in many cases (such as other
> databases - MySQL doesn't get upset when I tried a simple, similar
> test - or when you don't intend to change the transaction isolation
> level, etc.).
>
> Perhaps a configuration directive like rollBackAfterValidationQuery or
> something?
>
> - -chris
>
Re: [jdbc-pool] Transaction left open by the connection validation
mechanism ?
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
Pid,
On 1/4/13 4:26 PM, Pid wrote:
> On 04/01/2013 20:34, Christopher Schultz wrote:
>> On 1/3/13 12:49 PM, Davoust, Pascal wrote:
>>>
>>> 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.
Whoops, I think you're right. That's a minor bug, but definitely one
that should be fixed IMO.
Also, if the validation query succeeds and the connection is in
auto-commit mode, we may want to issue a rollback. I wouldn't want to
do that without any additional configuration, though, because it would
needlessly send ROLLBACK queries in many cases (such as other
databases - MySQL doesn't get upset when I tried a simple, similar
test - or when you don't intend to change the transaction isolation
level, etc.).
Perhaps a configuration directive like rollBackAfterValidationQuery or
something?
- -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/
iEYEAREIAAYFAlDnS80ACgkQ9CaO5/Lv0PCCYQCguRp0qAPS/ViLhUKGyV+Tc6mY
2qMAn3DpYdxDl5zo9EgLpqDSIFPmCDrD
=U427
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: [jdbc-pool] Transaction left open by the connection validation
mechanism ?
Posted by Pid <pi...@pidster.com>.
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-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
--
[key:62590808]
Re: [jdbc-pool] Transaction left open by the connection validation
mechanism ?
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----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