Mailing-List: contact users-help@tomcat.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Tomcat Users List" <users@tomcat.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: RE: Connection Pooling and Teradata
Date: Wed, 18 Jun 2008 10:31:02 -0400
Message-ID: <D271577A4E747D45B0BA7450486CA05E01580C8F@resmsg04.AME.ad.sag>
In-Reply-To: <485911A5.4090609@christopherschultz.net>
Thread-Topic: Connection Pooling and Teradata
Thread-Index: AcjRTc7tRERdiUBQQVePDCO8ZVoPGwAAW/sg
References: <D271577A4E747D45B0BA7450486CA05E01580C8B@resmsg04.AME.ad.sag>
 <485911A5.4090609@christopherschultz.net>
From: "Katilie, John" <John.Katilie@softwareagusa.com>
To: "Tomcat Users List" <users@tomcat.apache.org>

Chris, Thanks for your reply. I'll do some more testing. May i ask a
dumb question? How did you turn on your tracing to get:

Wed Jun 18 09:39:20 EDT 2008 INFO: Profiler Event: [QUERY]      at
org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingS
tatement.java:208)
duration: 0 ms, connection-id: 1236, statement-id: 41, resultset-id: 39,
message: SELECT 1
Wed Jun 18 09:39:20 EDT 2008 INFO: Profiler Event: [FETCH]      at
org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingS
tatement.java:208)
duration: 0 ms, connection-id: 1236, statement-id: 41, resultset-id: 39
...

I tried doing a number of things but could never get any trace output
from org.apache.tomcat.dbcp.*. That's when I ended up turning on the
Teradata driver tracing by adding LOG=3DDEBUG in the url string.

Thanks again, John Katilie
=20
-----Original Message-----
From: Christopher Schultz [mailto:chris@christopherschultz.net]=20
Sent: Wednesday, June 18, 2008 9:46 AM
To: Tomcat Users List
Subject: Re: Connection Pooling and Teradata

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Katilie,

Katilie, John wrote:
| Basically, I have a web application that communicates with Teradata
| under Tomcat using a DBCP pooled connection. In my context.xml file I
| have:

[snip]

|       validationQuery=3D"Select 1"

[snip]

| Via JNDI we get the Resource object, get a connection, create a
| statement, issue the statement, close the statement and then close the
| connection. Everything works just fine. But looking at a Teradata
| command log and then turning on LOG=3DDEBUG on the URL I see 2 things.
|
| 1st. Everytime we get a connection I see a "Select 1" followed by a
| BT/Rollback being issued and

The "SELECT 1" is because of your validation query in the <Resource>
configuration. I'm not sure why a ROLLBACK is being issued -- perhaps
your driver does this automatically or something?

| 2nd: Everytime we close the connection I see an "Abort" followed by
| multiple ET/Commit's being issued

Again, this might be your driver. You should write yourself a little
test class to just issue a simple query, and turn on DEBUG mode on your
driver, to see what happens when connection pooling is not used.

Are you using transactions?

I'm using MySQL 5.0.x with Connector/J 5.0.8 on TC 5.5.23 and Java
1.5.0_13 and my JDBC driver reports the following queries when executing
"SELECT 'foo'" without a transaction or anything like that. Auto-commit
is set to "true".

Wed Jun 18 09:39:20 EDT 2008 INFO: Profiler Event: [QUERY]      at
org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingS
tatement.java:208)
duration: 0 ms, connection-id: 1236, statement-id: 41, resultset-id: 39,
message: SELECT 1
Wed Jun 18 09:39:20 EDT 2008 INFO: Profiler Event: [FETCH]      at
org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingS
tatement.java:208)
duration: 0 ms, connection-id: 1236, statement-id: 41, resultset-id: 39
Wed Jun 18 09:39:20 EDT 2008 INFO: Profiler Event: [QUERY]      at
org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingS
tatement.java:208)
duration: 1 ms, connection-id: 1236, statement-id: 42, resultset-id: 40,
message: SELECT 'foo'
Wed Jun 18 09:39:20 EDT 2008 INFO: Profiler Event: [FETCH]      at
org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingS
tatement.java:208)
duration: 0 ms, connection-id: 1236, statement-id: 42, resultset-id: 40

Through all that, the only queries executed are:

SELECT 1      (the validation query)
SELECT 'foo'  (the query I wanted to run)

| I've seen similar things with other DB's and am now wondering if I
have
| a problem or is this the way it is designed to work?
|
| Using connection pooling is it normal to issue the above commands when
a
| connection is obtained and closed? Is there something I can do to say
| don't do it? Is it an Application error, Tomcat error or JDBC driver
| error?

I would take some of your code out of your application and run it in a
test bed -- that will tell you how much of the overhead is coming from
your own application and how much is being added by DBCP. There's always
the possibility that your driver itself is adding these COMMIT/ROLLBACK
queries.

- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkhZEaUACgkQ9CaO5/Lv0PCjHACaAugA+Vnyv/6SV2bZch7JVTQ8
rPgAoIEcl71fbOeNO48FmZx65wizVJnD
=3DaI2l
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org