Thanks for taking the time Daniel,
It is very hard to explain the problem since, and it was also stupid of me
to not include the fact that I have tried all kinds of similar combinations
of configuration in context.xml. With botch dbcp and jdbc pools
The behaviour persists. For example these are one version of config. I have
tested.
DBCP: behaviour is absent and all i well
<Resource name="database1"
auth="Container"
maxActive="50"
maxIdle="8"
minIdle="2"
initialSize="0"
username="'''''"
password="'''''"
driverClassName="com.inet.tds.TdsDriver"
type="javax.sql.DataSource"
defaultTransactionIsolation="READ_UNCOMMITTED"
url="jdbc:inetdae7://devdb12:1433/database1_dev"
testOnBorrow="true"
validationQuery="SELECT 1"
timeBetweenEvictionRunsMillis="10000"
removeAbandoned="true"
removeAbandonedTimeout="600"
maxWait="10000"/>
JDBC: I see the weird behaviour and my DBA is angry
<Resource name="database1"
auth="Container"
maxActive="50"
maxIdle="10"
minIdle="2"
initialSize="0"
username="'''''"
password="'''''"
driverClassName="com.inet.tds.TdsDriver"
type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
defaultTransactionIsolation="READ_UNCOMMITTED"
defaultAutoCommit="true"
url="jdbc:inetdae7://devdb12:1433/database1_dev"
testOnBorrow="true"
validationQuery="SELECT 1"
timeBetweenEvictionRunsMillis="10000"
removeAbandoned="true"
removeAbandonedTimeout="600"
maxWait="10000"/>
The behaviour applies to ALL queries/statements from the application.
I have here an example of the way we close from the application, (the devs
have named it dispose). From my untrained non java dev eye we do not seem
to be doing statement.Close(); and Im curious if that might be the issue?
If so, why does DBCP handle it nicely and not JDBC?
public void dispose() {
if (connection != null) {
try {
if (!connection.isClosed()) {
// If autoCommit is false, we are most likely using
transactions. A rollback will end the transaction
// properly even if a pool treats all actual
connections to the db as single long transactions.
// Examine the connection directly instead of relying
on ConnectionManager attribute.
if (ROLLBACK_ON_CLOSE && !connection.getAutoCommit()) {
connection.rollback();
}
// Close the connection
connection.close();
if (traceOpenedConnections) {
timeConnectionClosed = System.currentTimeMillis();
}
}
} catch (java.sql.SQLException sqle) {
sqle.printStackTrace();
}
this.connection = null;
}
// Deregister this ConnectionManger
if (traceOpenedConnections) {
deregister(this);
}
}
TNTUnilab delenda est
---
Carl Boberg
Operations
Memnon Networks AB
Tegnérgatan 34, SE-113 59 Stockholm
Mobile: +46(0)70 467 27 12
www.memnonnetworks.com
On 18 November 2013 18:23, Daniel Mikusa <dmik...@gopivotal.com> wrote:
> On Nov 18, 2013, at 9:48 AM, Carl Boberg <carl.bob...@memnonnetworks.com>
> wrote:
>
> > Hello,
> >
> > We have recently migrated from dbcp pool to the newer tomcat-jdbc pool.
> As
> > I understand, it is supposed to be almost a "drop in replacement" for
> dbcp.
>
> *Almost* is the key word here. It's very similar, but there are
> differences (typically with default values). Most of the time these don't
> matter, but occasionally you'll bump into them.
>
> > Everything works great except once small thing. Its a bit difficult for
> me
> > to explain but our DBA is really annoyed by it...
> >
> > With the new jdbc configuration, idle connections never really turn into
> > "normal idle" on the database side.
> > The DBS looks at the connections on the db side with sp_whoisactive
> > (excellent improvement of the sp_whoX procedures by Adam Machanic). It
> > should just show the statement/queries that are actually running. After
> > that the connection should be "empty" and idle and thus not be visible...
> > But with the new JDBC pool it shows the last statement/query executed in
> > the connection and it only changes when a new query comes from the
> > application (see below)* I therefore know that the connection gets reused
> > as an idle connection should but this is not the expected behaviour. It
> > should not "linger" on th db server like it does..
>
> Sorry, I'm not certain what you mean here. I don't use MSSQL, but perhaps
> someone else does and can comment.
>
> > I automatically would assume there is something in the code not closing
> > statements properly and/or connection leak or some such…
>
> Entirely possible. A good test here would be to deploy a trivial
> application that you know is closing connections properly. If that still
> exhibits the same behavior then you can rule out the application, plus
> you'll have a good test application that you can use to replicate the
> problem.
>
> Probably also worth enabling "logAbandoned", so that you can see if you're
> getting abandoned connections with either configuration.
>
> > BUT When using the old dbcp pool configuration all connections behaved
> as expected ..
> > .statement/query get run and the connection turns to a normal idle
> > connection with no information regarding earlier queries/statements...
> > therefore it never shows up in sp_whoisactive which is expected.
>
> It might help if you can be more specific about what these stored
> procedures are doing. What are they looking at? What is different about
> the data that they are looking at for DBCP & tomcat-jdbc?
>
> Also if you could correlate this data to what is shown in JMX for the
> pool, that would help. For example, if the database shows 5 connections
> with 3 idle, what does the connection pool show in JMX? Does this match?
>
> >
> > For example: rebooting the application an not doing anything (test env.
> no
> > users no nothing) if I execute sp_whoisactive I currently see the default
> > startup query from the app
> >
> > * 00 00:31:59.793 333 <?query -- update Account set loggedIn = 0 where
> > loggedIn = 1 --?> Sleeping
> >
> > Now this is a short simple fast query and it shouldn't show here but its
> > shows as been hanging around for half an hour. It doesn't hang around
> like
> > this if I revert to the old dbcp configuration... <- the issue
> >
> > Am I missing something fundamental in expected behaviour or should I just
> > go shout at the devs (who will only say "it worked with the old pool so
> it
> > should work the same now")...
> >
> > We are using tomcat 6.32 / jdk1.6.0_26 with MS SQL server 2012
> >
> > Old dbcp conf, everything works fully as expected
> > <Resource name="database1"
> > auth="Container"
> > maxActive="50"
> > maxIdle="8"
> > username="*****"
> > password="****"
> > driverClassName="com.inet.tds.TdsDriver"
> > type="javax.sql.DataSource"
> > defaultTransactionIsolation="READ_UNCOMMITTED"
> > url="jdbc:inetdae7://sqlserver12:1433/database1_dev"
> > timeBetweenEvictionRunsMillis="300000"
> > removeAbandoned="true"
> > removeAbandonedTimeout="600"
> > maxWait="10000"/>
>
> In this config, minIdle is going to be 0. That's the default listed here.
>
> https://commons.apache.org/proper/commons-dbcp/configuration.html
>
> In the config below, you're setting minIdle to 2. That means your new
> configuration will always have at least two connections to the database
> server, whereas the old one could idle down to zero.
>
> Some other differences in configuration:
>
> - old configuration doesn't specify a validation query
> - the timeBetweenEvictionRunsMillis is quite different (300000 vs 10000)
> - remove abandoned timeout is different by a factor of 3
> - maxIdle is 8 vs 10
> - initial size is 0 vs 2
>
> Probably worth making the configurations as similar as possible.
>
> >
> > JDBC configuration with tomca-jdbc.jar taken from tomcat7.42, has "idle
> > connections" with statement/query info in them, so that the dba never
> > really sees them as fully idle
> > <Resource name="database1"
> > auth="Container"
> > maxActive="50"
> > maxIdle="10"
> > minIdle="2"
> > initialSize="2"
> > username="*****"
> > password="*****"
> > driverClassName="com.inet.tds.TdsDriver"
> > type="javax.sql.DataSource"
> > factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
> > defaultTransactionIsolation="READ_UNCOMMITTED"
> > jdbcInterceptors="ConnectionState;StatementFinalizer"
> > defaultAutoCommit="true"
> > url="jdbc:inetdae7://sqlserver12:1433/database1_dev"
> > testOnBorrow="true"
> > validationQuery="SELECT 1"
> > timeBetweenEvictionRunsMillis="10000"
> > removeAbandoned="true"
> > removeAbandonedTimeout="1800"
> > maxWait="10000"/>
>
> Have you tried disabling the interceptors (remove the attribute or set it
> to "")? That would make the pool more similar to DBCP.
>
> Hope that helps!
>
> Dan
>
> >
> > I have googled, read documentation, browsed forums but never seen anyone
> > having an issue like this... and I admit, its nothing major. I'm just
> very
> > curious about the source to this difference in behaviour (and can I do
> > something configuration wise to resolve it?).
> >
> > Best regards
> > ---
> > Carl Boberg
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
> For additional commands, e-mail: users-h...@tomcat.apache.org
>
>
