You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by lightbulb432 <ve...@hotmail.com> on 2007/06/01 21:01:22 UTC
Connection Pool and Connections
When using the tomcat-dbcp DataSource, when my web application code gets a
connection:
myConnection = myDataSource.getConnection();
then executes multiple separate statements
myStatement1 = myConnection.createStatement();
myStatement1.execute();
myStatement2 = myConnection.createStatement();
myStatement2.execute();
then close the connection
myConnection.close();
Is it possible that myStatement1 and myStatement2 would be run using
different physical database connections, or are they absolutely guaranteed
to be executed using the same connection?
Or is connection pooling only for not actually closing the physical database
connection on myConnection.close(), instead returning it to the connection
pool?
A different way of asking this is does connection pooling pool connections
within an application connection (myDataSource.getConnection() and
myConnection.close()), or between application connections?
If this question doesn't make sense, I can clarify. Thanks a lot.
--
View this message in context: http://www.nabble.com/Connection-Pool-and-Connections-tf3853952.html#a10918685
Sent from the Tomcat - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
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
Re: [OT] Connection Pool and Connections
Posted by Pid <p...@pidster.com>.
Christopher Schultz wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> lightbulb,
>
> (I marked this as off-topic since it's really a JDBC/DBCP issue, not a
> Tomcat one. I'm still happy to answer.)
>
> lightbulb432 wrote:
>> Is it possible that myStatement1 and myStatement2 would be run using
>> different physical database connections, or are they absolutely guaranteed
>> to be executed using the same connection?
>
> They are guaranteed to act like they are executing through the same
> connection, even if they aren't (not sure how loose the spec is). You
> should check the JDBC specification to make sure, but I would imagine
> that splitting statements across connections would be more trouble than
> it's worth... I doubt very seriously that anyone would ever want to do this.
>
>> Or is connection pooling only for not actually closing the physical database
>> connection on myConnection.close(), instead returning it to the connection
>> pool?
>
> Pretty much. The connection pool simply ... pools connections. Once you
> have a connection, you can do whatever you want (including never
> returning it to the pool, which isn't a good idea, of course).
>
>> A different way of asking this is does connection pooling pool connections
>> within an application connection (myDataSource.getConnection() and
>> myConnection.close()), or between application connections?
Also worth noting is that if you can use a PreparedStatement (because
the instruction is the same and the data is different), then the
statement/object itself is re-used which further optimises your application.
p
> Well... when you call myDataSource.getConnection, it gives you a
> connection from the pool. When you call connection.close(), it returns
> the connection to the pool. Technically, while you have control of the
> connection, it's not pooled... it's "out of the pool with the intent to
> be returned soon".
>
>> If this question doesn't make sense, I can clarify. Thanks a lot.
>
> I hope my answer makes sense ;)
>
> - -chris
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.7 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iD8DBQFGYH4s9CaO5/Lv0PARAihvAJwKgt3EccemfZ9AhtT6JI0EKAjF7wCgnu8g
> v0pYh6pxJPAZTt2lEGZXjJ4=
> =AGDl
> -----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
>
>
Re: [OT] Connection Pool and Connections
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
lightbulb,
(I marked this as off-topic since it's really a JDBC/DBCP issue, not a
Tomcat one. I'm still happy to answer.)
lightbulb432 wrote:
> Is it possible that myStatement1 and myStatement2 would be run using
> different physical database connections, or are they absolutely guaranteed
> to be executed using the same connection?
They are guaranteed to act like they are executing through the same
connection, even if they aren't (not sure how loose the spec is). You
should check the JDBC specification to make sure, but I would imagine
that splitting statements across connections would be more trouble than
it's worth... I doubt very seriously that anyone would ever want to do this.
> Or is connection pooling only for not actually closing the physical database
> connection on myConnection.close(), instead returning it to the connection
> pool?
Pretty much. The connection pool simply ... pools connections. Once you
have a connection, you can do whatever you want (including never
returning it to the pool, which isn't a good idea, of course).
> A different way of asking this is does connection pooling pool connections
> within an application connection (myDataSource.getConnection() and
> myConnection.close()), or between application connections?
Well... when you call myDataSource.getConnection, it gives you a
connection from the pool. When you call connection.close(), it returns
the connection to the pool. Technically, while you have control of the
connection, it's not pooled... it's "out of the pool with the intent to
be returned soon".
> If this question doesn't make sense, I can clarify. Thanks a lot.
I hope my answer makes sense ;)
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFGYH4s9CaO5/Lv0PARAihvAJwKgt3EccemfZ9AhtT6JI0EKAjF7wCgnu8g
v0pYh6pxJPAZTt2lEGZXjJ4=
=AGDl
-----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
Re: Connection Pool and Connections
Posted by Johnny Kewl <jo...@kewlstuff.co.za>.
Hi LightBulb...
No its not going to work like I think you want it to...
Statement stmt= con.createStatement();
ResultSet resultSet = stmt.executeQuery(sSql); //For
Select
and say
Statement stmt = con.createStatement();
recs = stmt.executeUpdate(sSql); //For UPDATE
Will use the same connection because you just opened it or got it from the
pool
BUT... they work independently......
ie one can succeed and the other can fail... and thats what I think you
asking
If you want them to behave as a single TRANSACTION... its got nothing to do
with the connection.
Code has to look something like this
con.setAutoCommit(false);
PreparedStatement ps1 = con.prepareStatement(sSql1);
PreparedStatement ps2 = con.prepareStatement(sSql2);
ps1.executeUpdate();
ps2.executeUpdate();
con.commit(); //Commit all statements together or none at
all
con.setAutoCommit(true);
Now they must both work.... or both fail.... read up on it.... you can also
rollback
and set roll back points and do all sorts of fancy things.
In normal JDBC programming you
OPEN CONNECTION
Execute Queries
CLOSE CONNECTION
but opening a connection takes a long time.... will make app slow
So all the pool does is open a whole lot of connections.... and it will give
you one instantly.
When you give it back... it doesnt close it, it holds it open for another
thread to use...
That all it really does....
What maybe confusing you is that in EJB application servers they hide the dB
behind an entity bean, and then let you set annotations that control the
transactions... but they have to coz the sql is now hidden... ultimately
(simplified) underneath... the above is all thats happening.
Tomcat is not an EJB server... but once you know how to do POJO, its kinda
nice knowing this code will work in any application... I think its easier,
doing it the hard way ;)
Have fun
----- Original Message -----
From: "lightbulb432" <ve...@hotmail.com>
To: <us...@tomcat.apache.org>
Sent: Friday, June 01, 2007 9:01 PM
Subject: Connection Pool and Connections
>
> When using the tomcat-dbcp DataSource, when my web application code gets a
> connection:
>
> myConnection = myDataSource.getConnection();
>
> then executes multiple separate statements
>
> myStatement1 = myConnection.createStatement();
> myStatement1.execute();
>
> myStatement2 = myConnection.createStatement();
> myStatement2.execute();
>
> then close the connection
>
> myConnection.close();
>
> Is it possible that myStatement1 and myStatement2 would be run using
> different physical database connections, or are they absolutely guaranteed
> to be executed using the same connection?
>
> Or is connection pooling only for not actually closing the physical
> database
> connection on myConnection.close(), instead returning it to the connection
> pool?
>
> A different way of asking this is does connection pooling pool connections
> within an application connection (myDataSource.getConnection() and
> myConnection.close()), or between application connections?
>
> If this question doesn't make sense, I can clarify. Thanks a lot.
> --
> View this message in context:
> http://www.nabble.com/Connection-Pool-and-Connections-tf3853952.html#a10918685
> Sent from the Tomcat - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> 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