Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id D40539F9F7B for ; Tue, 11 Sep 2007 09:26:43 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 56786-02 for ; Tue, 11 Sep 2007 09:26:39 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by postgresql.org (Postfix) with ESMTP id AA1C39F9E5E for ; Tue, 11 Sep 2007 09:26:39 -0300 (ADT) Received: from list by ciao.gmane.org with local (Exim 4.43) id 1IV4p6-0004gG-Cs for pgsql-admin@postgresql.org; Tue, 11 Sep 2007 14:26:32 +0200 Received: from c906d7f2.virtua.com.br ([201.6.215.242]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 11 Sep 2007 14:26:32 +0200 Received: from p.zarnick by c906d7f2.virtua.com.br with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 11 Sep 2007 14:26:32 +0200 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-admin@postgresql.org From: Mateus Interciso Subject: Re: Idle transactions Date: Tue, 11 Sep 2007 12:26:18 +0000 (UTC) Lines: 50 Message-ID: References: <20070911113928.GB26986@easy2.in-chemnitz.de> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@sea.gmane.org X-Gmane-NNTP-Posting-Host: c906d7f2.virtua.com.br User-Agent: Pan/0.132 (Waxed in Black) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/61 X-Sequence-Number: 26822 On Tue, 11 Sep 2007 13:39:28 +0200, Tino Schwarze wrote: > On Tue, Sep 11, 2007 at 11:15:54AM +0000, Mateus Interciso wrote: > >> Hello, it's been a while that I'm seeing that our servers running JBoss >> with PostgreSQL, after some hours of work, have at least 70 or more >> " in transaction" status for transactions, and this is bogging >> down our servers. >> Is this normal behaviour? If not, is there anything I can do to prevent >> it? Why is it happening? What's a transaction that is " in >> transaction" actually really means? > > "idle in transaction" means that someone did a "begin", but didn't issue > a "commit" or "rollback" yet. It is often a sign of bad application > design and you should contact the application developers. Since open > transactions may hold locks on tables, the whole application may stop > unexpectedly if transactions are left open. > > Another possibility is that you've just got a huge workload, e.g. lots > of concurrent access to the application so that it has to perform a lot > of work, but then you should see SELECT/INSERT/UPDATE/etc. as well, not > only "idle in transaction". > > In Java terms, code which uses a transaction should always look like > this: > boolean success = false; // default: roll back, e.g. on Exception > connection.openTransaction(); > try > { > // perform work > success = true; > } > finally > { > connection.closeTransaction (success); > } > > HTH, > > Tino. Thanks for your reply. Is there any way on PostgreSQL that I can see the transaction that haven't been commited? This would simplify the debugging. Thanks a lot. Mateus.