Mailing-List: contact users-help@tomcat.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Tomcat Users List" <users@tomcat.apache.org>
Received-SPF: pass (nike.apache.org: domain of josh.gooding@gmail.com
 designates 209.85.210.203 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=mime-version:in-reply-to:references:date:message-id:subject:from:to
         :content-type;
        b=ZwkacvR+gO7JCj6Qc1Sp5m/7wdDmcQGOswtAPA6jNwLZ+5OmNZUXdl/QA7dK8WLQH6
         CACNWxF2hQ1PCl+tAxLIoqthDNsjqVPxNVbTIbeoTw8TflDQHTVgbcEvl+CXOSeIScP5
         2zopn/eVF7GRC22TN5PwqT2NXkv4QGkSPsy2g=
MIME-Version: 1.0
In-Reply-To: <4d7e0b220911031306j7a365caelb60151fbf56f6d41@mail.gmail.com>
References: <4d7e0b220910291817i390fc7ccu40afc1ec00012b83@mail.gmail.com>
	 <4AEB5751.9060100@christopherschultz.net>
	 <4d7e0b220910302138r18ac7d5fs710b2f42f078bfba@mail.gmail.com>
	 <b97771c60911020108hce277d2n58a8e33994a4e805@mail.gmail.com>
	 <4AEF43C0.60708@christopherschultz.net>
	 <4d7e0b220911031101w652af97eubcd85d717aaacf5b@mail.gmail.com>
	 <4d7e0b220911031202j54b7c38k6ff771821793d6e4@mail.gmail.com>
	 <b97771c60911031213ocde5d2cqfbb98b579fad0e7d@mail.gmail.com>
	 <4d7e0b220911031224o32741dbcvff05932df1397244@mail.gmail.com>
	 <4d7e0b220911031306j7a365caelb60151fbf56f6d41@mail.gmail.com>
Date: Wed, 4 Nov 2009 08:56:20 -0500
Message-ID: <4d7e0b220911040556l70d17505sd8672a739861b94c@mail.gmail.com>
Subject: Re: ConnectionPool question
From: Josh Gooding <josh.gooding@gmail.com>
To: Tomcat Users List <users@tomcat.apache.org>
Content-Type: multipart/alternative; boundary=000e0cd330c8b1d2e504778bf94e

--000e0cd330c8b1d2e504778bf94e
Content-Type: text/plain; charset=ISO-8859-1

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 <josh.gooding@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 <elli@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
>>> > >>
>>> > >>
>>> > >
>>> >
>>>
>>
>>
>

--000e0cd330c8b1d2e504778bf94e--