You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by John Huss <jo...@gmail.com> on 2016/08/11 19:33:56 UTC
[jdbc-pool] Validation query leaves transaction open
I have verified that in version 8.0.36 of tomcat jdbc the validation query
functionality does not commit the transaction it starts in order to run the
validation query, which can cause an open transaction for a very long time
(possibly until the database crashes). This seems to be especially
problematic if you have a large pool where the connections are idle for a
large period of time.
I also tested against version 9.0.0M9, and in that version the problem is
fixed. However, nothing about this appears in the release notes for any
version (9.0, 8.5, or 8.0). If this bug has indeed been fixed it should be
documented in the release notes. Also, back-porting the fix to the 8.0
branch would be very helpful.
For the time being I am working around the problem by changing my
validation query from "SELECT 1" to be "SELECT 1; COMMIT" (using
postgresql). This prevents the issue.
This was reported previously (three years ago) here:
https://mail-archives.apache.org/mod_mbox/tomcat-users/201301.mbox/%3CCAPw873vy%2B9kWMQiFPbnTX6g1FSQ8oTVhDJ3Zagp9UkUZ3d5qjw%40mail.gmail.com%3E
Thanks,
John
Re: [jdbc-pool] Validation query leaves transaction open
Posted by John Huss <jo...@gmail.com>.
I don't have one. I'm creating the pool in code. Basically like this:
PoolConfiguration p = new PoolProperties();
p.setUrl(url);
p.setDriverClassName(driverClass);
p.setUsername(username);
p.setPassword(password);
p.setJmxEnabled(true);
p.setTestWhileIdle(true);
p.setTestOnBorrow(true);
p.setTestOnReturn(false);
p.setValidationQuery("SELECT 1");
p.setValidationInterval(30000);
p.setValidationQueryTimeout(15000);
p.setLogValidationErrors(true);
p.setTimeBetweenEvictionRunsMillis(60000);
p.setMaxActive(maxConnections);
p.setMaxIdle(Math.max(maxConnections / 2, minConnections));
p.setMinIdle(minConnections);
p.setInitialSize(minConnections);
p.setMaxWait(maxWait);
p.setRemoveAbandoned(true);
p.setRemoveAbandonedTimeout(60*30); // 30 minutes (in seconds)
p.setLogAbandoned(true);
p.setMinEvictableIdleTimeMillis(5*60*1000); // 5 minutes
p.setFairQueue(true);
p.setJdbcInterceptors(
"org.apache.tomcat.jdbc.pool.interceptor.ConnectionState"
+";org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
);
org.apache.tomcat.jdbc.pool.DataSource result = new
org.apache.tomcat.jdbc.pool.DataSource(p);
On Mon, Aug 15, 2016 at 4:44 PM Christopher Schultz <
chris@christopherschultz.net> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA256
>
> John,
>
> On 8/11/16 3:33 PM, John Huss wrote:
> > I have verified that in version 8.0.36 of tomcat jdbc the
> > validation query functionality does not commit the transaction it
> > starts in order to run the validation query, which can cause an
> > open transaction for a very long time (possibly until the database
> > crashes). This seems to be especially problematic if you have a
> > large pool where the connections are idle for a large period of
> > time.
> >
> > I also tested against version 9.0.0M9, and in that version the
> > problem is fixed. However, nothing about this appears in the
> > release notes for any version (9.0, 8.5, or 8.0). If this bug has
> > indeed been fixed it should be documented in the release notes.
> > Also, back-porting the fix to the 8.0 branch would be very
> > helpful.
> >
> > For the time being I am working around the problem by changing my
> > validation query from "SELECT 1" to be "SELECT 1; COMMIT" (using
> > postgresql). This prevents the issue.
> >
> > This was reported previously (three years ago) here:
> > https://mail-archives.apache.org/mod_mbox/tomcat-users/201301.mbox/%3C
> CAPw873vy%2B9kWMQiFPbnTX6g1FSQ8oTVhDJ3Zagp9UkUZ3d5qjw%40mail.gmail.com%3
> E
>
> Can
> >
> you post your whole (sanitized) <Resource> definition?
>
> Thanks,
> - -chris
> -----BEGIN PGP SIGNATURE-----
> Comment: GPGTools - http://gpgtools.org
> Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
>
> iQIcBAEBCAAGBQJXsjewAAoJEBzwKT+lPKRYqacP/j/IUunibNUhUMx+FzyfBVZo
> wK2WcQdM/gdjo6VvhT30A5ndf0HgD8PxHt+7ZaQ8LPQ3cqcLem2KhZvaMD0Kaepw
> k2+HCCayv89KhDXBlvLdKBKJ9mNT1s7cgmNLsF0xdvEioTi8KAbJtG0JOndYwQFb
> MUuYBifOoW4ir47tQmETnu0z6rGvezTRu7eycbiaUZw0jNoQgy0WojJQqm2mmQou
> uLkQghDHivqeauCLf/SbnHnJR1RuXTxef6v25M3a0rbC1XpLE9mrv/CExI92Vm9R
> 94iR7olGErxJbjZywSBM4sbdlhT91IFbSeIAh/Q6FlnC2MbJxEH5naUHVIX9q36R
> p94412emJ2tBWxLB8VY5E9hJVeTNzOe2+bhjBypj9z9aewuCi9oDfVs5pTTmKedu
> LypcLCWsXPg4/VQ4DQ6VL81igAnLnNZbcWNueBTQiqEg2F5c3pjEwfc/G4YqTfQ0
> byTRBraKBR3FLU8n9kkwpNpO4MMVX/keUTD1NEhP/NL8ldh+nGroekFx1JBmzIk4
> 5ID8gvtelcS9Mwp+nxz4uUuXYy0iDe4pMX50QRG2AY+Hxye4rfX9NmCG/BYhwNAQ
> x0X/16fZ4usyKOyj75Lya6IfbMTq2ufv8rVciKaIJJXybQNAhgWvO2F1VZHTgdIG
> HT8rSho2EhsqjyNlR2s4
> =28er
> -----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] Validation query leaves transaction open
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
John,
On 8/11/16 3:33 PM, John Huss wrote:
> I have verified that in version 8.0.36 of tomcat jdbc the
> validation query functionality does not commit the transaction it
> starts in order to run the validation query, which can cause an
> open transaction for a very long time (possibly until the database
> crashes). This seems to be especially problematic if you have a
> large pool where the connections are idle for a large period of
> time.
>
> I also tested against version 9.0.0M9, and in that version the
> problem is fixed. However, nothing about this appears in the
> release notes for any version (9.0, 8.5, or 8.0). If this bug has
> indeed been fixed it should be documented in the release notes.
> Also, back-porting the fix to the 8.0 branch would be very
> helpful.
>
> For the time being I am working around the problem by changing my
> validation query from "SELECT 1" to be "SELECT 1; COMMIT" (using
> postgresql). This prevents the issue.
>
> This was reported previously (three years ago) here:
> https://mail-archives.apache.org/mod_mbox/tomcat-users/201301.mbox/%3C
CAPw873vy%2B9kWMQiFPbnTX6g1FSQ8oTVhDJ3Zagp9UkUZ3d5qjw%40mail.gmail.com%3
E
Can
>
you post your whole (sanitized) <Resource> definition?
Thanks,
- -chris
-----BEGIN PGP SIGNATURE-----
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iQIcBAEBCAAGBQJXsjewAAoJEBzwKT+lPKRYqacP/j/IUunibNUhUMx+FzyfBVZo
wK2WcQdM/gdjo6VvhT30A5ndf0HgD8PxHt+7ZaQ8LPQ3cqcLem2KhZvaMD0Kaepw
k2+HCCayv89KhDXBlvLdKBKJ9mNT1s7cgmNLsF0xdvEioTi8KAbJtG0JOndYwQFb
MUuYBifOoW4ir47tQmETnu0z6rGvezTRu7eycbiaUZw0jNoQgy0WojJQqm2mmQou
uLkQghDHivqeauCLf/SbnHnJR1RuXTxef6v25M3a0rbC1XpLE9mrv/CExI92Vm9R
94iR7olGErxJbjZywSBM4sbdlhT91IFbSeIAh/Q6FlnC2MbJxEH5naUHVIX9q36R
p94412emJ2tBWxLB8VY5E9hJVeTNzOe2+bhjBypj9z9aewuCi9oDfVs5pTTmKedu
LypcLCWsXPg4/VQ4DQ6VL81igAnLnNZbcWNueBTQiqEg2F5c3pjEwfc/G4YqTfQ0
byTRBraKBR3FLU8n9kkwpNpO4MMVX/keUTD1NEhP/NL8ldh+nGroekFx1JBmzIk4
5ID8gvtelcS9Mwp+nxz4uUuXYy0iDe4pMX50QRG2AY+Hxye4rfX9NmCG/BYhwNAQ
x0X/16fZ4usyKOyj75Lya6IfbMTq2ufv8rVciKaIJJXybQNAhgWvO2F1VZHTgdIG
HT8rSho2EhsqjyNlR2s4
=28er
-----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] Validation query leaves transaction open
Posted by Jose María Zaragoza <de...@gmail.com>.
2016-08-12 13:46 GMT+02:00 John Huss <jo...@gmail.com>:
> In every database I have used every query requires a transaction. Often
> they will start them automatically if you don't do it explicitly.
Right , but I guess that Tomcat uses JDBC connections and I wonder why
for executing validationQuery sentence, Tomcat doesn't use autocommit
= true setting
> On Fri, Aug 12, 2016 at 4:13 AM Jose María Zaragoza <de...@gmail.com>
> wrote:
>
>> 2016-08-11 21:33 GMT+02:00 John Huss <jo...@gmail.com>:
>> > I have verified that in version 8.0.36 of tomcat jdbc the validation
>> query
>> > functionality does not commit the transaction it starts in order to run
>> the
>> > validation query, which can cause an open transaction for a very long
>> time
>> > (possibly until the database crashes). This seems to be especially
>> > problematic if you have a large pool where the connections are idle for a
>> > large period of time.
>> >
>> > I also tested against version 9.0.0M9, and in that version the problem is
>> > fixed. However, nothing about this appears in the release notes for any
>> > version (9.0, 8.5, or 8.0). If this bug has indeed been fixed it should
>> be
>> > documented in the release notes. Also, back-porting the fix to the 8.0
>> > branch would be very helpful.
>> >
>> > For the time being I am working around the problem by changing my
>> > validation query from "SELECT 1" to be "SELECT 1; COMMIT" (using
>> > postgresql). This prevents the issue.
>>
>>
>> I wonder why a validationQuery needs to open a new transaction
>>
>>
>> >
>> > This was reported previously (three years ago) here:
>> >
>> https://mail-archives.apache.org/mod_mbox/tomcat-users/201301.mbox/%3CCAPw873vy%2B9kWMQiFPbnTX6g1FSQ8oTVhDJ3Zagp9UkUZ3d5qjw%40mail.gmail.com%3E
>> >
>> > Thanks,
>> > John
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>>
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: [jdbc-pool] Validation query leaves transaction open
Posted by John Huss <jo...@gmail.com>.
In every database I have used every query requires a transaction. Often
they will start them automatically if you don't do it explicitly.
On Fri, Aug 12, 2016 at 4:13 AM Jose María Zaragoza <de...@gmail.com>
wrote:
> 2016-08-11 21:33 GMT+02:00 John Huss <jo...@gmail.com>:
> > I have verified that in version 8.0.36 of tomcat jdbc the validation
> query
> > functionality does not commit the transaction it starts in order to run
> the
> > validation query, which can cause an open transaction for a very long
> time
> > (possibly until the database crashes). This seems to be especially
> > problematic if you have a large pool where the connections are idle for a
> > large period of time.
> >
> > I also tested against version 9.0.0M9, and in that version the problem is
> > fixed. However, nothing about this appears in the release notes for any
> > version (9.0, 8.5, or 8.0). If this bug has indeed been fixed it should
> be
> > documented in the release notes. Also, back-porting the fix to the 8.0
> > branch would be very helpful.
> >
> > For the time being I am working around the problem by changing my
> > validation query from "SELECT 1" to be "SELECT 1; COMMIT" (using
> > postgresql). This prevents the issue.
>
>
> I wonder why a validationQuery needs to open a new transaction
>
>
> >
> > This was reported previously (three years ago) here:
> >
> https://mail-archives.apache.org/mod_mbox/tomcat-users/201301.mbox/%3CCAPw873vy%2B9kWMQiFPbnTX6g1FSQ8oTVhDJ3Zagp9UkUZ3d5qjw%40mail.gmail.com%3E
> >
> > Thanks,
> > John
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: [jdbc-pool] Validation query leaves transaction open
Posted by Jose María Zaragoza <de...@gmail.com>.
2016-08-11 21:33 GMT+02:00 John Huss <jo...@gmail.com>:
> I have verified that in version 8.0.36 of tomcat jdbc the validation query
> functionality does not commit the transaction it starts in order to run the
> validation query, which can cause an open transaction for a very long time
> (possibly until the database crashes). This seems to be especially
> problematic if you have a large pool where the connections are idle for a
> large period of time.
>
> I also tested against version 9.0.0M9, and in that version the problem is
> fixed. However, nothing about this appears in the release notes for any
> version (9.0, 8.5, or 8.0). If this bug has indeed been fixed it should be
> documented in the release notes. Also, back-porting the fix to the 8.0
> branch would be very helpful.
>
> For the time being I am working around the problem by changing my
> validation query from "SELECT 1" to be "SELECT 1; COMMIT" (using
> postgresql). This prevents the issue.
I wonder why a validationQuery needs to open a new transaction
>
> This was reported previously (three years ago) here:
> https://mail-archives.apache.org/mod_mbox/tomcat-users/201301.mbox/%3CCAPw873vy%2B9kWMQiFPbnTX6g1FSQ8oTVhDJ3Zagp9UkUZ3d5qjw%40mail.gmail.com%3E
>
> Thanks,
> John
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org