You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Elli Albek <el...@sustainlane.com> on 2009/11/02 10:08:24 UTC
Re: ConnectionPool question
Here is some idea for you:
First, you have two static methods to get and return the connection inside a
listener class. Those methods do not have anything to do with the listener
(which is an instance), and also and also do not need synchronization.
Synchronizing on getConnection can be a big bottleneck, especially if you
are doing a validation query onBorrow. Having said that, it sounds like this
is the least of your problems now.
I think you can have a solution without changing your code.
Try something like this:
getConnection() static method should get the connection, and add it to a
list that you keep in threadlocal.
recycleConnection() should close the connection and remove the connection
object from thread local.
Add a servlet filter that closes all connections in thread local. The filter
calls next filter, and in a finally block get the connections from thread
local, close all of them, and clear the list in thread local.
This will allow you to keep your legacy code. As far as I remember DBCP has
an option to close the result sets and statements when you close the
connection. If not this will partly work.
This is as close as you can get to having the connections reclaimed
immediately without rewriting your application.
Something like:
public static /* NOT synchronized */ Connection getConnection(){
Connection c = ...// get the connection from JNDI/DBCP
List<Connection> connections = // get or create from thread local
if(connections.contains(c) == false)
// make sure c.equals() works as object reference equality, eg ==
// You may have to read DBCP code for this or add a break point and trace
through the code until you get to equals()
connections.add(c);
if(myLogger.isDebug())
myLogger.debug(“Request consumed “+ list.size() + “ connection(s)”);
return c;
}
public void doFilter(ServletRequest req, ServletResponse res, FilterChain
chain) throws IOException, ServletException {
try{
// handle request
chain.doFilter(req, res);
}finally{
List<Connection> connections = ...// get from thread local
if (connections != null){
for(Connection c: connections){
try{
c.close();
}catch(Throwable t){
myLogger.error(“Error on connection close”, t);
}
}
connections.clear();
}
}
}
public static synchronized void recycleConnection(Connection c) {
List<Connection> connections = ...// get from thread local
connections.remove(c);
try {
c.close();
} catch (Throwable t) {
// DO NOT eat the exception, especially when there are known issues in the
connection management code
myLogger.warn(“Error closing connection”, t);
}
}
I also think that I have seen somewhere a configuration that does the above
for you, but if not its not a lot of code to do yourself. Map the filter to
all the URL mappings that can possibly can open connection, which in the
worse case is /*.
Notice that with the code sample you wrote, a typical application will
consume many connections for one request (possible one per statement), and
worse than that, will not be able to perform transactional writes (since
each write is on a separate connection).
Version 2: Advanced
Keep the actual connection in thread local. You will have one connection per
HTTP request. getConnection() should be something like
public static /* NOT synchronized */ Connection getConnection(){
Connection c = ...// get the connection from thread local
if (c != null)
return c;
Connection c = ...// get the connection from JNDI/DBCP
// put connection in thread local
return c;
}
recycleConnection(){
// empty, connection will be recycled by filter.
}
public void doFilter(ServletRequest req, ServletResponse res, FilterChain
chain) throws IOException, ServletException {
try{
// handle request
chain.doFilter(req, res);
}finally{
List<Connection> connections = ...// get from thread local
if (connections != null){
for(Connection c: connections){
try{
c.close();
}catch(Throwable t){
myLogger.error(“Error on connection
close”, t);
}
}
connections.clear();
}
}
}
public void doFilter(ServletRequest req, ServletResponse res, FilterChain
chain) throws IOException, ServletException {
try{
// handle request
chain.doFilter(req, res);
}finally{
Connection c = ...// get and REMOVE from thread local
if (c != null){
try{
c.close();
}catch(Throwable t){
myLogger.error(“Error on connection close”, t);
}
}
}
}
The filter is the same, but not using lists. Version 2 will give you one
connection per servlet request, which normally I would not do for a well
written DAO code, but in your case you are not really dealing with such
code. You can get large performance boost by having one connection per
request and closing it immediately. Your connection usage will be close to
optimal. It may not be 100% in the transactional sense, but it does not look
like your legacy code is.
E
On Fri, Oct 30, 2009 at 8:38 PM, Josh Gooding <jo...@gmail.com>wrote:
> Chris,
>
> I was looking at that earlier, wondering why it was put in there in the
> first place. It just doesn't fit in. Sometimes you just hate to inherit
> someone else's mess.
>
> While there is another school of thought telling me to re-write the entire
> DAO (which I could be willing to later on) for right now, I want to just
> tweak and get it to work more efficiently on the server. I think this is a
> HUGE improvement over what was there and what actually was going on. Thank
> you sir!
Re: [OT] ConnectionPool question
Posted by Josh Gooding <jo...@gmail.com>.
Yup,
No wonder my shop is in so much trouble. No procedures, no brainstorming,
no project management, no best practices, just a "shoot from the hip,
Git-R-Done" attitude.
I liked that so much it's now hanging in my cube :)
- Josh
On Wed, Nov 11, 2009 at 3:33 PM, Caldarale, Charles R <
Chuck.Caldarale@unisys.com> wrote:
> > From: Josh Gooding [mailto:josh.gooding@gmail.com]
> > Subject: Re: [OT] ConnectionPool question
> >
> > Not only did I get yelled at for having the audacity to write up
> > procedures for the developers on my team
>
> "But we've always done it that way..."
>
> "A long habit of not thinking a thing wrong, gives it a superficial
> appearance of being right."
> - Thomas Paine, 1776
>
> - Chuck
>
>
> THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY
> MATERIAL and is thus for use only by the intended recipient. If you received
> this in error, please contact the sender and delete the e-mail and its
> attachments from all computers.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
RE: [OT] ConnectionPool question
Posted by "Propes, Barry L " <ba...@citi.com>.
That's good stuff, Chuck - and sadly, so prevalent among many places as the thought process.
-----Original Message-----
From: Caldarale, Charles R [mailto:Chuck.Caldarale@unisys.com]
Sent: Wednesday, November 11, 2009 2:34 PM
To: Tomcat Users List
Subject: RE: [OT] ConnectionPool question
> From: Josh Gooding [mailto:josh.gooding@gmail.com]
> Subject: Re: [OT] ConnectionPool question
>
> Not only did I get yelled at for having the audacity to write up
> procedures for the developers on my team
"But we've always done it that way..."
"A long habit of not thinking a thing wrong, gives it a superficial appearance of being right."
- Thomas Paine, 1776
- Chuck
THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL and is thus for use only by the intended recipient. If you received this in error, please contact the sender and delete the e-mail and its attachments from all computers.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: [OT] ConnectionPool question
Posted by "Caldarale, Charles R" <Ch...@unisys.com>.
> From: Josh Gooding [mailto:josh.gooding@gmail.com]
> Subject: Re: [OT] ConnectionPool question
>
> Not only did I get yelled at for having the audacity to write up
> procedures for the developers on my team
"But we've always done it that way..."
"A long habit of not thinking a thing wrong, gives it a superficial appearance of being right."
- Thomas Paine, 1776
- Chuck
THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL and is thus for use only by the intended recipient. If you received this in error, please contact the sender and delete the e-mail and its attachments from all computers.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: [OT] ConnectionPool question
Posted by Josh Gooding <jo...@gmail.com>.
Chris,
I AM going to use Elli's suggestion. It's going to take some time, but I
don't have to worry, once it's done, it'll be done correctly.
Not only did I get yelled at for having the audacity to write up procedures
for the developers on my team (assuming I ever get a another team), but I
told my company that the procedures I wrote up needed to be used on ALL dev
teams. I thought 99% of it was common sense, but I guess not.
On Tue, Nov 10, 2009 at 3:40 PM, Christopher Schultz <
chris@christopherschultz.net> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Josh,
>
> On 11/10/2009 9:18 AM, Josh Gooding wrote:
> > the reason there are multiple recycleConnection methods is because if a
> RS
> > is passed to the front presentation layer, I have to have a way to close
> it,
> > I have to be able to get the statement and connection somehow, and I
> don't
> > want that in my code.
>
> I was assuming that you were going to use Elli's suggestion to use a
> ThreadLocal to capture any allocated Connection objects, and just close
> everything at the end of the request processing.
>
> If you're going to do that, you can be lazy about it and just always
> close everything.
>
> >>From everything that everyone here has said, to semi fix this right:
> >
> > 1 - Each method that calls the DB for a RS needs to get it's own
> connection
> > and close the resources itself (keeping it in the same scope)
>
> This is, of course, recommended technique.
>
> > 2 - The CP wrapper that is here, CAN be used, but could end up causing
> more
> > problems in the long run.
>
> Well, yes and no. "Yes" in the sense that you shouldn't encourage the
> kind of coding behavior that's been exhibited in the past: by writing
> such a wrapper and clean-up code, you (might) encourage laziness and
> make the problem worse rather than better. "No" in the sense that I
> don't believe this solution is actually going to exacerbate the actual
> problem itself.
>
> Good luck,
> - -chris
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkr5z7AACgkQ9CaO5/Lv0PCM5ACfdA3MPnJ/YEeUwIeuNjh9akH7
> 2X4AoKyfvWVdOv59FJtxMrw4nyLxsrk9
> =WWP3
> -----END PGP SIGNATURE-----
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: [OT] ConnectionPool question
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Josh,
On 11/10/2009 9:18 AM, Josh Gooding wrote:
> the reason there are multiple recycleConnection methods is because if a RS
> is passed to the front presentation layer, I have to have a way to close it,
> I have to be able to get the statement and connection somehow, and I don't
> want that in my code.
I was assuming that you were going to use Elli's suggestion to use a
ThreadLocal to capture any allocated Connection objects, and just close
everything at the end of the request processing.
If you're going to do that, you can be lazy about it and just always
close everything.
>>>From everything that everyone here has said, to semi fix this right:
>
> 1 - Each method that calls the DB for a RS needs to get it's own connection
> and close the resources itself (keeping it in the same scope)
This is, of course, recommended technique.
> 2 - The CP wrapper that is here, CAN be used, but could end up causing more
> problems in the long run.
Well, yes and no. "Yes" in the sense that you shouldn't encourage the
kind of coding behavior that's been exhibited in the past: by writing
such a wrapper and clean-up code, you (might) encourage laziness and
make the problem worse rather than better. "No" in the sense that I
don't believe this solution is actually going to exacerbate the actual
problem itself.
Good luck,
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkr5z7AACgkQ9CaO5/Lv0PCM5ACfdA3MPnJ/YEeUwIeuNjh9akH7
2X4AoKyfvWVdOv59FJtxMrw4nyLxsrk9
=WWP3
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: [OT] ConnectionPool question
Posted by Josh Gooding <jo...@gmail.com>.
I'm glad I amuse all of you.... :P
Chris,
the reason there are multiple recycleConnection methods is because if a RS
is passed to the front presentation layer, I have to have a way to close it,
I have to be able to get the statement and connection somehow, and I don't
want that in my code.
>From everything that everyone here has said, to semi fix this right:
1 - Each method that calls the DB for a RS needs to get it's own connection
and close the resources itself (keeping it in the same scope)
2 - The CP wrapper that is here, CAN be used, but could end up causing more
problems in the long run.
Hey again, thanks for everything you all have said. I feel slightly more
confident now in fixing this conundrum. :)
- Josh
On Sat, Nov 7, 2009 at 3:17 AM, Elli Albek <el...@sustainlane.com> wrote:
> Chris, as long as we are having fun its all good. I think this
> question is something many developers ask. I have seen it here and in
> other forums.
>
> You are correct, everything is wrapped. The connection wrapper spawns
> statment wrappers which spawn result set wrappers, and everything is
> tracked in parent child relationships.
>
> E
>
> On Fri, Nov 6, 2009 at 5:54 PM, Christopher Schultz
> <ch...@christopherschultz.net> wrote:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > Elli,
> >
> > Okay, I think we've hijacked Josh's thread enough to at least mark it as
> > off topic. :)
> >
> > On 11/5/2009 10:37 PM, Elli Albek wrote:
> >> Christopher Schultz wrote:
> >>> Pooled connections are almost certainly not behaving this way. This has
> >>> been discussed at least twice in the last week or two, and, I believe,
> >>> already once in this thread.
> >>
> >> This is certainly not the case for my DBCP library (1.2.2). I just
> >> stepped through the source code: Calling connection.close() is closing
> >> result sets and statements.
> >
> > I'm not sure how calling Connection.close (where the Connection object
> > is one from DBCP) would possibly be able to close ResultSet and
> > Statement objects created by the underlying connection.
> >
> > Unless, I suppose, they decided to wrap /everything/ and register all
> > the ResultSet and Statement objects handed-out by the underlying
> > Connection for the purposes of (possibly) closing them later. I should
> > really read the source :)
> >
> >> Below is the DBCP code that keeps track of open statements/result sets
> >> and closes them when the connection is closed (BDCP 1.2.2).
> >
> > Yup: it looks like they do wrap/register everything and then close it
> > for you. It's nice that they followed the spec, here :)
> >
> > - -chris
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: [OT] ConnectionPool question
Posted by Elli Albek <el...@sustainlane.com>.
Chris, as long as we are having fun its all good. I think this
question is something many developers ask. I have seen it here and in
other forums.
You are correct, everything is wrapped. The connection wrapper spawns
statment wrappers which spawn result set wrappers, and everything is
tracked in parent child relationships.
E
On Fri, Nov 6, 2009 at 5:54 PM, Christopher Schultz
<ch...@christopherschultz.net> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Elli,
>
> Okay, I think we've hijacked Josh's thread enough to at least mark it as
> off topic. :)
>
> On 11/5/2009 10:37 PM, Elli Albek wrote:
>> Christopher Schultz wrote:
>>> Pooled connections are almost certainly not behaving this way. This has
>>> been discussed at least twice in the last week or two, and, I believe,
>>> already once in this thread.
>>
>> This is certainly not the case for my DBCP library (1.2.2). I just
>> stepped through the source code: Calling connection.close() is closing
>> result sets and statements.
>
> I'm not sure how calling Connection.close (where the Connection object
> is one from DBCP) would possibly be able to close ResultSet and
> Statement objects created by the underlying connection.
>
> Unless, I suppose, they decided to wrap /everything/ and register all
> the ResultSet and Statement objects handed-out by the underlying
> Connection for the purposes of (possibly) closing them later. I should
> really read the source :)
>
>> Below is the DBCP code that keeps track of open statements/result sets
>> and closes them when the connection is closed (BDCP 1.2.2).
>
> Yup: it looks like they do wrap/register everything and then close it
> for you. It's nice that they followed the spec, here :)
>
> - -chris
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: ConnectionPool question
Posted by Elli Albek <el...@sustainlane.com>.
Hi Josh, you are keeping this thread more and more interesting :)
This seems like a complex case. Passing open result sets directly to
pages makes things little more difficult, since the database code is
not handled in one place. Closing the connection via the result set
can lead to some problems, but it will indeed close the connection.
One possible problem is that the same connection was used for creating
two result sets. On the page you close the connection via the first
result set, this forces all open result sets to close, including the
second one which you still need to use.
In a typical Java application developers use doe design pattern, such
as MVC to separate the back end from the front end. The back end java
classes do the database queries (the model), and pass the data to the
page as java objects and not as live database objects. This makes the
pages simpler and without control flow complexity that open resources
cause (such as what you do when the database code throws errors and
the page is half written to the browser).
If all you have is the result set, you can either use it to close the
connection, or switch to the thread local system where you rely on the
thread local + filter combination to do all the resource management.
If you have many database queries to do, use the same connection. When
you get the strings and integers, close the result sets and
statements, but try to use the connection for as long as possible. In
our (home built) framework, this is done automatically. This is a
typical pattern, where server code has means to start and close a
connection implicitly without repeating the same code everywhere. Some
frameworks do it through annotations. How about something like this:
public Something getSomethingFromDatabase(Connection c) throws SQLException{
PreparedStatement s = c.prepareStatement("sql");
try{
ResultSet rs = s.executeQuery();
try{
if (rs.next() == false)
return null;
int someNumber = rs.getInt(1);
String someString = rs.getString(2);
return new Something(someNumber, someString);
}finally{
rs.close();
}
}finally{
s.close();
}
// connection left open
}
See this page for more ideas:
http://commons.apache.org/dbutils/examples.html
Simple rule: A method closes what it opened, and does not close what
it got from the outside. Keep this simple rule and your code will be
easy to follow and contain less errors.
But you also have to think what you try to achieve. Do you want to get
it to work, or go you want to make it solid. If it’s the first, then
most of this discussion is not relevant, the static getConnection and
filter close() solve it in a very simple and performing way without
touching the rest of the code. You can leak all over the place, it
will not make much difference. One connection, always closed. If it’s
the latter, then start looking at simple java database frameworks and
design patterns. That code looks like something that needs to be
rewritten rather than patched.
Getting the connection via the static method is fine. The addition of
thread local can also be easily done in the static method. What the
method does is get the data source and use it. If you want to use the
datasource directly, you will end up writing that code a few times.
The static method also gives you a central place to fix bugs and
control the system. It is the way the code is currently written, so
why change. It also gives you a place to add some extra logic, such as
getReadConnection() and getWriteConnection(), easy change.
The real solution will be to switch to something nicer. Example is a
framework that will give you an implicit connection via annotation on
a method, but this is more of a rewrite project.
It is not common to pass open result sets from the back end (model) to
the front end (view) and expect the view to close things correctly.
This gives you less ability to handle problems. For example exception
handling can be quite complex on the page. A page can get an error
from the DB layer when half of the page is written and flushed to the
browser. Now go figure out what to do.
On Fri, Nov 6, 2009 at 6:31 AM, Josh Gooding <jo...@gmail.com> wrote:
> So with this being said, and I have to ask now the following questions:
>
> 1 - with statements that are returned directly (return
> ConnectionPool.getConnection().prepareStatement(sqlStatement);) how do I
> close the connection? Since there are no finally blocks to close the
> connection in the method on the backend, would I do this in the presentation
> layer, and just do a rs.getStatement().getConnection().close();? (or
> something like that?)
>
> 2 - Should each of my methods that call the DB and extract a string,
> integer, etc, have the "getConnection" in that particular method grab the
> info and then close the connection in the same method?
>
> 3 - Since I fixed / updated / and realized now it's a bad idea to write a
> wrapper for the CP, what do I do to get a connection? Since I'll not be
> using the CP Wrapper anymore. Would it be importing the datasource class
> and doing a ds.getConnection() right there in each method?
>
> 4- For my dumb curiosity what problems can it lead to?
>
> - Josh
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: ConnectionPool question
Posted by Josh Gooding <jo...@gmail.com>.
So with this being said, and I have to ask now the following questions:
1 - with statements that are returned directly (return
ConnectionPool.getConnection().prepareStatement(sqlStatement);) how do I
close the connection? Since there are no finally blocks to close the
connection in the method on the backend, would I do this in the presentation
layer, and just do a rs.getStatement().getConnection().close();? (or
something like that?)
2 - Should each of my methods that call the DB and extract a string,
integer, etc, have the "getConnection" in that particular method grab the
info and then close the connection in the same method?
3 - Since I fixed / updated / and realized now it's a bad idea to write a
wrapper for the CP, what do I do to get a connection? Since I'll not be
using the CP Wrapper anymore. Would it be importing the datasource class
and doing a ds.getConnection() right there in each method?
4- For my dumb curiosity what problems can it lead to?
- Josh
On Thu, Nov 5, 2009 at 10:37 PM, Elli Albek <el...@sustainlane.com> wrote:
> Hi,
>
> > Elli,
> >
> > On 11/4/2009 7:01 PM, Elli Albek wrote:
> >> I also
> >> remember that closing a connection closes statements and result sets,
> but it
> >> has been a while since I read the source.
> >
> > Pooled connections are almost certainly not behaving this way. This has
> > been discussed at least twice in the last week or two, and, I believe,
> > already once in this thread.
> >
> > - -chris
>
> This is certainly not the case for my DBCP library (1.2.2). I just
> stepped through the source code: Calling connection.close() is closing
> result sets and statements. In addition I commented the code that
> closes statements and result sets in our framework to create a leak,
> and DBCP closed everything. Relevant DBCP Code snippets below.
>
> Josh:
> You are correct, wrapping connections, statements and datasources is
> not a good idea. It is complicated code and can lead to many problems.
> Fortunately for you, you don’t have to do that. You can recklessly
> leave statements and result sets open, and as long as you call
> connectiohn.close() at the end DBCP will do all the work for you. You
> can verify this by downloading the source code, and putting a break
> point just before you call close.
>
> I am telling you this based on experience. We do wrap everything for
> logging purpose (data source, connection, statement). Doing it was far
> from trivial, even using the DBCP ready-to-use wrapper classes
> (DelegatingConnection, DelagatingStatement…).
>
> Of course is it good to follow best practices and close things as soon
> as possible in finally blocks. But in your case, it may just be an
> unnecessary intermediary step that you can skip. Maybe you can start
> with a brute force filter and rely on the good fellows that wrote DBCP
> to clean up the mess. Then go to a better solution, where you use a
> framework that does most of this work for you. There are too many java
> database frameworks to list, some of them are heavy, some are minimal
> right above the JDBC layer, and still provide automatic closing of
> resources.
>
> Below is the DBCP code that keeps track of open statements/result sets
> and closes them when the connection is closed (BDCP 1.2.2).
>
> PollableConnectionFactory:
> public void passivateObject(Object obj) throws Exception {
> ...
> if(obj instanceof DelegatingConnection) {
> ((DelegatingConnection)obj).passivate();
> }
> }
>
> DelegatingConnection (called via subclass PollableConnection):
>
> protected void passivate() throws SQLException {
> try {
> // The JDBC spec requires that a Connection close any open
> // Statement's when it is closed.
> List statements = getTrace();
> if( statements != null) {
> Statement[] set = new Statement[statements.size()];
> statements.toArray(set);
> for (int i = 0; i < set.length; i++) {
> set[i].close();
> }
> clearTrace();
> }
> setLastUsed(0);
> if(_conn instanceof DelegatingConnection) {
> ((DelegatingConnection)_conn).passivate();
> }
> }
> finally {
> _closed = true;
> }
> }
>
> Delegating statement:
> public void close() throws SQLException {
> try {
> try {
> if (_conn != null) {
> _conn.removeTrace(this);
> _conn = null;
> }
>
> // The JDBC spec requires that a statment close any open
> // ResultSet's when it is closed.
> // FIXME The PreparedStatement we're wrapping should
> handle this for us.
> // See bug 17301 for what could happen when ResultSets
> are closed twice.
> List resultSets = getTrace();
> if( resultSets != null) {
> ResultSet[] set = (ResultSet[])
> resultSets.toArray(new ResultSet[resultSets.size()]);
> for (int i = 0; i < set.length; i++) {
> set[i].close();
> }
> clearTrace();
> }
>
> _stmt.close();
> }
> catch (SQLException e) {
> handleException(e);
> }
> }
> finally {
> _closed = true;
> }
> }
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: [OT] ConnectionPool question
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Elli,
Okay, I think we've hijacked Josh's thread enough to at least mark it as
off topic. :)
On 11/5/2009 10:37 PM, Elli Albek wrote:
> Christopher Schultz wrote:
>> Pooled connections are almost certainly not behaving this way. This has
>> been discussed at least twice in the last week or two, and, I believe,
>> already once in this thread.
>
> This is certainly not the case for my DBCP library (1.2.2). I just
> stepped through the source code: Calling connection.close() is closing
> result sets and statements.
I'm not sure how calling Connection.close (where the Connection object
is one from DBCP) would possibly be able to close ResultSet and
Statement objects created by the underlying connection.
Unless, I suppose, they decided to wrap /everything/ and register all
the ResultSet and Statement objects handed-out by the underlying
Connection for the purposes of (possibly) closing them later. I should
really read the source :)
> Below is the DBCP code that keeps track of open statements/result sets
> and closes them when the connection is closed (BDCP 1.2.2).
Yup: it looks like they do wrap/register everything and then close it
for you. It's nice that they followed the spec, here :)
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkr001YACgkQ9CaO5/Lv0PDKfgCgl0DRVzft7OkckYlzs4IsBcYu
wvsAoIRysepHLxAW7wBznL0vfaJhy/ll
=tTmF
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: ConnectionPool question
Posted by Elli Albek <el...@sustainlane.com>.
Hi,
> Elli,
>
> On 11/4/2009 7:01 PM, Elli Albek wrote:
>> I also
>> remember that closing a connection closes statements and result sets, but it
>> has been a while since I read the source.
>
> Pooled connections are almost certainly not behaving this way. This has
> been discussed at least twice in the last week or two, and, I believe,
> already once in this thread.
>
> - -chris
This is certainly not the case for my DBCP library (1.2.2). I just
stepped through the source code: Calling connection.close() is closing
result sets and statements. In addition I commented the code that
closes statements and result sets in our framework to create a leak,
and DBCP closed everything. Relevant DBCP Code snippets below.
Josh:
You are correct, wrapping connections, statements and datasources is
not a good idea. It is complicated code and can lead to many problems.
Fortunately for you, you don’t have to do that. You can recklessly
leave statements and result sets open, and as long as you call
connectiohn.close() at the end DBCP will do all the work for you. You
can verify this by downloading the source code, and putting a break
point just before you call close.
I am telling you this based on experience. We do wrap everything for
logging purpose (data source, connection, statement). Doing it was far
from trivial, even using the DBCP ready-to-use wrapper classes
(DelegatingConnection, DelagatingStatement…).
Of course is it good to follow best practices and close things as soon
as possible in finally blocks. But in your case, it may just be an
unnecessary intermediary step that you can skip. Maybe you can start
with a brute force filter and rely on the good fellows that wrote DBCP
to clean up the mess. Then go to a better solution, where you use a
framework that does most of this work for you. There are too many java
database frameworks to list, some of them are heavy, some are minimal
right above the JDBC layer, and still provide automatic closing of
resources.
Below is the DBCP code that keeps track of open statements/result sets
and closes them when the connection is closed (BDCP 1.2.2).
PollableConnectionFactory:
public void passivateObject(Object obj) throws Exception {
...
if(obj instanceof DelegatingConnection) {
((DelegatingConnection)obj).passivate();
}
}
DelegatingConnection (called via subclass PollableConnection):
protected void passivate() throws SQLException {
try {
// The JDBC spec requires that a Connection close any open
// Statement's when it is closed.
List statements = getTrace();
if( statements != null) {
Statement[] set = new Statement[statements.size()];
statements.toArray(set);
for (int i = 0; i < set.length; i++) {
set[i].close();
}
clearTrace();
}
setLastUsed(0);
if(_conn instanceof DelegatingConnection) {
((DelegatingConnection)_conn).passivate();
}
}
finally {
_closed = true;
}
}
Delegating statement:
public void close() throws SQLException {
try {
try {
if (_conn != null) {
_conn.removeTrace(this);
_conn = null;
}
// The JDBC spec requires that a statment close any open
// ResultSet's when it is closed.
// FIXME The PreparedStatement we're wrapping should
handle this for us.
// See bug 17301 for what could happen when ResultSets
are closed twice.
List resultSets = getTrace();
if( resultSets != null) {
ResultSet[] set = (ResultSet[])
resultSets.toArray(new ResultSet[resultSets.size()]);
for (int i = 0; i < set.length; i++) {
set[i].close();
}
clearTrace();
}
_stmt.close();
}
catch (SQLException e) {
handleException(e);
}
}
finally {
_closed = true;
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: ConnectionPool question
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Josh,
On 11/5/2009 8:51 PM, Josh Gooding wrote:
> So my ConnectionPool class is here
> http://www.realissuesforrealpeople.com/ConnectionPool.java
The overloaded recycleConnection methods are a bit strange...
Also, this class is written as a ServletContextListener but it doesn't
ever actually react to ServletContext events. The methods are static...
you don't need to do anything except call them directly: no need for the
class to be any kind of listener or anything.
> and my DatabaseTransaction class is here
> http://www.realissuesforrealpeople.com/DatabaseTransaction.java
I think you're doing more than necessary.
finally
{
if (rs != null)
ConnectionPool.recycleConnection(rs);
if (pstmntCommand != null)
ConnectionPool.recycleConnection(pstmntCommand);
if (!conn.isClosed())
conn.close();
}
If you have code like this, you don't need a separate method to recycle
each object: just call close on it directly.
Also, all your recycleConnection methods (though they don't all take
Connection objects) close everything: ResultSet, Statement, and
Connection. Then, the 'finally' block above re-closes everything over
again (twice!).
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkr01nMACgkQ9CaO5/Lv0PDZmgCgtY2+TdYs4UFFxyWTTNMbpApF
9a0AoIEmkVGifK0KXh0IzoB4mEFv//m7
=ErkB
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: ConnectionPool question
Posted by Josh Gooding <jo...@gmail.com>.
Barry, that's a normal console message when using the DataSource. :)
Alright. I have a question. I've finished up the ConnectionPool wrapper
class that I found and is being used. I don't need it to run, but I figured
it's there why not modify it to handle EVERYTHING dealing with connections.
While I have a getConnection(), I have also created many recycleConnection
methods that close abandoned Statements, Connections, ResultSets, and
DatabaseMetaData's.
So my ConnectionPool class is here
http://www.realissuesforrealpeople.com/ConnectionPool.java
and my DatabaseTransaction class is here
http://www.realissuesforrealpeople.com/DatabaseTransaction.java - Thanks for
the template for the Transaction Mr. Schultz btw.
I haven't worked on the filter as of yet, however, I am being told that the
ConnectionPool wrapper is a horrible idea and that I will end up with
problems with it. As well as the DatabaseTransaction class. Can anyone
forsee any issues with either that I am not forseeing? My ability to use
the force is broken at the moment. I"m looking at the filter as of right
now. I"ve never implemented one however.
On Thu, Nov 5, 2009 at 4:03 PM, Propes, Barry L <ba...@citi.com>wrote:
> Oh yeah....good point..I likely don't have many (I have a few) DB errors
> printing tighto the console.
>
> Fortunately, I've shored that area up greatly.
>
>
>
> -----Original Message-----
> From: Caldarale, Charles R [mailto:Chuck.Caldarale@unisys.com]
> Sent: Thursday, November 05, 2009 2:52 PM
> To: Tomcat Users List
> Subject: RE: ConnectionPool question
>
> > From: Propes, Barry L [mailto:barry.l.propes@citi.com]
> > Subject: RE: ConnectionPool question
> >
> > When I FIRST make a DB query through one of my JSPs/servlets, I get
> > this msg printed to the console.
> >
> > AbandonedObjectPool is used
> > (org.apache.commons.dbcp.AbandonedObjectPool@b32627)
> > LogAbandoned: true
> > RemoveAbandoned: true
> > RemoveAbandonedTimeout: 30
>
> The above message is displayed by the constructor for the pool, directly to
> System.out, when the DataSource is being instantiated. It simply says that
> your config is using the abandoned pool mechanism, and not an indication
> that you actually have any abandoned connections at this time.
>
> - Chuck
>
>
> THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY
> MATERIAL and is thus for use only by the intended recipient. If you received
> this in error, please contact the sender and delete the e-mail and its
> attachments from all computers.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
RE: ConnectionPool question
Posted by "Propes, Barry L " <ba...@citi.com>.
Oh yeah....good point..I likely don't have many (I have a few) DB errors printing to the console.
Fortunately, I've shored that area up greatly.
-----Original Message-----
From: Caldarale, Charles R [mailto:Chuck.Caldarale@unisys.com]
Sent: Thursday, November 05, 2009 2:52 PM
To: Tomcat Users List
Subject: RE: ConnectionPool question
> From: Propes, Barry L [mailto:barry.l.propes@citi.com]
> Subject: RE: ConnectionPool question
>
> When I FIRST make a DB query through one of my JSPs/servlets, I get
> this msg printed to the console.
>
> AbandonedObjectPool is used
> (org.apache.commons.dbcp.AbandonedObjectPool@b32627)
> LogAbandoned: true
> RemoveAbandoned: true
> RemoveAbandonedTimeout: 30
The above message is displayed by the constructor for the pool, directly to System.out, when the DataSource is being instantiated. It simply says that your config is using the abandoned pool mechanism, and not an indication that you actually have any abandoned connections at this time.
- Chuck
THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL and is thus for use only by the intended recipient. If you received this in error, please contact the sender and delete the e-mail and its attachments from all computers.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: ConnectionPool question
Posted by "Caldarale, Charles R" <Ch...@unisys.com>.
> From: Propes, Barry L [mailto:barry.l.propes@citi.com]
> Subject: RE: ConnectionPool question
>
> When I FIRST make a DB query through one of my JSPs/servlets, I get
> this msg printed to the console.
>
> AbandonedObjectPool is used
> (org.apache.commons.dbcp.AbandonedObjectPool@b32627)
> LogAbandoned: true
> RemoveAbandoned: true
> RemoveAbandonedTimeout: 30
The above message is displayed by the constructor for the pool, directly to System.out, when the DataSource is being instantiated. It simply says that your config is using the abandoned pool mechanism, and not an indication that you actually have any abandoned connections at this time.
- Chuck
THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL and is thus for use only by the intended recipient. If you received this in error, please contact the sender and delete the e-mail and its attachments from all computers.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: ConnectionPool question
Posted by "Propes, Barry L " <ba...@citi.com>.
When I FIRST make a DB query through one of my JSPs/servlets, I get this msg printed to the console.
AbandonedObjectPool is used (org.apache.commons.dbcp.AbandonedObjectPool@b32627)
LogAbandoned: true
RemoveAbandoned: true
RemoveAbandonedTimeout: 30
I don't get it afterwards, possibly insinuating I'm using one connection that get's recycled. Even if I get an exception error later thrown (printed) to the console, it doesn't give me that message again.
-----Original Message-----
From: Elli Albek [mailto:elli@sustainlane.com]
Sent: Wednesday, November 04, 2009 6:02 PM
To: Tomcat Users List
Subject: Re: ConnectionPool question
As far as I remember, "abandoned" is a connection that was not closed. So if you call recycle on a connection it will not generate abandoned message. The messages that you see are from connections that you do not close. I also remember that closing a connection closes statements and result sets, but it has been a while since I read the source.
Give the filter above a shot. It would takes maybe an hour to get running and can solve all your problems in one go. You may experience a major boost to your system performance even comparing to closing every connection (because you will be using a single connection per request as opposed to multiple open/close).
E
On Wed, Nov 4, 2009 at 11:43 AM, Christopher Schultz < chris@christopherschultz.net> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Josh,
>
> On 11/4/2009 12:11 PM, Josh Gooding wrote:
> > type="javax.sql.DataSource"
>
> [snip]
>
> > <res-type>javax.sql.DataSource</res-type>
>
> I believe it is these types that must match, and they do. Don't change
> a thing.
>
> - -chris
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkrx2VUACgkQ9CaO5/Lv0PCvIQCgvuD2fkIQ7iHH+xlT22SdRmnq
> E7YAn0JmNbP22/rm6hwKPchNm1dbbXyj
> =zIOM
> -----END PGP SIGNATURE-----
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: ConnectionPool question
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Elli,
On 11/4/2009 7:01 PM, Elli Albek wrote:
> I also
> remember that closing a connection closes statements and result sets, but it
> has been a while since I read the source.
Pooled connections are almost certainly not behaving this way. This has
been discussed at least twice in the last week or two, and, I believe,
already once in this thread.
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkry9DMACgkQ9CaO5/Lv0PCY5QCfZn8KfVZQOuyv6jC9lORXLMJu
54QAoJk4ECAEc3z9uUHBM3MHKNMbH5eV
=d0Nw
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: ConnectionPool question
Posted by Elli Albek <el...@sustainlane.com>.
As far as I remember, "abandoned" is a connection that was not closed. So if
you call recycle on a connection it will not generate abandoned message. The
messages that you see are from connections that you do not close. I also
remember that closing a connection closes statements and result sets, but it
has been a while since I read the source.
Give the filter above a shot. It would takes maybe an hour to get running
and can solve all your problems in one go. You may experience a major boost
to your system performance even comparing to closing every connection
(because you will be using a single connection per request as opposed to
multiple open/close).
E
On Wed, Nov 4, 2009 at 11:43 AM, Christopher Schultz <
chris@christopherschultz.net> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Josh,
>
> On 11/4/2009 12:11 PM, Josh Gooding wrote:
> > type="javax.sql.DataSource"
>
> [snip]
>
> > <res-type>javax.sql.DataSource</res-type>
>
> I believe it is these types that must match, and they do. Don't change a
> thing.
>
> - -chris
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkrx2VUACgkQ9CaO5/Lv0PCvIQCgvuD2fkIQ7iHH+xlT22SdRmnq
> E7YAn0JmNbP22/rm6hwKPchNm1dbbXyj
> =zIOM
> -----END PGP SIGNATURE-----
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: ConnectionPool question
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Josh,
On 11/4/2009 12:11 PM, Josh Gooding wrote:
> type="javax.sql.DataSource"
[snip]
> <res-type>javax.sql.DataSource</res-type>
I believe it is these types that must match, and they do. Don't change a
thing.
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkrx2VUACgkQ9CaO5/Lv0PCvIQCgvuD2fkIQ7iHH+xlT22SdRmnq
E7YAn0JmNbP22/rm6hwKPchNm1dbbXyj
=zIOM
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: ConnectionPool question
Posted by Josh Gooding <jo...@gmail.com>.
Chris,
I would imagine that you are correct, unless I store the RSMD in anther
object (like a Map or AL).
Now that I have started closing my resources something strange has actually
happened now. I've hit the connectionpool limit faster.
I was digging and realized that I changed my context.xml file for the new
DataSourceFactory, but there is a watched resource in my web.xml that is
still pointing to javax.sql.DataSource. should this be changed to the
tomcat's DataSourceFactory as well?
Here's the snippet from the context.xml:
<Resource
name="jdbc/RealmDB"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
username="root"
password="password"
driverClassName="com.mysql.jdbc.Driver"
Here's from the web.xml:
<resource-ref>
<res-type>javax.sql.DataSource</res-type>
<res-ref-name>jdbc/RealmDB</res-ref-name>
<res-auth>Container</res-auth>
</resource-ref>
Do I need to change the <res-type> to the tomcat's DataSource?
On Wed, Nov 4, 2009 at 11:47 AM, Christopher Schultz <
chris@christopherschultz.net> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Josh,
>
> On 11/3/2009 3:24 PM, Josh Gooding wrote:
> > If I close the RS, can I still use the MD?
>
> I see you've already answered this, but note the error is that the
> Connection is closed, not the ResultSet.
>
> I would expect that ResultSetMetaData requires that the ResultSet itself
> still be "open" in order to use it.
>
> - -chris
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkrxsBUACgkQ9CaO5/Lv0PA9FgCgoK27dFtvbyyF4A6M/LrxviWX
> OCoAn0Uxn21O6woqtRZMbZKGOcLlHpQS
> =WlcR
> -----END PGP SIGNATURE-----
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: ConnectionPool question
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Josh,
On 11/3/2009 3:24 PM, Josh Gooding wrote:
> If I close the RS, can I still use the MD?
I see you've already answered this, but note the error is that the
Connection is closed, not the ResultSet.
I would expect that ResultSetMetaData requires that the ResultSet itself
still be "open" in order to use it.
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkrxsBUACgkQ9CaO5/Lv0PA9FgCgoK27dFtvbyyF4A6M/LrxviWX
OCoAn0Uxn21O6woqtRZMbZKGOcLlHpQS
=WlcR
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: ConnectionPool question
Posted by ramzi khlil <ra...@gmail.com>.
Yes, It releases the connection back to the pool.
On Wed, Nov 4, 2009 at 9:43 AM, Carsten Pohl <po...@tyntec.com> wrote:
> Hi,
>
> if you close the connection, it will be recycled. So, close() does not
> really close, but releases the connection.
>
> Regards,
> Carsten Pohl
> ----- Original Message -----
> From: "Josh Gooding" <jo...@gmail.com>
> To: "Tomcat Users List" <us...@tomcat.apache.org>
> Sent: Wednesday, 4 November, 2009 14:56:20 GMT +01:00 Amsterdam / Berlin /
> Bern / Rome / Stockholm / Vienna
> Subject: Re: ConnectionPool question
>
> HOLY MOLY I am getting a TON of abandoned connection warnings now..... I
> see I have logAbandoned="true". My Catalina log grew fast! Now here is a
> question, everytime I recycle a connection (close RS, statement, and the
> connection) does it place it back into the pool or is that what the
> abandoned connection messages are for letting me know they were abandoned
> and put back into the pool?
>
> On Tue, Nov 3, 2009 at 4:06 PM, Josh Gooding <jo...@gmail.com>
> wrote:
>
> > nevermind. I get:
> >
> > javax.servlet.ServletException:
> com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
> >
> >
> > No operations allowed after connection closed.
> >
> > Guess that answers my question.
> >
> >
> > On Tue, Nov 3, 2009 at 3:24 PM, Josh Gooding <josh.gooding@gmail.com
> >wrote:
> >
> >> If I close the RS, can I still use the MD?
> >>
> >>
> >> On Tue, Nov 3, 2009 at 3:13 PM, Elli Albek <el...@sustainlane.com>
> wrote:
> >>
> >>> No, you do not need to close the XXXMetaData classes.
> >>>
> >>> E
> >>>
> >>> On Tue, Nov 3, 2009 at 12:02 PM, Josh Gooding <josh.gooding@gmail.com
> >>> >wrote:
> >>>
> >>> > One more question on bleeding resources. When closing RS / statement
> /
> >>> > connections. Do I have to do anything with the MetaData if I got
> that
> >>> as
> >>> > well? (I.E Do I explicitly have to close the metadata as well?)
> >>> >
> >>> > Josh
> >>> >
> >>> > On Tue, Nov 3, 2009 at 2:01 PM, Josh Gooding <josh.gooding@gmail.com
> >
> >>> > wrote:
> >>> >
> >>> > > Elle,
> >>> > >
> >>> > > I am going to dig into this code and check it out. I want to know
> >>> more
> >>> > > about how to use threadlocal and filters. (Sorry I'm not as
> >>> experienced
> >>> > in
> >>> > > Tomcat as some for you gurus here).
> >>> > >
> >>> > > The code looks promising and I like the 2nd option due to the fact
> >>> that
> >>> > > each HTTP req. only has one connection (which should drop the
> >>> overhead
> >>> > > immensely) however for right now, I just want to fix the bleeding
> >>> issue
> >>> > > (which it seems that I have caught a good portion of them), so I'll
> >>> use
> >>> > my
> >>> > > legacy code, but during a "minor" code release, I can definitely
> look
> >>> > into
> >>> > > rolling this out. I am getting a ton of "abandoned" connection
> >>> warnings
> >>> > in
> >>> > > the console window, so I need to find out where these are coming
> from
> >>> > now.
> >>> > >
> >>> > > I don't know where to begin thanking you guys but thank you. I've
> >>> gotten
> >>> > > more mentoring here on this listing than I have in 2 years at my
> >>> current
> >>> > > employer. Thank you all again.
> >>> > >
> >>> > > - Josh
> >>> > >
> >>> > >
> >>> > > On Mon, Nov 2, 2009 at 3:40 PM, Christopher Schultz <
> >>> > > chris@christopherschultz.net> wrote:
> >>> > >
> >>> > >> -----BEGIN PGP SIGNED MESSAGE-----
> >>> > >> Hash: SHA1
> >>> > >>
> >>> > >> Elli,
> >>> > >>
> >>> > >> On 11/2/2009 4:08 AM, Elli Albek wrote:
> >>> > >> > I think you can have a solution without changing your code.
> >>> > >> >
> >>> > >> > Try something like this:
> >>> > >> >
> >>> > >> > getConnection() static method should get the connection, and add
> >>> it to
> >>> > a
> >>> > >> > list that you keep in threadlocal.
> >>> > >> >
> >>> > >> > recycleConnection() should close the connection and remove the
> >>> > >> connection
> >>> > >> > object from thread local.
> >>> > >> >
> >>> > >> > Add a servlet filter that closes all connections in thread
> local.
> >>> The
> >>> > >> filter
> >>> > >> > calls next filter, and in a finally block get the connections
> from
> >>> > >> thread
> >>> > >> > local, close all of them, and clear the list in thread local.
> >>> > >>
> >>> > >> This is a horrible, nasty hack and it's entirely brilliant!
> >>> > >>
> >>> > >> I would change Elli's implementation just slightly, and actually
> >>> write
> >>> > >> your own DataSource implementation that piggybacks on another one.
> >>> > >> Basically, you just wrap the DataSource that Tomcat provides
> either
> >>> by:
> >>> > >>
> >>> > >> a. Using JNDI to look-up the Tomcat-created JNDI DataSource and
> just
> >>> > >> writing the plumbing code to pass everything through
> >>> > >> b. Actually subclass the DataSource class(es) provided by Tomcat
> and
> >>> > >> use /those/ in your <Resource> configuration.
> >>> > >>
> >>> > >> I would also not make any of this static... there's just no reason
> >>> to do
> >>> > >> so, especially if your DataSource object is in the JNDI context.
> >>> > >>
> >>> > >> Although the /real/ solution is to fix the code, I really like
> this
> >>> > >> solution for a couple of reasons:
> >>> > >>
> >>> > >> 1. It requires no wrapping of Connection, Statement, etc. objects
> >>> > >> (which is entirely miserable if you've ever had to do it)
> >>> > >> 2. It requires no changes to your code whatsoever (if you use my
> >>> > >> DataSource-wrapping suggestion above)
> >>> > >> 3. You won't end up closing your connection, statement, result
> set,
> >>> etc.
> >>> > >> too early because your code has completed execution (unless you
> >>> > >> are using JDBC resources across requests, which is another
> story)
> >>> > >>
> >>> > >> What this won't help, unfortunately is:
> >>> > >>
> >>> > >> * Closing your ResultSet and Statement objects (though this can be
> >>> > >> solved by wrapping the Connection, Statement, etc. objects
> handed-
> >>> > >> out by your DataSource. Yes, it's miserable.)
> >>> > >>
> >>> > >> > This will allow you to keep your legacy code. As far as I
> remember
> >>> > DBCP
> >>> > >> has
> >>> > >> > an option to close the result sets and statements when you close
> >>> the
> >>> > >> > connection. If not this will partly work.
> >>> > >>
> >>> > >> I don't believe commons-dbcp has this capability at all. I'm
> willing
> >>> to
> >>> > >> read any documentation to the contrary, though.
> >>> > >>
> >>> > >> > Version 2: Advanced
> >>> > >> >
> >>> > >> > Keep the actual connection in thread local. You will have one
> >>> > connection
> >>> > >> per
> >>> > >> > HTTP request. getConnection() should be something like
> >>> > >> >
> >>> > >> > public static /* NOT synchronized */ Connection getConnection(){
> >>> > >> >
> >>> > >> > Connection c = ...// get the connection from thread local
> >>> > >> >
> >>> > >> > if (c != null)
> >>> > >> >
> >>> > >> > return c;
> >>> > >> >
> >>> > >> > Connection c = ...// get the connection from JNDI/DBCP
> >>> > >> >
> >>> > >> > // put connection in thread local
> >>> > >> >
> >>> > >> > return c;
> >>> > >> >
> >>> > >> > }
> >>> > >>
> >>> > >> I like this technique, too. You just have to decide if it's
> >>> acceptable
> >>> > >> for your webapp to re-use connections. I can't imagine why that
> >>> would be
> >>> > >> a problem, but it's worth considering before you blindly do it.
> This
> >>> > >> optimization can save you from deadlock (though you're killing-off
> >>> > >> connections after 15 seconds anyway) and should significantly
> >>> improve
> >>> > >> the performance of your webapp because you won't be bleeding so
> many
> >>> > >> connections: you're limited to bleeding one connection per request
> >>> > >> instead of potentially dozens.
> >>> > >>
> >>> > >> > recycleConnection(){
> >>> > >> >
> >>> > >> > // empty, connection will be recycled by filter.
> >>> > >> >
> >>> > >> > }
> >>> > >>
> >>> > >> I would actually allow recycleConnection to close the connection,
> >>> and
> >>> > >> have the filter call recycleConnection. That way, as you improve
> >>> your
> >>> > >> webapp's code, the connections will be closed as soon as possible
> >>> > >> instead of waiting until the request is (mostly) finished.
> >>> > >>
> >>> > >> Again, Elli, a great suggestion!
> >>> > >>
> >>> > >> - -chris
> >>> > >> -----BEGIN PGP SIGNATURE-----
> >>> > >> Version: GnuPG v1.4.10 (MingW32)
> >>> > >> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
> >>> > >>
> >>> > >> iEYEARECAAYFAkrvQ8AACgkQ9CaO5/Lv0PDOSACeJfqgaXmrySSKItQHji2K6UzK
> >>> > >> hmsAoKIAhRAgwzI/QN8SPdVGkBbewA2a
> >>> > >> =Mqjn
> >>> > >> -----END PGP SIGNATURE-----
> >>> > >>
> >>> > >>
> >>> ---------------------------------------------------------------------
> >>> > >> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> >>> > >> For additional commands, e-mail: users-help@tomcat.apache.org
> >>> > >>
> >>> > >>
> >>> > >
> >>> >
> >>>
> >>
> >>
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: ConnectionPool question
Posted by Carsten Pohl <po...@tyntec.com>.
Hi,
if you close the connection, it will be recycled. So, close() does not really close, but releases the connection.
Regards,
Carsten Pohl
----- Original Message -----
From: "Josh Gooding" <jo...@gmail.com>
To: "Tomcat Users List" <us...@tomcat.apache.org>
Sent: Wednesday, 4 November, 2009 14:56:20 GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna
Subject: Re: ConnectionPool question
HOLY MOLY I am getting a TON of abandoned connection warnings now..... I
see I have logAbandoned="true". My Catalina log grew fast! Now here is a
question, everytime I recycle a connection (close RS, statement, and the
connection) does it place it back into the pool or is that what the
abandoned connection messages are for letting me know they were abandoned
and put back into the pool?
On Tue, Nov 3, 2009 at 4:06 PM, Josh Gooding <jo...@gmail.com> wrote:
> nevermind. I get:
>
> javax.servlet.ServletException: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
>
>
> No operations allowed after connection closed.
>
> Guess that answers my question.
>
>
> On Tue, Nov 3, 2009 at 3:24 PM, Josh Gooding <jo...@gmail.com>wrote:
>
>> If I close the RS, can I still use the MD?
>>
>>
>> On Tue, Nov 3, 2009 at 3:13 PM, Elli Albek <el...@sustainlane.com> wrote:
>>
>>> No, you do not need to close the XXXMetaData classes.
>>>
>>> E
>>>
>>> On Tue, Nov 3, 2009 at 12:02 PM, Josh Gooding <josh.gooding@gmail.com
>>> >wrote:
>>>
>>> > One more question on bleeding resources. When closing RS / statement /
>>> > connections. Do I have to do anything with the MetaData if I got that
>>> as
>>> > well? (I.E Do I explicitly have to close the metadata as well?)
>>> >
>>> > Josh
>>> >
>>> > On Tue, Nov 3, 2009 at 2:01 PM, Josh Gooding <jo...@gmail.com>
>>> > wrote:
>>> >
>>> > > Elle,
>>> > >
>>> > > I am going to dig into this code and check it out. I want to know
>>> more
>>> > > about how to use threadlocal and filters. (Sorry I'm not as
>>> experienced
>>> > in
>>> > > Tomcat as some for you gurus here).
>>> > >
>>> > > The code looks promising and I like the 2nd option due to the fact
>>> that
>>> > > each HTTP req. only has one connection (which should drop the
>>> overhead
>>> > > immensely) however for right now, I just want to fix the bleeding
>>> issue
>>> > > (which it seems that I have caught a good portion of them), so I'll
>>> use
>>> > my
>>> > > legacy code, but during a "minor" code release, I can definitely look
>>> > into
>>> > > rolling this out. I am getting a ton of "abandoned" connection
>>> warnings
>>> > in
>>> > > the console window, so I need to find out where these are coming from
>>> > now.
>>> > >
>>> > > I don't know where to begin thanking you guys but thank you. I've
>>> gotten
>>> > > more mentoring here on this listing than I have in 2 years at my
>>> current
>>> > > employer. Thank you all again.
>>> > >
>>> > > - Josh
>>> > >
>>> > >
>>> > > On Mon, Nov 2, 2009 at 3:40 PM, Christopher Schultz <
>>> > > chris@christopherschultz.net> wrote:
>>> > >
>>> > >> -----BEGIN PGP SIGNED MESSAGE-----
>>> > >> Hash: SHA1
>>> > >>
>>> > >> Elli,
>>> > >>
>>> > >> On 11/2/2009 4:08 AM, Elli Albek wrote:
>>> > >> > I think you can have a solution without changing your code.
>>> > >> >
>>> > >> > Try something like this:
>>> > >> >
>>> > >> > getConnection() static method should get the connection, and add
>>> it to
>>> > a
>>> > >> > list that you keep in threadlocal.
>>> > >> >
>>> > >> > recycleConnection() should close the connection and remove the
>>> > >> connection
>>> > >> > object from thread local.
>>> > >> >
>>> > >> > Add a servlet filter that closes all connections in thread local.
>>> The
>>> > >> filter
>>> > >> > calls next filter, and in a finally block get the connections from
>>> > >> thread
>>> > >> > local, close all of them, and clear the list in thread local.
>>> > >>
>>> > >> This is a horrible, nasty hack and it's entirely brilliant!
>>> > >>
>>> > >> I would change Elli's implementation just slightly, and actually
>>> write
>>> > >> your own DataSource implementation that piggybacks on another one.
>>> > >> Basically, you just wrap the DataSource that Tomcat provides either
>>> by:
>>> > >>
>>> > >> a. Using JNDI to look-up the Tomcat-created JNDI DataSource and just
>>> > >> writing the plumbing code to pass everything through
>>> > >> b. Actually subclass the DataSource class(es) provided by Tomcat and
>>> > >> use /those/ in your <Resource> configuration.
>>> > >>
>>> > >> I would also not make any of this static... there's just no reason
>>> to do
>>> > >> so, especially if your DataSource object is in the JNDI context.
>>> > >>
>>> > >> Although the /real/ solution is to fix the code, I really like this
>>> > >> solution for a couple of reasons:
>>> > >>
>>> > >> 1. It requires no wrapping of Connection, Statement, etc. objects
>>> > >> (which is entirely miserable if you've ever had to do it)
>>> > >> 2. It requires no changes to your code whatsoever (if you use my
>>> > >> DataSource-wrapping suggestion above)
>>> > >> 3. You won't end up closing your connection, statement, result set,
>>> etc.
>>> > >> too early because your code has completed execution (unless you
>>> > >> are using JDBC resources across requests, which is another story)
>>> > >>
>>> > >> What this won't help, unfortunately is:
>>> > >>
>>> > >> * Closing your ResultSet and Statement objects (though this can be
>>> > >> solved by wrapping the Connection, Statement, etc. objects handed-
>>> > >> out by your DataSource. Yes, it's miserable.)
>>> > >>
>>> > >> > This will allow you to keep your legacy code. As far as I remember
>>> > DBCP
>>> > >> has
>>> > >> > an option to close the result sets and statements when you close
>>> the
>>> > >> > connection. If not this will partly work.
>>> > >>
>>> > >> I don't believe commons-dbcp has this capability at all. I'm willing
>>> to
>>> > >> read any documentation to the contrary, though.
>>> > >>
>>> > >> > Version 2: Advanced
>>> > >> >
>>> > >> > Keep the actual connection in thread local. You will have one
>>> > connection
>>> > >> per
>>> > >> > HTTP request. getConnection() should be something like
>>> > >> >
>>> > >> > public static /* NOT synchronized */ Connection getConnection(){
>>> > >> >
>>> > >> > Connection c = ...// get the connection from thread local
>>> > >> >
>>> > >> > if (c != null)
>>> > >> >
>>> > >> > return c;
>>> > >> >
>>> > >> > Connection c = ...// get the connection from JNDI/DBCP
>>> > >> >
>>> > >> > // put connection in thread local
>>> > >> >
>>> > >> > return c;
>>> > >> >
>>> > >> > }
>>> > >>
>>> > >> I like this technique, too. You just have to decide if it's
>>> acceptable
>>> > >> for your webapp to re-use connections. I can't imagine why that
>>> would be
>>> > >> a problem, but it's worth considering before you blindly do it. This
>>> > >> optimization can save you from deadlock (though you're killing-off
>>> > >> connections after 15 seconds anyway) and should significantly
>>> improve
>>> > >> the performance of your webapp because you won't be bleeding so many
>>> > >> connections: you're limited to bleeding one connection per request
>>> > >> instead of potentially dozens.
>>> > >>
>>> > >> > recycleConnection(){
>>> > >> >
>>> > >> > // empty, connection will be recycled by filter.
>>> > >> >
>>> > >> > }
>>> > >>
>>> > >> I would actually allow recycleConnection to close the connection,
>>> and
>>> > >> have the filter call recycleConnection. That way, as you improve
>>> your
>>> > >> webapp's code, the connections will be closed as soon as possible
>>> > >> instead of waiting until the request is (mostly) finished.
>>> > >>
>>> > >> Again, Elli, a great suggestion!
>>> > >>
>>> > >> - -chris
>>> > >> -----BEGIN PGP SIGNATURE-----
>>> > >> Version: GnuPG v1.4.10 (MingW32)
>>> > >> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>>> > >>
>>> > >> iEYEARECAAYFAkrvQ8AACgkQ9CaO5/Lv0PDOSACeJfqgaXmrySSKItQHji2K6UzK
>>> > >> hmsAoKIAhRAgwzI/QN8SPdVGkBbewA2a
>>> > >> =Mqjn
>>> > >> -----END PGP SIGNATURE-----
>>> > >>
>>> > >>
>>> ---------------------------------------------------------------------
>>> > >> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>>> > >> For additional commands, e-mail: users-help@tomcat.apache.org
>>> > >>
>>> > >>
>>> > >
>>> >
>>>
>>
>>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: ConnectionPool question
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Chuck,
On 11/4/2009 1:17 PM, Caldarale, Charles R wrote:
>> From: Christopher Schultz [mailto:chris@christopherschultz.net]
>> Subject: Re: ConnectionPool question
>>
>> I believe both the real connection and the wrapper are discarded.
>
> Yes; I should have said the real connection is closed and a new real
> one created for the pool.
Not to belabor the point, but I believe the real connection is simply
discarded and not even closed by DBCP.
I can't find much in the way of documentation about how the "abandoned"
features work, and I don't feel like reading source code right now :)
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkrx2P8ACgkQ9CaO5/Lv0PAncACfbZ8qCDWovlPVh+DjqmJQyiaq
aQAAnApF8fWXbRL+728QSVWFdmVtpEki
=NpgC
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: ConnectionPool question
Posted by "Caldarale, Charles R" <Ch...@unisys.com>.
> From: Christopher Schultz [mailto:chris@christopherschultz.net]
> Subject: Re: ConnectionPool question
>
> I believe both the real connection and the wrapper are discarded.
Yes; I should have said the real connection is closed and a new real one created for the pool.
- Chuck
THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL and is thus for use only by the intended recipient. If you received this in error, please contact the sender and delete the e-mail and its attachments from all computers.
Re: ConnectionPool question
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Chuck,
On 11/4/2009 10:06 AM, Caldarale, Charles R wrote:
> If a webapp fails to close a
> connection wrapper and its associated real connection stays idle
> beyond the configured limit, the association between the wrapper and
> real connection is severed, the real connection is returned to the
> pool, and the wrapper is discarded
I believe both the real connection and the wrapper are discarded. It
would be dangerous to place the real connection back into the pool, say,
if it were in the middle of a transaction.
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkrxsIoACgkQ9CaO5/Lv0PAxWQCdEqJSfWXKSi5byN07ubFJR0QR
QhAAoLHdagXCppMFjmGPVv4ReMMepwvN
=uv37
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: ConnectionPool question
Posted by "Caldarale, Charles R" <Ch...@unisys.com>.
> From: Josh Gooding [mailto:josh.gooding@gmail.com]
> Subject: Re: ConnectionPool question
>
> everytime I recycle a connection (close RS, statement, and the
> connection) does it place it back into the pool or is that what the
> abandoned connection messages are for letting me know they were
> abandoned and put back into the pool?
There are actually two sets of connection objects here: the real connection to the DB server, and an associated connection wrapper that's visible to the webapp(s). If a webapp fails to close a connection wrapper and its associated real connection stays idle beyond the configured limit, the association between the wrapper and real connection is severed, the real connection is returned to the pool, and the wrapper is discarded; it's at this time that the log entry is made, containing the stack trace of the point where the wrapper was acquired by the webapp.
When the webapp closes the wrapper, both the real connection and the wrapper are made available for reuse.
- Chuck
THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL and is thus for use only by the intended recipient. If you received this in error, please contact the sender and delete the e-mail and its attachments from all computers.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: ConnectionPool question
Posted by Josh Gooding <jo...@gmail.com>.
HOLY MOLY I am getting a TON of abandoned connection warnings now..... I
see I have logAbandoned="true". My Catalina log grew fast! Now here is a
question, everytime I recycle a connection (close RS, statement, and the
connection) does it place it back into the pool or is that what the
abandoned connection messages are for letting me know they were abandoned
and put back into the pool?
On Tue, Nov 3, 2009 at 4:06 PM, Josh Gooding <jo...@gmail.com> wrote:
> nevermind. I get:
>
> javax.servlet.ServletException: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
>
>
> No operations allowed after connection closed.
>
> Guess that answers my question.
>
>
> On Tue, Nov 3, 2009 at 3:24 PM, Josh Gooding <jo...@gmail.com>wrote:
>
>> If I close the RS, can I still use the MD?
>>
>>
>> On Tue, Nov 3, 2009 at 3:13 PM, Elli Albek <el...@sustainlane.com> wrote:
>>
>>> No, you do not need to close the XXXMetaData classes.
>>>
>>> E
>>>
>>> On Tue, Nov 3, 2009 at 12:02 PM, Josh Gooding <josh.gooding@gmail.com
>>> >wrote:
>>>
>>> > One more question on bleeding resources. When closing RS / statement /
>>> > connections. Do I have to do anything with the MetaData if I got that
>>> as
>>> > well? (I.E Do I explicitly have to close the metadata as well?)
>>> >
>>> > Josh
>>> >
>>> > On Tue, Nov 3, 2009 at 2:01 PM, Josh Gooding <jo...@gmail.com>
>>> > wrote:
>>> >
>>> > > Elle,
>>> > >
>>> > > I am going to dig into this code and check it out. I want to know
>>> more
>>> > > about how to use threadlocal and filters. (Sorry I'm not as
>>> experienced
>>> > in
>>> > > Tomcat as some for you gurus here).
>>> > >
>>> > > The code looks promising and I like the 2nd option due to the fact
>>> that
>>> > > each HTTP req. only has one connection (which should drop the
>>> overhead
>>> > > immensely) however for right now, I just want to fix the bleeding
>>> issue
>>> > > (which it seems that I have caught a good portion of them), so I'll
>>> use
>>> > my
>>> > > legacy code, but during a "minor" code release, I can definitely look
>>> > into
>>> > > rolling this out. I am getting a ton of "abandoned" connection
>>> warnings
>>> > in
>>> > > the console window, so I need to find out where these are coming from
>>> > now.
>>> > >
>>> > > I don't know where to begin thanking you guys but thank you. I've
>>> gotten
>>> > > more mentoring here on this listing than I have in 2 years at my
>>> current
>>> > > employer. Thank you all again.
>>> > >
>>> > > - Josh
>>> > >
>>> > >
>>> > > On Mon, Nov 2, 2009 at 3:40 PM, Christopher Schultz <
>>> > > chris@christopherschultz.net> wrote:
>>> > >
>>> > >> -----BEGIN PGP SIGNED MESSAGE-----
>>> > >> Hash: SHA1
>>> > >>
>>> > >> Elli,
>>> > >>
>>> > >> On 11/2/2009 4:08 AM, Elli Albek wrote:
>>> > >> > I think you can have a solution without changing your code.
>>> > >> >
>>> > >> > Try something like this:
>>> > >> >
>>> > >> > getConnection() static method should get the connection, and add
>>> it to
>>> > a
>>> > >> > list that you keep in threadlocal.
>>> > >> >
>>> > >> > recycleConnection() should close the connection and remove the
>>> > >> connection
>>> > >> > object from thread local.
>>> > >> >
>>> > >> > Add a servlet filter that closes all connections in thread local.
>>> The
>>> > >> filter
>>> > >> > calls next filter, and in a finally block get the connections from
>>> > >> thread
>>> > >> > local, close all of them, and clear the list in thread local.
>>> > >>
>>> > >> This is a horrible, nasty hack and it's entirely brilliant!
>>> > >>
>>> > >> I would change Elli's implementation just slightly, and actually
>>> write
>>> > >> your own DataSource implementation that piggybacks on another one.
>>> > >> Basically, you just wrap the DataSource that Tomcat provides either
>>> by:
>>> > >>
>>> > >> a. Using JNDI to look-up the Tomcat-created JNDI DataSource and just
>>> > >> writing the plumbing code to pass everything through
>>> > >> b. Actually subclass the DataSource class(es) provided by Tomcat and
>>> > >> use /those/ in your <Resource> configuration.
>>> > >>
>>> > >> I would also not make any of this static... there's just no reason
>>> to do
>>> > >> so, especially if your DataSource object is in the JNDI context.
>>> > >>
>>> > >> Although the /real/ solution is to fix the code, I really like this
>>> > >> solution for a couple of reasons:
>>> > >>
>>> > >> 1. It requires no wrapping of Connection, Statement, etc. objects
>>> > >> (which is entirely miserable if you've ever had to do it)
>>> > >> 2. It requires no changes to your code whatsoever (if you use my
>>> > >> DataSource-wrapping suggestion above)
>>> > >> 3. You won't end up closing your connection, statement, result set,
>>> etc.
>>> > >> too early because your code has completed execution (unless you
>>> > >> are using JDBC resources across requests, which is another story)
>>> > >>
>>> > >> What this won't help, unfortunately is:
>>> > >>
>>> > >> * Closing your ResultSet and Statement objects (though this can be
>>> > >> solved by wrapping the Connection, Statement, etc. objects handed-
>>> > >> out by your DataSource. Yes, it's miserable.)
>>> > >>
>>> > >> > This will allow you to keep your legacy code. As far as I remember
>>> > DBCP
>>> > >> has
>>> > >> > an option to close the result sets and statements when you close
>>> the
>>> > >> > connection. If not this will partly work.
>>> > >>
>>> > >> I don't believe commons-dbcp has this capability at all. I'm willing
>>> to
>>> > >> read any documentation to the contrary, though.
>>> > >>
>>> > >> > Version 2: Advanced
>>> > >> >
>>> > >> > Keep the actual connection in thread local. You will have one
>>> > connection
>>> > >> per
>>> > >> > HTTP request. getConnection() should be something like
>>> > >> >
>>> > >> > public static /* NOT synchronized */ Connection getConnection(){
>>> > >> >
>>> > >> > Connection c = ...// get the connection from thread local
>>> > >> >
>>> > >> > if (c != null)
>>> > >> >
>>> > >> > return c;
>>> > >> >
>>> > >> > Connection c = ...// get the connection from JNDI/DBCP
>>> > >> >
>>> > >> > // put connection in thread local
>>> > >> >
>>> > >> > return c;
>>> > >> >
>>> > >> > }
>>> > >>
>>> > >> I like this technique, too. You just have to decide if it's
>>> acceptable
>>> > >> for your webapp to re-use connections. I can't imagine why that
>>> would be
>>> > >> a problem, but it's worth considering before you blindly do it. This
>>> > >> optimization can save you from deadlock (though you're killing-off
>>> > >> connections after 15 seconds anyway) and should significantly
>>> improve
>>> > >> the performance of your webapp because you won't be bleeding so many
>>> > >> connections: you're limited to bleeding one connection per request
>>> > >> instead of potentially dozens.
>>> > >>
>>> > >> > recycleConnection(){
>>> > >> >
>>> > >> > // empty, connection will be recycled by filter.
>>> > >> >
>>> > >> > }
>>> > >>
>>> > >> I would actually allow recycleConnection to close the connection,
>>> and
>>> > >> have the filter call recycleConnection. That way, as you improve
>>> your
>>> > >> webapp's code, the connections will be closed as soon as possible
>>> > >> instead of waiting until the request is (mostly) finished.
>>> > >>
>>> > >> Again, Elli, a great suggestion!
>>> > >>
>>> > >> - -chris
>>> > >> -----BEGIN PGP SIGNATURE-----
>>> > >> Version: GnuPG v1.4.10 (MingW32)
>>> > >> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>>> > >>
>>> > >> iEYEARECAAYFAkrvQ8AACgkQ9CaO5/Lv0PDOSACeJfqgaXmrySSKItQHji2K6UzK
>>> > >> hmsAoKIAhRAgwzI/QN8SPdVGkBbewA2a
>>> > >> =Mqjn
>>> > >> -----END PGP SIGNATURE-----
>>> > >>
>>> > >>
>>> ---------------------------------------------------------------------
>>> > >> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>>> > >> For additional commands, e-mail: users-help@tomcat.apache.org
>>> > >>
>>> > >>
>>> > >
>>> >
>>>
>>
>>
>
Re: ConnectionPool question
Posted by Josh Gooding <jo...@gmail.com>.
nevermind. I get:
javax.servlet.ServletException:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
No operations allowed after connection closed.
Guess that answers my question.
On Tue, Nov 3, 2009 at 3:24 PM, Josh Gooding <jo...@gmail.com> wrote:
> If I close the RS, can I still use the MD?
>
>
> On Tue, Nov 3, 2009 at 3:13 PM, Elli Albek <el...@sustainlane.com> wrote:
>
>> No, you do not need to close the XXXMetaData classes.
>>
>> E
>>
>> On Tue, Nov 3, 2009 at 12:02 PM, Josh Gooding <josh.gooding@gmail.com
>> >wrote:
>>
>> > One more question on bleeding resources. When closing RS / statement /
>> > connections. Do I have to do anything with the MetaData if I got that
>> as
>> > well? (I.E Do I explicitly have to close the metadata as well?)
>> >
>> > Josh
>> >
>> > On Tue, Nov 3, 2009 at 2:01 PM, Josh Gooding <jo...@gmail.com>
>> > wrote:
>> >
>> > > Elle,
>> > >
>> > > I am going to dig into this code and check it out. I want to know
>> more
>> > > about how to use threadlocal and filters. (Sorry I'm not as
>> experienced
>> > in
>> > > Tomcat as some for you gurus here).
>> > >
>> > > The code looks promising and I like the 2nd option due to the fact
>> that
>> > > each HTTP req. only has one connection (which should drop the overhead
>> > > immensely) however for right now, I just want to fix the bleeding
>> issue
>> > > (which it seems that I have caught a good portion of them), so I'll
>> use
>> > my
>> > > legacy code, but during a "minor" code release, I can definitely look
>> > into
>> > > rolling this out. I am getting a ton of "abandoned" connection
>> warnings
>> > in
>> > > the console window, so I need to find out where these are coming from
>> > now.
>> > >
>> > > I don't know where to begin thanking you guys but thank you. I've
>> gotten
>> > > more mentoring here on this listing than I have in 2 years at my
>> current
>> > > employer. Thank you all again.
>> > >
>> > > - Josh
>> > >
>> > >
>> > > On Mon, Nov 2, 2009 at 3:40 PM, Christopher Schultz <
>> > > chris@christopherschultz.net> wrote:
>> > >
>> > >> -----BEGIN PGP SIGNED MESSAGE-----
>> > >> Hash: SHA1
>> > >>
>> > >> Elli,
>> > >>
>> > >> On 11/2/2009 4:08 AM, Elli Albek wrote:
>> > >> > I think you can have a solution without changing your code.
>> > >> >
>> > >> > Try something like this:
>> > >> >
>> > >> > getConnection() static method should get the connection, and add it
>> to
>> > a
>> > >> > list that you keep in threadlocal.
>> > >> >
>> > >> > recycleConnection() should close the connection and remove the
>> > >> connection
>> > >> > object from thread local.
>> > >> >
>> > >> > Add a servlet filter that closes all connections in thread local.
>> The
>> > >> filter
>> > >> > calls next filter, and in a finally block get the connections from
>> > >> thread
>> > >> > local, close all of them, and clear the list in thread local.
>> > >>
>> > >> This is a horrible, nasty hack and it's entirely brilliant!
>> > >>
>> > >> I would change Elli's implementation just slightly, and actually
>> write
>> > >> your own DataSource implementation that piggybacks on another one.
>> > >> Basically, you just wrap the DataSource that Tomcat provides either
>> by:
>> > >>
>> > >> a. Using JNDI to look-up the Tomcat-created JNDI DataSource and just
>> > >> writing the plumbing code to pass everything through
>> > >> b. Actually subclass the DataSource class(es) provided by Tomcat and
>> > >> use /those/ in your <Resource> configuration.
>> > >>
>> > >> I would also not make any of this static... there's just no reason to
>> do
>> > >> so, especially if your DataSource object is in the JNDI context.
>> > >>
>> > >> Although the /real/ solution is to fix the code, I really like this
>> > >> solution for a couple of reasons:
>> > >>
>> > >> 1. It requires no wrapping of Connection, Statement, etc. objects
>> > >> (which is entirely miserable if you've ever had to do it)
>> > >> 2. It requires no changes to your code whatsoever (if you use my
>> > >> DataSource-wrapping suggestion above)
>> > >> 3. You won't end up closing your connection, statement, result set,
>> etc.
>> > >> too early because your code has completed execution (unless you
>> > >> are using JDBC resources across requests, which is another story)
>> > >>
>> > >> What this won't help, unfortunately is:
>> > >>
>> > >> * Closing your ResultSet and Statement objects (though this can be
>> > >> solved by wrapping the Connection, Statement, etc. objects handed-
>> > >> out by your DataSource. Yes, it's miserable.)
>> > >>
>> > >> > This will allow you to keep your legacy code. As far as I remember
>> > DBCP
>> > >> has
>> > >> > an option to close the result sets and statements when you close
>> the
>> > >> > connection. If not this will partly work.
>> > >>
>> > >> I don't believe commons-dbcp has this capability at all. I'm willing
>> to
>> > >> read any documentation to the contrary, though.
>> > >>
>> > >> > Version 2: Advanced
>> > >> >
>> > >> > Keep the actual connection in thread local. You will have one
>> > connection
>> > >> per
>> > >> > HTTP request. getConnection() should be something like
>> > >> >
>> > >> > public static /* NOT synchronized */ Connection getConnection(){
>> > >> >
>> > >> > Connection c = ...// get the connection from thread local
>> > >> >
>> > >> > if (c != null)
>> > >> >
>> > >> > return c;
>> > >> >
>> > >> > Connection c = ...// get the connection from JNDI/DBCP
>> > >> >
>> > >> > // put connection in thread local
>> > >> >
>> > >> > return c;
>> > >> >
>> > >> > }
>> > >>
>> > >> I like this technique, too. You just have to decide if it's
>> acceptable
>> > >> for your webapp to re-use connections. I can't imagine why that would
>> be
>> > >> a problem, but it's worth considering before you blindly do it. This
>> > >> optimization can save you from deadlock (though you're killing-off
>> > >> connections after 15 seconds anyway) and should significantly improve
>> > >> the performance of your webapp because you won't be bleeding so many
>> > >> connections: you're limited to bleeding one connection per request
>> > >> instead of potentially dozens.
>> > >>
>> > >> > recycleConnection(){
>> > >> >
>> > >> > // empty, connection will be recycled by filter.
>> > >> >
>> > >> > }
>> > >>
>> > >> I would actually allow recycleConnection to close the connection, and
>> > >> have the filter call recycleConnection. That way, as you improve your
>> > >> webapp's code, the connections will be closed as soon as possible
>> > >> instead of waiting until the request is (mostly) finished.
>> > >>
>> > >> Again, Elli, a great suggestion!
>> > >>
>> > >> - -chris
>> > >> -----BEGIN PGP SIGNATURE-----
>> > >> Version: GnuPG v1.4.10 (MingW32)
>> > >> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>> > >>
>> > >> iEYEARECAAYFAkrvQ8AACgkQ9CaO5/Lv0PDOSACeJfqgaXmrySSKItQHji2K6UzK
>> > >> hmsAoKIAhRAgwzI/QN8SPdVGkBbewA2a
>> > >> =Mqjn
>> > >> -----END PGP SIGNATURE-----
>> > >>
>> > >> ---------------------------------------------------------------------
>> > >> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>> > >> For additional commands, e-mail: users-help@tomcat.apache.org
>> > >>
>> > >>
>> > >
>> >
>>
>
>
Re: ConnectionPool question
Posted by Josh Gooding <jo...@gmail.com>.
If I close the RS, can I still use the MD?
On Tue, Nov 3, 2009 at 3:13 PM, Elli Albek <el...@sustainlane.com> wrote:
> No, you do not need to close the XXXMetaData classes.
>
> E
>
> On Tue, Nov 3, 2009 at 12:02 PM, Josh Gooding <josh.gooding@gmail.com
> >wrote:
>
> > One more question on bleeding resources. When closing RS / statement /
> > connections. Do I have to do anything with the MetaData if I got that as
> > well? (I.E Do I explicitly have to close the metadata as well?)
> >
> > Josh
> >
> > On Tue, Nov 3, 2009 at 2:01 PM, Josh Gooding <jo...@gmail.com>
> > wrote:
> >
> > > Elle,
> > >
> > > I am going to dig into this code and check it out. I want to know more
> > > about how to use threadlocal and filters. (Sorry I'm not as
> experienced
> > in
> > > Tomcat as some for you gurus here).
> > >
> > > The code looks promising and I like the 2nd option due to the fact that
> > > each HTTP req. only has one connection (which should drop the overhead
> > > immensely) however for right now, I just want to fix the bleeding issue
> > > (which it seems that I have caught a good portion of them), so I'll use
> > my
> > > legacy code, but during a "minor" code release, I can definitely look
> > into
> > > rolling this out. I am getting a ton of "abandoned" connection
> warnings
> > in
> > > the console window, so I need to find out where these are coming from
> > now.
> > >
> > > I don't know where to begin thanking you guys but thank you. I've
> gotten
> > > more mentoring here on this listing than I have in 2 years at my
> current
> > > employer. Thank you all again.
> > >
> > > - Josh
> > >
> > >
> > > On Mon, Nov 2, 2009 at 3:40 PM, Christopher Schultz <
> > > chris@christopherschultz.net> wrote:
> > >
> > >> -----BEGIN PGP SIGNED MESSAGE-----
> > >> Hash: SHA1
> > >>
> > >> Elli,
> > >>
> > >> On 11/2/2009 4:08 AM, Elli Albek wrote:
> > >> > I think you can have a solution without changing your code.
> > >> >
> > >> > Try something like this:
> > >> >
> > >> > getConnection() static method should get the connection, and add it
> to
> > a
> > >> > list that you keep in threadlocal.
> > >> >
> > >> > recycleConnection() should close the connection and remove the
> > >> connection
> > >> > object from thread local.
> > >> >
> > >> > Add a servlet filter that closes all connections in thread local.
> The
> > >> filter
> > >> > calls next filter, and in a finally block get the connections from
> > >> thread
> > >> > local, close all of them, and clear the list in thread local.
> > >>
> > >> This is a horrible, nasty hack and it's entirely brilliant!
> > >>
> > >> I would change Elli's implementation just slightly, and actually write
> > >> your own DataSource implementation that piggybacks on another one.
> > >> Basically, you just wrap the DataSource that Tomcat provides either
> by:
> > >>
> > >> a. Using JNDI to look-up the Tomcat-created JNDI DataSource and just
> > >> writing the plumbing code to pass everything through
> > >> b. Actually subclass the DataSource class(es) provided by Tomcat and
> > >> use /those/ in your <Resource> configuration.
> > >>
> > >> I would also not make any of this static... there's just no reason to
> do
> > >> so, especially if your DataSource object is in the JNDI context.
> > >>
> > >> Although the /real/ solution is to fix the code, I really like this
> > >> solution for a couple of reasons:
> > >>
> > >> 1. It requires no wrapping of Connection, Statement, etc. objects
> > >> (which is entirely miserable if you've ever had to do it)
> > >> 2. It requires no changes to your code whatsoever (if you use my
> > >> DataSource-wrapping suggestion above)
> > >> 3. You won't end up closing your connection, statement, result set,
> etc.
> > >> too early because your code has completed execution (unless you
> > >> are using JDBC resources across requests, which is another story)
> > >>
> > >> What this won't help, unfortunately is:
> > >>
> > >> * Closing your ResultSet and Statement objects (though this can be
> > >> solved by wrapping the Connection, Statement, etc. objects handed-
> > >> out by your DataSource. Yes, it's miserable.)
> > >>
> > >> > This will allow you to keep your legacy code. As far as I remember
> > DBCP
> > >> has
> > >> > an option to close the result sets and statements when you close the
> > >> > connection. If not this will partly work.
> > >>
> > >> I don't believe commons-dbcp has this capability at all. I'm willing
> to
> > >> read any documentation to the contrary, though.
> > >>
> > >> > Version 2: Advanced
> > >> >
> > >> > Keep the actual connection in thread local. You will have one
> > connection
> > >> per
> > >> > HTTP request. getConnection() should be something like
> > >> >
> > >> > public static /* NOT synchronized */ Connection getConnection(){
> > >> >
> > >> > Connection c = ...// get the connection from thread local
> > >> >
> > >> > if (c != null)
> > >> >
> > >> > return c;
> > >> >
> > >> > Connection c = ...// get the connection from JNDI/DBCP
> > >> >
> > >> > // put connection in thread local
> > >> >
> > >> > return c;
> > >> >
> > >> > }
> > >>
> > >> I like this technique, too. You just have to decide if it's acceptable
> > >> for your webapp to re-use connections. I can't imagine why that would
> be
> > >> a problem, but it's worth considering before you blindly do it. This
> > >> optimization can save you from deadlock (though you're killing-off
> > >> connections after 15 seconds anyway) and should significantly improve
> > >> the performance of your webapp because you won't be bleeding so many
> > >> connections: you're limited to bleeding one connection per request
> > >> instead of potentially dozens.
> > >>
> > >> > recycleConnection(){
> > >> >
> > >> > // empty, connection will be recycled by filter.
> > >> >
> > >> > }
> > >>
> > >> I would actually allow recycleConnection to close the connection, and
> > >> have the filter call recycleConnection. That way, as you improve your
> > >> webapp's code, the connections will be closed as soon as possible
> > >> instead of waiting until the request is (mostly) finished.
> > >>
> > >> Again, Elli, a great suggestion!
> > >>
> > >> - -chris
> > >> -----BEGIN PGP SIGNATURE-----
> > >> Version: GnuPG v1.4.10 (MingW32)
> > >> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
> > >>
> > >> iEYEARECAAYFAkrvQ8AACgkQ9CaO5/Lv0PDOSACeJfqgaXmrySSKItQHji2K6UzK
> > >> hmsAoKIAhRAgwzI/QN8SPdVGkBbewA2a
> > >> =Mqjn
> > >> -----END PGP SIGNATURE-----
> > >>
> > >> ---------------------------------------------------------------------
> > >> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> > >> For additional commands, e-mail: users-help@tomcat.apache.org
> > >>
> > >>
> > >
> >
>
Re: ConnectionPool question
Posted by Elli Albek <el...@sustainlane.com>.
No, you do not need to close the XXXMetaData classes.
E
On Tue, Nov 3, 2009 at 12:02 PM, Josh Gooding <jo...@gmail.com>wrote:
> One more question on bleeding resources. When closing RS / statement /
> connections. Do I have to do anything with the MetaData if I got that as
> well? (I.E Do I explicitly have to close the metadata as well?)
>
> Josh
>
> On Tue, Nov 3, 2009 at 2:01 PM, Josh Gooding <jo...@gmail.com>
> wrote:
>
> > Elle,
> >
> > I am going to dig into this code and check it out. I want to know more
> > about how to use threadlocal and filters. (Sorry I'm not as experienced
> in
> > Tomcat as some for you gurus here).
> >
> > The code looks promising and I like the 2nd option due to the fact that
> > each HTTP req. only has one connection (which should drop the overhead
> > immensely) however for right now, I just want to fix the bleeding issue
> > (which it seems that I have caught a good portion of them), so I'll use
> my
> > legacy code, but during a "minor" code release, I can definitely look
> into
> > rolling this out. I am getting a ton of "abandoned" connection warnings
> in
> > the console window, so I need to find out where these are coming from
> now.
> >
> > I don't know where to begin thanking you guys but thank you. I've gotten
> > more mentoring here on this listing than I have in 2 years at my current
> > employer. Thank you all again.
> >
> > - Josh
> >
> >
> > On Mon, Nov 2, 2009 at 3:40 PM, Christopher Schultz <
> > chris@christopherschultz.net> wrote:
> >
> >> -----BEGIN PGP SIGNED MESSAGE-----
> >> Hash: SHA1
> >>
> >> Elli,
> >>
> >> On 11/2/2009 4:08 AM, Elli Albek wrote:
> >> > I think you can have a solution without changing your code.
> >> >
> >> > Try something like this:
> >> >
> >> > getConnection() static method should get the connection, and add it to
> a
> >> > list that you keep in threadlocal.
> >> >
> >> > recycleConnection() should close the connection and remove the
> >> connection
> >> > object from thread local.
> >> >
> >> > Add a servlet filter that closes all connections in thread local. The
> >> filter
> >> > calls next filter, and in a finally block get the connections from
> >> thread
> >> > local, close all of them, and clear the list in thread local.
> >>
> >> This is a horrible, nasty hack and it's entirely brilliant!
> >>
> >> I would change Elli's implementation just slightly, and actually write
> >> your own DataSource implementation that piggybacks on another one.
> >> Basically, you just wrap the DataSource that Tomcat provides either by:
> >>
> >> a. Using JNDI to look-up the Tomcat-created JNDI DataSource and just
> >> writing the plumbing code to pass everything through
> >> b. Actually subclass the DataSource class(es) provided by Tomcat and
> >> use /those/ in your <Resource> configuration.
> >>
> >> I would also not make any of this static... there's just no reason to do
> >> so, especially if your DataSource object is in the JNDI context.
> >>
> >> Although the /real/ solution is to fix the code, I really like this
> >> solution for a couple of reasons:
> >>
> >> 1. It requires no wrapping of Connection, Statement, etc. objects
> >> (which is entirely miserable if you've ever had to do it)
> >> 2. It requires no changes to your code whatsoever (if you use my
> >> DataSource-wrapping suggestion above)
> >> 3. You won't end up closing your connection, statement, result set, etc.
> >> too early because your code has completed execution (unless you
> >> are using JDBC resources across requests, which is another story)
> >>
> >> What this won't help, unfortunately is:
> >>
> >> * Closing your ResultSet and Statement objects (though this can be
> >> solved by wrapping the Connection, Statement, etc. objects handed-
> >> out by your DataSource. Yes, it's miserable.)
> >>
> >> > This will allow you to keep your legacy code. As far as I remember
> DBCP
> >> has
> >> > an option to close the result sets and statements when you close the
> >> > connection. If not this will partly work.
> >>
> >> I don't believe commons-dbcp has this capability at all. I'm willing to
> >> read any documentation to the contrary, though.
> >>
> >> > Version 2: Advanced
> >> >
> >> > Keep the actual connection in thread local. You will have one
> connection
> >> per
> >> > HTTP request. getConnection() should be something like
> >> >
> >> > public static /* NOT synchronized */ Connection getConnection(){
> >> >
> >> > Connection c = ...// get the connection from thread local
> >> >
> >> > if (c != null)
> >> >
> >> > return c;
> >> >
> >> > Connection c = ...// get the connection from JNDI/DBCP
> >> >
> >> > // put connection in thread local
> >> >
> >> > return c;
> >> >
> >> > }
> >>
> >> I like this technique, too. You just have to decide if it's acceptable
> >> for your webapp to re-use connections. I can't imagine why that would be
> >> a problem, but it's worth considering before you blindly do it. This
> >> optimization can save you from deadlock (though you're killing-off
> >> connections after 15 seconds anyway) and should significantly improve
> >> the performance of your webapp because you won't be bleeding so many
> >> connections: you're limited to bleeding one connection per request
> >> instead of potentially dozens.
> >>
> >> > recycleConnection(){
> >> >
> >> > // empty, connection will be recycled by filter.
> >> >
> >> > }
> >>
> >> I would actually allow recycleConnection to close the connection, and
> >> have the filter call recycleConnection. That way, as you improve your
> >> webapp's code, the connections will be closed as soon as possible
> >> instead of waiting until the request is (mostly) finished.
> >>
> >> Again, Elli, a great suggestion!
> >>
> >> - -chris
> >> -----BEGIN PGP SIGNATURE-----
> >> Version: GnuPG v1.4.10 (MingW32)
> >> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
> >>
> >> iEYEARECAAYFAkrvQ8AACgkQ9CaO5/Lv0PDOSACeJfqgaXmrySSKItQHji2K6UzK
> >> hmsAoKIAhRAgwzI/QN8SPdVGkBbewA2a
> >> =Mqjn
> >> -----END PGP SIGNATURE-----
> >>
> >> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> >> For additional commands, e-mail: users-help@tomcat.apache.org
> >>
> >>
> >
>
Re: ConnectionPool question
Posted by Josh Gooding <jo...@gmail.com>.
One more question on bleeding resources. When closing RS / statement /
connections. Do I have to do anything with the MetaData if I got that as
well? (I.E Do I explicitly have to close the metadata as well?)
Josh
On Tue, Nov 3, 2009 at 2:01 PM, Josh Gooding <jo...@gmail.com> wrote:
> Elle,
>
> I am going to dig into this code and check it out. I want to know more
> about how to use threadlocal and filters. (Sorry I'm not as experienced in
> Tomcat as some for you gurus here).
>
> The code looks promising and I like the 2nd option due to the fact that
> each HTTP req. only has one connection (which should drop the overhead
> immensely) however for right now, I just want to fix the bleeding issue
> (which it seems that I have caught a good portion of them), so I'll use my
> legacy code, but during a "minor" code release, I can definitely look into
> rolling this out. I am getting a ton of "abandoned" connection warnings in
> the console window, so I need to find out where these are coming from now.
>
> I don't know where to begin thanking you guys but thank you. I've gotten
> more mentoring here on this listing than I have in 2 years at my current
> employer. Thank you all again.
>
> - Josh
>
>
> On Mon, Nov 2, 2009 at 3:40 PM, Christopher Schultz <
> chris@christopherschultz.net> wrote:
>
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> Elli,
>>
>> On 11/2/2009 4:08 AM, Elli Albek wrote:
>> > I think you can have a solution without changing your code.
>> >
>> > Try something like this:
>> >
>> > getConnection() static method should get the connection, and add it to a
>> > list that you keep in threadlocal.
>> >
>> > recycleConnection() should close the connection and remove the
>> connection
>> > object from thread local.
>> >
>> > Add a servlet filter that closes all connections in thread local. The
>> filter
>> > calls next filter, and in a finally block get the connections from
>> thread
>> > local, close all of them, and clear the list in thread local.
>>
>> This is a horrible, nasty hack and it's entirely brilliant!
>>
>> I would change Elli's implementation just slightly, and actually write
>> your own DataSource implementation that piggybacks on another one.
>> Basically, you just wrap the DataSource that Tomcat provides either by:
>>
>> a. Using JNDI to look-up the Tomcat-created JNDI DataSource and just
>> writing the plumbing code to pass everything through
>> b. Actually subclass the DataSource class(es) provided by Tomcat and
>> use /those/ in your <Resource> configuration.
>>
>> I would also not make any of this static... there's just no reason to do
>> so, especially if your DataSource object is in the JNDI context.
>>
>> Although the /real/ solution is to fix the code, I really like this
>> solution for a couple of reasons:
>>
>> 1. It requires no wrapping of Connection, Statement, etc. objects
>> (which is entirely miserable if you've ever had to do it)
>> 2. It requires no changes to your code whatsoever (if you use my
>> DataSource-wrapping suggestion above)
>> 3. You won't end up closing your connection, statement, result set, etc.
>> too early because your code has completed execution (unless you
>> are using JDBC resources across requests, which is another story)
>>
>> What this won't help, unfortunately is:
>>
>> * Closing your ResultSet and Statement objects (though this can be
>> solved by wrapping the Connection, Statement, etc. objects handed-
>> out by your DataSource. Yes, it's miserable.)
>>
>> > This will allow you to keep your legacy code. As far as I remember DBCP
>> has
>> > an option to close the result sets and statements when you close the
>> > connection. If not this will partly work.
>>
>> I don't believe commons-dbcp has this capability at all. I'm willing to
>> read any documentation to the contrary, though.
>>
>> > Version 2: Advanced
>> >
>> > Keep the actual connection in thread local. You will have one connection
>> per
>> > HTTP request. getConnection() should be something like
>> >
>> > public static /* NOT synchronized */ Connection getConnection(){
>> >
>> > Connection c = ...// get the connection from thread local
>> >
>> > if (c != null)
>> >
>> > return c;
>> >
>> > Connection c = ...// get the connection from JNDI/DBCP
>> >
>> > // put connection in thread local
>> >
>> > return c;
>> >
>> > }
>>
>> I like this technique, too. You just have to decide if it's acceptable
>> for your webapp to re-use connections. I can't imagine why that would be
>> a problem, but it's worth considering before you blindly do it. This
>> optimization can save you from deadlock (though you're killing-off
>> connections after 15 seconds anyway) and should significantly improve
>> the performance of your webapp because you won't be bleeding so many
>> connections: you're limited to bleeding one connection per request
>> instead of potentially dozens.
>>
>> > recycleConnection(){
>> >
>> > // empty, connection will be recycled by filter.
>> >
>> > }
>>
>> I would actually allow recycleConnection to close the connection, and
>> have the filter call recycleConnection. That way, as you improve your
>> webapp's code, the connections will be closed as soon as possible
>> instead of waiting until the request is (mostly) finished.
>>
>> Again, Elli, a great suggestion!
>>
>> - -chris
>> -----BEGIN PGP SIGNATURE-----
>> Version: GnuPG v1.4.10 (MingW32)
>> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>>
>> iEYEARECAAYFAkrvQ8AACgkQ9CaO5/Lv0PDOSACeJfqgaXmrySSKItQHji2K6UzK
>> hmsAoKIAhRAgwzI/QN8SPdVGkBbewA2a
>> =Mqjn
>> -----END PGP SIGNATURE-----
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>>
>>
>
Re: ConnectionPool question
Posted by Josh Gooding <jo...@gmail.com>.
Elle,
I am going to dig into this code and check it out. I want to know more
about how to use threadlocal and filters. (Sorry I'm not as experienced in
Tomcat as some for you gurus here).
The code looks promising and I like the 2nd option due to the fact that each
HTTP req. only has one connection (which should drop the overhead immensely)
however for right now, I just want to fix the bleeding issue (which it seems
that I have caught a good portion of them), so I'll use my legacy code, but
during a "minor" code release, I can definitely look into rolling this out.
I am getting a ton of "abandoned" conenction warnings in the console window,
so I need to find out where these are coming from now.
I don't know where to begin thanking you guys but thank you. I've gotten
more mentoring here on this listing than I have in 2 years at my current
employer. Thank you all again.
- Josh
On Mon, Nov 2, 2009 at 3:40 PM, Christopher Schultz <
chris@christopherschultz.net> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Elli,
>
> On 11/2/2009 4:08 AM, Elli Albek wrote:
> > I think you can have a solution without changing your code.
> >
> > Try something like this:
> >
> > getConnection() static method should get the connection, and add it to a
> > list that you keep in threadlocal.
> >
> > recycleConnection() should close the connection and remove the connection
> > object from thread local.
> >
> > Add a servlet filter that closes all connections in thread local. The
> filter
> > calls next filter, and in a finally block get the connections from thread
> > local, close all of them, and clear the list in thread local.
>
> This is a horrible, nasty hack and it's entirely brilliant!
>
> I would change Elli's implementation just slightly, and actually write
> your own DataSource implementation that piggybacks on another one.
> Basically, you just wrap the DataSource that Tomcat provides either by:
>
> a. Using JNDI to look-up the Tomcat-created JNDI DataSource and just
> writing the plumbing code to pass everything through
> b. Actually subclass the DataSource class(es) provided by Tomcat and
> use /those/ in your <Resource> configuration.
>
> I would also not make any of this static... there's just no reason to do
> so, especially if your DataSource object is in the JNDI context.
>
> Although the /real/ solution is to fix the code, I really like this
> solution for a couple of reasons:
>
> 1. It requires no wrapping of Connection, Statement, etc. objects
> (which is entirely miserable if you've ever had to do it)
> 2. It requires no changes to your code whatsoever (if you use my
> DataSource-wrapping suggestion above)
> 3. You won't end up closing your connection, statement, result set, etc.
> too early because your code has completed execution (unless you
> are using JDBC resources across requests, which is another story)
>
> What this won't help, unfortunately is:
>
> * Closing your ResultSet and Statement objects (though this can be
> solved by wrapping the Connection, Statement, etc. objects handed-
> out by your DataSource. Yes, it's miserable.)
>
> > This will allow you to keep your legacy code. As far as I remember DBCP
> has
> > an option to close the result sets and statements when you close the
> > connection. If not this will partly work.
>
> I don't believe commons-dbcp has this capability at all. I'm willing to
> read any documentation to the contrary, though.
>
> > Version 2: Advanced
> >
> > Keep the actual connection in thread local. You will have one connection
> per
> > HTTP request. getConnection() should be something like
> >
> > public static /* NOT synchronized */ Connection getConnection(){
> >
> > Connection c = ...// get the connection from thread local
> >
> > if (c != null)
> >
> > return c;
> >
> > Connection c = ...// get the connection from JNDI/DBCP
> >
> > // put connection in thread local
> >
> > return c;
> >
> > }
>
> I like this technique, too. You just have to decide if it's acceptable
> for your webapp to re-use connections. I can't imagine why that would be
> a problem, but it's worth considering before you blindly do it. This
> optimization can save you from deadlock (though you're killing-off
> connections after 15 seconds anyway) and should significantly improve
> the performance of your webapp because you won't be bleeding so many
> connections: you're limited to bleeding one connection per request
> instead of potentially dozens.
>
> > recycleConnection(){
> >
> > // empty, connection will be recycled by filter.
> >
> > }
>
> I would actually allow recycleConnection to close the connection, and
> have the filter call recycleConnection. That way, as you improve your
> webapp's code, the connections will be closed as soon as possible
> instead of waiting until the request is (mostly) finished.
>
> Again, Elli, a great suggestion!
>
> - -chris
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkrvQ8AACgkQ9CaO5/Lv0PDOSACeJfqgaXmrySSKItQHji2K6UzK
> hmsAoKIAhRAgwzI/QN8SPdVGkBbewA2a
> =Mqjn
> -----END PGP SIGNATURE-----
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: ConnectionPool question
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Elli,
On 11/2/2009 4:08 AM, Elli Albek wrote:
> I think you can have a solution without changing your code.
>
> Try something like this:
>
> getConnection() static method should get the connection, and add it to a
> list that you keep in threadlocal.
>
> recycleConnection() should close the connection and remove the connection
> object from thread local.
>
> Add a servlet filter that closes all connections in thread local. The filter
> calls next filter, and in a finally block get the connections from thread
> local, close all of them, and clear the list in thread local.
This is a horrible, nasty hack and it's entirely brilliant!
I would change Elli's implementation just slightly, and actually write
your own DataSource implementation that piggybacks on another one.
Basically, you just wrap the DataSource that Tomcat provides either by:
a. Using JNDI to look-up the Tomcat-created JNDI DataSource and just
writing the plumbing code to pass everything through
b. Actually subclass the DataSource class(es) provided by Tomcat and
use /those/ in your <Resource> configuration.
I would also not make any of this static... there's just no reason to do
so, especially if your DataSource object is in the JNDI context.
Although the /real/ solution is to fix the code, I really like this
solution for a couple of reasons:
1. It requires no wrapping of Connection, Statement, etc. objects
(which is entirely miserable if you've ever had to do it)
2. It requires no changes to your code whatsoever (if you use my
DataSource-wrapping suggestion above)
3. You won't end up closing your connection, statement, result set, etc.
too early because your code has completed execution (unless you
are using JDBC resources across requests, which is another story)
What this won't help, unfortunately is:
* Closing your ResultSet and Statement objects (though this can be
solved by wrapping the Connection, Statement, etc. objects handed-
out by your DataSource. Yes, it's miserable.)
> This will allow you to keep your legacy code. As far as I remember DBCP has
> an option to close the result sets and statements when you close the
> connection. If not this will partly work.
I don't believe commons-dbcp has this capability at all. I'm willing to
read any documentation to the contrary, though.
> Version 2: Advanced
>
> Keep the actual connection in thread local. You will have one connection per
> HTTP request. getConnection() should be something like
>
> public static /* NOT synchronized */ Connection getConnection(){
>
> Connection c = ...// get the connection from thread local
>
> if (c != null)
>
> return c;
>
> Connection c = ...// get the connection from JNDI/DBCP
>
> // put connection in thread local
>
> return c;
>
> }
I like this technique, too. You just have to decide if it's acceptable
for your webapp to re-use connections. I can't imagine why that would be
a problem, but it's worth considering before you blindly do it. This
optimization can save you from deadlock (though you're killing-off
connections after 15 seconds anyway) and should significantly improve
the performance of your webapp because you won't be bleeding so many
connections: you're limited to bleeding one connection per request
instead of potentially dozens.
> recycleConnection(){
>
> // empty, connection will be recycled by filter.
>
> }
I would actually allow recycleConnection to close the connection, and
have the filter call recycleConnection. That way, as you improve your
webapp's code, the connections will be closed as soon as possible
instead of waiting until the request is (mostly) finished.
Again, Elli, a great suggestion!
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkrvQ8AACgkQ9CaO5/Lv0PDOSACeJfqgaXmrySSKItQHji2K6UzK
hmsAoKIAhRAgwzI/QN8SPdVGkBbewA2a
=Mqjn
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: ConnectionPool question
Posted by Elli Albek <el...@sustainlane.com>.
Oops, I had a copy paste error. Version 2 has the filter method twice.
Ignore the first, use the second. Notice this code was written in an email
client, not an IDE :)
E