Re: [OT] Curious difference in connection behaviour on database side DBCP vs. JDBC?
On 22.11.2013, at 02:20, Christopher Schultz ch...@christopherschultz.net wrote: I also think that this is a justifiable spec violation, and all I’m asking is that this fact is shown more prominently, esp. as JDBC pool is advertised as a drop-in replacement for DBCP. Fair enough. Care you file a documentation bug and possibly provide a patch? Will do, not sure I’ll get to it before the weekend though. Rainer - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: [OT] Curious difference in connection behaviour on database side DBCP vs. JDBC?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Rainer, On 11/21/13, 2:18 AM, Rainer Frey (Inxmail GmbH) wrote: On 20.11.2013, at 14:21, Christopher Schultz ch...@christopherschultz.net wrote: Rainer, FWIW, Connection.close also states this: Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released. Does that mean that all connection pools by design are in direct violation of the JDBC spec? I assume you’re referring to the Releases this Connection object's database resources” part, then yes, they’re in violation of the letter of the API spec. I’m not sure whether the Javadoc is regarded as binding as the spec document though. And following the letter would indeed defy the very purpose of the pool. The other pools that I know do free the JDBC resources though. And that’s the part of the behavior that is really visible to the application. (And yes, Javadoc says it is best practice to explicitly close the JDBC resources as early as possible, but it also states that one can get away with not doing so). I also think that this is a justifiable spec violation, and all I’m asking is that this fact is shown more prominently, esp. as JDBC pool is advertised as a drop-in replacement for DBCP. Fair enough. Care you file a documentation bug and possibly provide a patch? - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.15 (Darwin) Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJSjrFXAAoJEBzwKT+lPKRYTPMQAI/9LkWMM9h/VdmQdqIRiWpo DUZJhzTDs+BqVo5pjaomEpjIcUsjckyqOuvdJ2Fp1b2maUAFJd8E3DiqEQpYw4Kq 5lyPhk3MxWLmrjg5yqdnQzUb7Ay0RFQcjlKzwJ1xVGaaBam0erwpUuwKCxuf/xZk Svo2/Q+HxDPZeJfRetzGaBVfSCNW8eWKNQS0i0iJuFgLpvciesHb3YgTQC39ma/7 0JSOuwPvNX0+ulRVCYnXLUThhclO4mmWmnuo7RamAfx7hv+fKaKaZu1aI9eSrskn kMtsly8vbc9W1WL38pO7EiDmufVEBdlE2Qhmb88kJnzCEsp9wNxK2qGTRFj6CNmA KkGXXVag4B2z2ycHNmuxj4VS0y6RSxvhLsHAbiYaeitT8tpWkyxO0P+FOX4+Od86 cTR/iGyUQjUuVQ1wLMqsAlMNM0e28i9OkKmrOzNOjLnhre4RXH9Wd3f1tfwH5TBC m3Ll9SFgmWPHQzLxpinp5XqmyJVl9KlMbaW8rB/6ribhPPXoULUQnIVdPFhm7r2n zZ7QSYcBuW6fcqpM583VQJDmL/1dOaTcspRSWoBiRFVPq6SuIilaXjeIqNTf+SxQ BKgRPAs+hZ5B9jS2v5rBfvLoUfBf1I8YmohGd4xG8ifbLhzZXHirakMJhiW045fW ZYB3DK0svtw7+FkUKOId =sux5 -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Curious difference in connection behaviour on database side DBCP vs. JDBC?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256
Carl,
A late reply, but I believe you have some problems with your code.
Mark's comments about Tomcat-pool indicate that certain sloppy
resource-management activities may leave resources open on the server,
and I'm fairly sure that, given the code you have here, there are
likely resource issues in other places, too.
Keep reading for some commentary.
On 11/19/13, 8:32 AM, Carl Boberg wrote:
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
Just curious: under what conditions do you want to use
READ_UNCOMMITTED? That would seem to be more appropriate for a
particular set of queries and not the application-wide default.
defaultAutoCommit=true
url=jdbc:inetdae7://devdb12:1433/database1_dev
testOnBorrow=true validationQuery=SELECT 1
timeBetweenEvictionRunsMillis=1 removeAbandoned=true
removeAbandonedTimeout=600 maxWait=1/
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(); }
If you don't have try/catch around the connection.rollback() call, you
run the risk of leaking connections. If you leak a connection in this
dispose() method, you should be getting a) an exception logged from
your own code here:
// Close the connection connection.close(); if
(traceOpenedConnections) { timeConnectionClosed =
System.currentTimeMillis(); } }
} catch (java.sql.SQLException sqle) { sqle.printStackTrace(); }
... and b) an abandoned log from Tomcat-pool 5 minutes later
(removeAbandonedTimeout=600).
this.connection = null; }
If you have an object that is retaining a reference to a Connection
and the rollback fails, you won't clear this reference. If the
connection-wrapping object is not disposed, it might be re-used and
the connection either re-used or replaced with another checkout from
the pool.
Are you always sure to call dispose from a catch block after your JDBC
calls have completed?
// Deregister this ConnectionManger if (traceOpenedConnections) {
deregister(this); }
In the event that the Connection is indeed leaked, here you run the
risk of another resource leak in your application: presumably you are
tracking Connection objects somewhere, and this call removed that
stored reference. If the rollback fails, you won't de-register your
Connection and, even if removeAbandoned is working properly, those
Connection objects will remain in memory indefinitely and never be
cleaned-up.
I wrote a piece a long time ago about proper JDBC resource management.
I recommend that you read it as well:
http://blog.christopherschultz.net/index.php/2009/03/16/properly-handling-pooled-jdbc-connections/
- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.15 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iQIcBAEBCAAGBQJSjLZ6AAoJEBzwKT+lPKRYL0cP/0F9nT8tJlvybVyII/cjXOGY
P6TgUUOx79tO/Jw8Q6nbLP0x9yrqD6KyKVnJqlVPaj4055tDJoSa7IZqUYNNN7WX
qZcUphI8AeqFC6php8+sofr2Qq1skYFcgiL5xFmdNvGTnfWFVluH1NjUrI6udMol
w+5dy418xGnQKxEODLigGC1tJk+1M4q/gFjbj3NhxW6gbzSnlm+Nm+96ZD+q+wU6
UPcP7kZQVol5npAW2st8ki8bbJTLXF7P8ku+vGRQ8e2GvEWXOScrtOVxu0KrS015
DZvFAIPefIjGe7XxHjo7TlfSNymBcZ7y5j5RaN544uwEIr3Yu1dJ3au5HV48u8dO
tTEPjMRbGNTqECjjG/eVPohq9SHefWXMYXJCQjbIAnCyO0h7/HQVks1i3sT3doTt
b86wIiE94iFXjuz2rTKXR2jsrcOqzPkHoTL0NvkV5wpHsagmIjynKUDb2NjiSjCc
6GFom7ZSF8IYLKdIuT7qaVriYj1FrCCNCiW+giNJGeO7yrRoPiZdQ+NAxaLwpIum
l2WUh8JIoGE7v20HXXRFjOrY+iYuZQOu+vrlCah31SpSz291MirB/lZguGR2Yqfg
AgKdxHySaOhPOXDDRlHc+6TKtACWKeIsnbZxtqrrZGWYVNLGwoTofWoJc70fPW1v
l7s5HBNVhlIWyvKg49XV
=ptL4
-END PGP SIGNATURE-
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
Re: [OT] Curious difference in connection behaviour on database side DBCP vs. JDBC?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Rainer, On 11/20/13, 2:36 AM, Rainer Frey (Inxmail GmbH) wrote: On 19.11.2013, at 14:45, Mark Thomas ma...@apache.org wrote: On 19/11/2013 13:32, Carl Boberg wrote: 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? Commons DBCP tracks Statements and ResultSets when they are created and closes the associated Statements and ResultSets when the connection that created them is returned to the pool. Tomcat's JDBC pool does not do this. This is one of the reasons that Commons DBCP has a larger code base. JDBC spec states (9.4.4): An application calls the method Connection.close() to indicate that it has finished using a connection. All Statement objects created from a given Connection object will be closed when the close method for the Connection object is called. Javadoc of Connection.close() and Statement.close() at least imply that as well. ResultSet’s Javadoc explicitly states that a ResultSet is closed when the statement is closed. FWIW, Connection.close also states this: Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released. Does that mean that all connection pools by design are in direct violation of the JDBC spec? AFAICT the JDBC pool uses (as most connection pools) the Connection.close() as means to return a connection to the pool. While I understand that the semantics of completely closing a standalone connection and returning a pooled connection is different, this behavior is still a (presumably deliberate) violation of the spec, and makes the usage non-transparent to the application code. IMO this should be clearly stated in the JDBC pool’s docs, in an easily visible way. +1 - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.15 (Darwin) Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJSjLd2AAoJEBzwKT+lPKRYNNQP/3cfd2vtsqPdLRYca5yVg7cv e+6nLlZtzuCWRA2V0DFS7jLm433qz3ADyvnwlP03luusiAt4ShByQEAIu173Zya3 niQ49e/yJ9R2xBcoyat/Z1Q0YqSjLxh+tniy/RuK/sh6zPhOXL2EqxtRB1I4xNc4 ZywlWdjkqUr5hflUTvfsGv3E+X+b+WhBTxsoKbWK/2ZIWkd7XA9Cyv95qZ/F1srI EQEshMm0uL81CTI7EwsjxqdlJMyAKNQaeVlJnNz/mEtSHeh0Yiv+nBkJ71vcUz87 UtNpSJtCzOpKye6MxOsaTeq8HnJhohA6sbP9EAuAoqLJv+MFlEi0FuQ7/+eTX3vS 2kmielRWIqNFzXZvdNmq2bAvb66H414aGLy5UIjfce7hpYokNGueBZQPtYZgGkyr xV6h7Wj37hwGgJt4eVRe9Jg+UVgalhmdOpGmpjAY7XM1GpWq5P0ZwK8j8fw7smnF xPSskG2ZRCFUkY2VXy11DPxA8bQHTiZoiDwZAumdDRmBmujG3MSogLL6X/b3mDtZ OnqtJ9UC3v15KNTgZDLymK+UKQ+l6SkWOZeS4/R/vOpvnOe/sVboKEGGWBSoMVq6 VRaTv+l3Qi3yJ5K0R4Xauvd8PXO0606xqW3DVqEo/1BOqTap29BxFI2SxuoJUqlX VLxLOnTimqEeXydZ7dP4 =Tm7/ -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: [OT] Curious difference in connection behaviour on database side DBCP vs. JDBC?
On 20.11.2013, at 14:21, Christopher Schultz ch...@christopherschultz.net wrote: Rainer, FWIW, Connection.close also states this: Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released. Does that mean that all connection pools by design are in direct violation of the JDBC spec? I assume you’re referring to the Releases this Connection object's database resources” part, then yes, they’re in violation of the letter of the API spec. I’m not sure whether the Javadoc is regarded as binding as the spec document though. And following the letter would indeed defy the very purpose of the pool. The other pools that I know do free the JDBC resources though. And that’s the part of the behavior that is really visible to the application. (And yes, Javadoc says it is best practice to explicitly close the JDBC resources as early as possible, but it also states that one can get away with not doing so). I also think that this is a justifiable spec violation, and all I’m asking is that this fact is shown more prominently, esp. as JDBC pool is advertised as a drop-in replacement for DBCP. Rainer - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Curious difference in connection behaviour on database side DBCP vs. JDBC?
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=1
removeAbandoned=true
removeAbandonedTimeout=600
maxWait=1/
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=1
removeAbandoned=true
removeAbandonedTimeout=600
maxWait=1/
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
Re: Curious difference in connection behaviour on database side DBCP vs. JDBC?
On 19/11/2013 13:32, Carl Boberg wrote: 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? Commons DBCP tracks Statements and ResultSets when they are created and closes the associated Statements and ResultSets when the connection that created them is returned to the pool. Tomcat's JDBC pool does not do this. This is one of the reasons that Commons DBCP has a larger code base. As an aside, DBCP 2 is getting close to a first release. It has all the features of DBCP and nearly all of the speed in highly concurrent environments of Tomcat's JDBC pool. Snapshots are available if you want to test it. Alternatively, you can use a Tomcat 8 RC as Tomcat 8 uses DBCP2 rather than DBCP as the default database connection pooling. Mark - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Curious difference in connection behaviour on database side DBCP vs. JDBC?
On Nov 19, 2013, at 8:32 AM, Carl Boberg carl.bob...@memnonnetworks.com wrote:
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=1
removeAbandoned=true
removeAbandonedTimeout=600
maxWait=1/
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=1
removeAbandoned=true
removeAbandonedTimeout=600
maxWait=1/
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 you open it, you should close it. That goes for Connections, Statements and
ResultSets. If you suspect this might be an issue, you could always run a
static analysis tool, like FindBugs, against your code base. Tools like that
will generally catch this type of problem.
If so, why does DBCP handle it nicely and not JDBC?
I think Mark has answered this already. Only thing that t I would add is that
jdbc-pol does have a StatementFinalizer interceptor. I'm not sure if you've
tried this already, I didn't look back at your original post. The
documentation for it states that the interceptor Keeps track of all statements
created using createStatement, prepareStatement or prepareCall and closes these
statements when the connection is returned to the pool.. It doesn't seem as
comprehensive as DBCP, so depending on how you create your statements, it may
or may not be helpful. Also, it doesn't mention anything about ResultSet
tracking, so I don't think it'll help there.
Dan
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.
Re: Curious difference in connection behaviour on database side DBCP vs. JDBC?
On 19.11.2013, at 14:45, Mark Thomas ma...@apache.org wrote: On 19/11/2013 13:32, Carl Boberg wrote: 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? Commons DBCP tracks Statements and ResultSets when they are created and closes the associated Statements and ResultSets when the connection that created them is returned to the pool. Tomcat's JDBC pool does not do this. This is one of the reasons that Commons DBCP has a larger code base. JDBC spec states (9.4.4): An application calls the method Connection.close() to indicate that it has finished using a connection. All Statement objects created from a given Connection object will be closed when the close method for the Connection object is called. Javadoc of Connection.close() and Statement.close() at least imply that as well. ResultSet’s Javadoc explicitly states that a ResultSet is closed when the statement is closed. AFAICT the JDBC pool uses (as most connection pools) the Connection.close() as means to return a connection to the pool. While I understand that the semantics of completely closing a standalone connection and returning a pooled connection is different, this behavior is still a (presumably deliberate) violation of the spec, and makes the usage non-transparent to the application code. IMO this should be clearly stated in the JDBC pool’s docs, in an easily visible way. Rainer - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Curious difference in connection behaviour on database side DBCP vs. JDBC?
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=30 removeAbandoned=true removeAbandonedTimeout=600 maxWait=1/ 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=1 removeAbandoned=true removeAbandonedTimeout=1800 maxWait=1/ 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
Re: Curious difference in connection behaviour on database side DBCP vs. JDBC?
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=30 removeAbandoned=true removeAbandonedTimeout=600 maxWait=1/ 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 (30 vs 1) - 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
