Connection
StatementHandle(this is where the READONLY/UPDATABLE/FORWARD/REVERSE cursor is
defined)
ResultSet
close ResultSet
CloseStatementHandle
CloseConnection
closing ResultSet closes the ResultSet only but has no effect on Statement
Handle
closing StatementHandle closes ResultSet and StatementHandle
closing Connection closes all \
are you running as a Transaction?
Begin_Transaction
Select into Buffer
UPDATE/DELETE/Insert with dynamic variables
COMMIT will flush to disk and close all handles
Rollback will restore Transaction state to before Begin_Transaction state
END
if you display the queries here and we can help you show how to properly
structure the statements you're using
Martin Gainty
______________________________________________
Jogi és Bizalmassági kinyilatkoztatás/Verzicht und
Vertraulichkeitanmerkung/Note de déni et de confidentialité
Ez az
üzenet bizalmas. Ha nem ön az akinek szánva volt, akkor kérjük, hogy
jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának
készítése nem megengedett. Ez az üzenet csak ismeret cserét szolgál és
semmiféle jogi alkalmazhatósága sincs. Mivel az electronikus üzenetek
könnyen megváltoztathatóak, ezért minket semmi felelöség nem terhelhet
ezen üzenet tartalma miatt.
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est
interdite. Ce message sert à l'information seulement et n'aura pas n'importe
quel effet légalement obligatoire. Étant donné que les email peuvent facilement
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité
pour le contenu fourni.
> Date: Wed, 14 Oct 2009 21:22:35 -0400
> From: ch...@christopherschultz.net
> To: users@tomcat.apache.org
> CC: u...@commons.apache.org
> Subject: Re: DBCP woes (running out of cursors).
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Bill,
>
> On 10/14/2009 5:05 PM, Bill Davidson wrote:
> > Usually, we don't need that many [connections], but sometimes, we get hit
> > really hard
> > with a lot of traffic and do need that many. BTW, this is load balanced
> > across 4 servers that can each do 245 connections.
>
> Is it possible that your server just doesn't want to allocate 245 * 4
> cursors, and that you are just hitting that barrier? I don't believe the
> JDBC driver cares at all how many cursors are allocated, so it's
> unlikely to be a client-side exception being thrown (or, if you prefer,
> it's a server-side error being represented by a client-side exception).
>
> > I thought [logAbandoned and removeAbandoned] was for Connection leaks.
>
> They are. I just thought it would be a good idea to enable these, just
> in case there was a case where leaks were occurring.
>
> > If we had Connection leaks, then the old pools wouldn't work properly
> > either, because the old pools only kill connections when the servlets
> > "free" the Connection (the same as close() on a DBCP connection).
> > The Connection's are being sent back to the pool, but apparently with
> > open cursors lingering.
>
> I though you said that after a connection was checked-out for 120
> seconds, it was forcibly closed by the connection pool.
>
> >>I don't know a thing about Oracle-specific queries, but what does:
> >>
> >>> v$open_cursor a
> >>
> >>mean? Does this explicitly open a new cursor, or use an existing one
> >>called a?
> >
> > v$cursor is a view in the Oracle data dictionary that shows currently
> > open cursors in the current Oracle instance. The sql_text column shows
> > the first 40-50 characters or so of SQL being executed for that cursor.
> > It shows them for both active and inactive sessions. I'm only guessing
> > that the inactive sessions are from Connection's that are closed without
> > having all of their ResultSet's closed. That might be incorrect.
> > Finding concrete information is difficult.
>
> Oh, so this query is intended to find out what is happening on the
> server side, so you can see what cursors are open and what their queries
> are. I thought you meant that a query such as this was being executed
> from your webapp.
>
> > Maybe, but as I said, I've tracked down the SQL for all of the open
> > cursors that don't seem to go away and they all have guaranteed close
> > calls on the Statement's, and many also have them on the ResultSet's.
> > A lot of the SQL is not that funky either. A lot of it is as simple
> > as grabbing a single record "SELECT * FROM some_table WHERE id = ?"
> > or a few records like "SELECT * FROM some_table WHERE some_col = ?".
>
> Do some of those methods have multiple queries being executed? If so,
> it's possible that one statement remains open while the second one is
> closed. For example:
>
> PreparedStatement ps = null;
> PreparedStatement ps2 = null;
>
> try {
> ps = conn.prepareStatement(...);
> ps2 = conn.prepareStatement(...);
>
> ...
> }
> finally
> {
> ps.close();
> }
>
> > }finally{
> > if ( rs != null ){
> > try{
> > rs.close();
> > }catch ( SQLException ex ){
> > // log it.
> > }
> > }
>
> This is perfect. I noticed that I don't see a conn.close in there (which
> is probably appropriate, given that the Connection object is a parameter
> to the method). I assume you have similar finally blocks in calling
> methods, right?
>
> >>>- Should I use timeBetweenEvictionRunsMillis - What's an "eviction" run?
> >>
> >>It's what happens every so often to flush-out all the connections that
> >>have been (for instance) idle too long, etc.
> >
> > That might help. The stuff I'm finding more recently is implying
> > to me that connections are never closed if I don't enable eviction
> > runs.
>
> The eviction run will only to remove connections from the pool: it won't
> fix any resource allocation problems. Your webapp and server ought to be
> able to tolerate all connections being open and active at once (so, a
> full 245 connections in each webapp instance, and 980 connections on the
> server).
>
> The only thing the eviction will really help with is reducing the memory
> being used on both the client and server. I suppose that calling a
> "true" close() on the connection might clean-up any sloppiness going on
> in the client OR the server, and thus might solve your problem, but I
> believe it will be merely hiding the symptom, not actually solving the
> underlying problem.
>
> Do you have access to an Oracle DBA? They may be able to help uncover
> the implications of some of the queries being run... it's possible that
> cursors are being allocated that you didn't expect, or that aren't being
> closed for /other/ reasons.
>
> Good luck,
> - -chris
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkrWeVsACgkQ9CaO5/Lv0PClbgCgnYUGJ/Uzh/UvTDeT8NpdzD/p
> 94sAoKjGV9j3GA01nbZZaBGIdFaC6nlA
> =9VFy
> -----END PGP SIGNATURE-----
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
> For additional commands, e-mail: users-h...@tomcat.apache.org
>
_________________________________________________________________
Your E-mail and More On-the-Go. Get Windows Live Hotmail Free.
http://clk.atdmt.com/GBL/go/171222985/direct/01/
