Re: [JDBC] Getting "This ResultSet is closed" exceptions

On Feb 18, 2008 5:08 PM, Jan de Visser <jdevisser@digitalfairway.com> wrote:
> On Monday 18 February 2008 14:48:24 Paul Tomblin wrote:
> > Forgive a possibly stupid question, but how would a class know whether
> > there is a Connection for this thread already? I use a simple static
> > to hold the Connection, and so I get one for the whole program.
>
> That sounds like a bad plan to me. You're going to interleave transactions
> etc. (You are using transactions, aren't you? :) Look into connection pools

Unfortunately the existing code ran with autocommit on, and there was no concern for transations. I've turned off autocommit, and am running around the code trying to figure out where is the best place to put "conn.commit();" calls. Right now, I'm hunting down a lot of cases where one program or another is stuck because of transaction locks held by another, so I'm being overzealous and sprinkling them around liberally, but I think we definitely need them before we send an event or an RMI call, or before finishing a thread or sleeping, but otherwise we probably don't need any others.

I also run a "vacuumdb -analyze" every night, and that's causing problems in the field with lock contention[1]. That's one reason why I want to make sure we use transactions and commit when we need to.

[1] I know it's off topic for this mailing list, but can anybody point me to a site that shows how to figure out what Java program is causing the thread locks? Right now I'm doing it like:  ps auwwx | grep 'idle in transaction'
that gives me the socket number, and then I use  lsof -i :[socketnumber]
and that gives me the two PIDs that have it open, one of which is postgresql and the other is java, and then I go back to "ps auwwx" to find that the arguments to "java" for that pid to find out what program it is.

-- 
For my assured failures and derelictions I ask pardon beforehand of my
betters and my equals in my Calling here assembled, praying that in
the hour of my temptations, weakness and weariness, the memory of this
my Obligation and of the company before whom it was entered into, may
return to me to aid, comfort and restrain.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               
http://www.postgresql.org/docs/faq