Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id B947E9F9E17 for ; Tue, 11 Sep 2007 08:40:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 95267-01 for ; Tue, 11 Sep 2007 08:39:55 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from easy2.in-chemnitz.de (easy2.in-chemnitz.de [88.198.17.232]) by postgresql.org (Postfix) with ESMTP id 54FCA9F9D3C for ; Tue, 11 Sep 2007 08:39:30 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by easy2.in-chemnitz.de (Postfix) with ESMTP id 1A0BDB1F28 for ; Tue, 11 Sep 2007 13:39:28 +0200 (CEST) Received: from easy2.in-chemnitz.de ([127.0.0.1]) by localhost (easy.in-chemnitz.de [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id roTGzFQfr4tx for ; Tue, 11 Sep 2007 13:39:28 +0200 (CEST) Received: by easy2.in-chemnitz.de (Postfix, from userid 1013) id 057EFB1DFE; Tue, 11 Sep 2007 13:39:28 +0200 (CEST) Date: Tue, 11 Sep 2007 13:39:28 +0200 From: Tino Schwarze To: pgsql-admin@postgresql.org Subject: Re: Idle transactions Message-ID: <20070911113928.GB26986@easy2.in-chemnitz.de> References: MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/60 X-Sequence-Number: 26821 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. -- www.spiritualdesign-chemnitz.de www.lebensraum11.de Tino Schwarze * Parkstraße 17h * 09120 Chemnitz