pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mateus Interciso <p.zarnick@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: Idle transactions
Date: Tue, 11 Sep 2007 12:26:18 +0000 (UTC)
Message-ID: <fc61ha$o66$2@sea.gmane.org> (raw)
References: <fc5td9$o66$1@sea.gmane.org>
	<20070911113928.GB26986@easy2.in-chemnitz.de>

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

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.




view thread (7+ messages)  latest in thread

Message-ID: <fc61ha$o66$2@sea.gmane.org>
Permalink:  ../fc61ha$o66$2@sea.gmane.org/
Also on:    postgresql.org/message-id/fc61ha$o66$2@sea.gmane.org

 · 

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: p.zarnick@gmail.com
  Subject: Re: Idle transactions
  In-Reply-To: <fc61ha$o66$2@sea.gmane.org>

* 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