pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tino Schwarze <postgresql@tisc.de>
To: pgsql-admin@postgresql.org
Subject: Re: Idle transactions
Date: Tue, 11 Sep 2007 13:39:28 +0200
Message-ID: <20070911113928.GB26986@easy2.in-chemnitz.de> (raw)
In-Reply-To: <fc5td9$o66$1@sea.gmane.org>
References: <fc5td9$o66$1@sea.gmane.org>

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 
> "<IDLE> 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 "<IDLE> 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



view thread (7+ messages)  latest in thread

Message-ID: <20070911113928.GB26986@easy2.in-chemnitz.de>
Permalink:  ../20070911113928.GB26986@easy2.in-chemnitz.de/
Also on:    postgresql.org/message-id/20070911113928.GB26986@easy2.in-chemnitz.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: postgresql@tisc.de
  Subject: Re: Idle transactions
  In-Reply-To: <20070911113928.GB26986@easy2.in-chemnitz.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox