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.
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..
I automatically would assume there is something in the code not closing
statements properly and/or connection leak or some such... 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.
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"/>
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"/>
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
