pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Idle transactions
7+ messages / 3 participants
[nested] [flat]

* Idle transactions
@ 2007-09-11 11:15 Mateus Interciso <p.zarnick@gmail.com>
  2007-09-11 11:37 ` Re: Idle transactions Thomas Markus <t.markus@proventis.net>
  2007-09-11 11:39 ` Re: Idle transactions Tino Schwarze <postgresql@tisc.de>
  0 siblings, 2 replies; 7+ messages in thread

From: Mateus Interciso @ 2007-09-11 11:15 UTC (permalink / raw)
  To: pgsql-admin

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?

Thanks a lot.

Mateus.




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Idle transactions
  2007-09-11 11:15 Idle transactions Mateus Interciso <p.zarnick@gmail.com>
@ 2007-09-11 11:37 ` Thomas Markus <t.markus@proventis.net>
  1 sibling, 0 replies; 7+ messages in thread

From: Thomas Markus @ 2007-09-11 11:37 UTC (permalink / raw)
  To: pgsql-admin

Hi,

it means that a transaction isn't finished (commit/rollback). check your 
application for long running operations or programmming errors.

tm

Mateus Interciso schrieb:
> 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?
>
> Thanks a lot.
>
> Mateus.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>   

Attachments:

t.markus.vcf (text/x-vcard, 267B) download begin:vcard fn:Thomas Markus n:Markus;Thomas org:proventis GmbH adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany email;internet:t.markus@proventis.net tel;work:+49 30 29 36 399 22 x-mozilla-html:FALSE url:http://www.proventis.net version:2.1 end:vcard
^ permalink raw reply [nested|flat] 7+ messages in thread

* Re: Idle transactions
  2007-09-11 11:15 Idle transactions Mateus Interciso <p.zarnick@gmail.com>
@ 2007-09-11 11:39 ` Tino Schwarze <postgresql@tisc.de>
  2007-09-11 12:26   ` Re: Idle transactions Mateus Interciso <p.zarnick@gmail.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Tino Schwarze @ 2007-09-11 11:39 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Idle transactions
  2007-09-11 11:15 Idle transactions Mateus Interciso <p.zarnick@gmail.com>
  2007-09-11 11:39 ` Re: Idle transactions Tino Schwarze <postgresql@tisc.de>
@ 2007-09-11 12:26   ` Mateus Interciso <p.zarnick@gmail.com>
  2007-09-11 12:33     ` Re: Idle transactions Thomas Markus <t.markus@proventis.net>
  2007-09-11 14:13     ` Re: Idle transactions Tino Schwarze <postgresql@tisc.de>
  0 siblings, 2 replies; 7+ messages in thread

From: Mateus Interciso @ 2007-09-11 12:26 UTC (permalink / raw)
  To: pgsql-admin

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.




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Idle transactions
  2007-09-11 11:15 Idle transactions Mateus Interciso <p.zarnick@gmail.com>
  2007-09-11 11:39 ` Re: Idle transactions Tino Schwarze <postgresql@tisc.de>
  2007-09-11 12:26   ` Re: Idle transactions Mateus Interciso <p.zarnick@gmail.com>
@ 2007-09-11 12:33     ` Thomas Markus <t.markus@proventis.net>
  2007-09-11 13:43       ` Re: Idle transactions Mateus Interciso <p.zarnick@gmail.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Thomas Markus @ 2007-09-11 12:33 UTC (permalink / raw)
  To: Mateus Interciso <p.zarnick@gmail.com>; +Cc: pgsql-admin

check your jboss log for stack traces.

Mateus Interciso schrieb:
> 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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>   

Attachments:

t.markus.vcf (text/x-vcard, 267B) download begin:vcard fn:Thomas Markus n:Markus;Thomas org:proventis GmbH adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany email;internet:t.markus@proventis.net tel;work:+49 30 29 36 399 22 x-mozilla-html:FALSE url:http://www.proventis.net version:2.1 end:vcard
^ permalink raw reply [nested|flat] 7+ messages in thread

* Re: Idle transactions
  2007-09-11 11:15 Idle transactions Mateus Interciso <p.zarnick@gmail.com>
  2007-09-11 11:39 ` Re: Idle transactions Tino Schwarze <postgresql@tisc.de>
  2007-09-11 12:26   ` Re: Idle transactions Mateus Interciso <p.zarnick@gmail.com>
  2007-09-11 12:33     ` Re: Idle transactions Thomas Markus <t.markus@proventis.net>
@ 2007-09-11 13:43       ` Mateus Interciso <p.zarnick@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Mateus Interciso @ 2007-09-11 13:43 UTC (permalink / raw)
  To: pgsql-admin

On Tue, 11 Sep 2007 14:33:23 +0200, Thomas Markus wrote:

> check your jboss log for stack traces.
> 
> Mateus Interciso schrieb:
>> 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.
>>
>>
>> ---------------------------(end of
>> broadcast)--------------------------- TIP 3: Have you checked our
>> extensive FAQ?
>>
>>                http://www.postgresql.org/docs/faq
>>   
>>   
> begin:vcard
> fn:Thomas Markus
> n:Markus;Thomas
> org:proventis GmbH
> adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany
> email;internet:t.markus@proventis.net tel;work:+49 30 29 36 399 22
> x-mozilla-html:FALSE
> url:http://www.proventis.net
> version:2.1
> end:vcard
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq

There's no way to do it on PostgreSQL instead?




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Idle transactions
  2007-09-11 11:15 Idle transactions Mateus Interciso <p.zarnick@gmail.com>
  2007-09-11 11:39 ` Re: Idle transactions Tino Schwarze <postgresql@tisc.de>
  2007-09-11 12:26   ` Re: Idle transactions Mateus Interciso <p.zarnick@gmail.com>
@ 2007-09-11 14:13     ` Tino Schwarze <postgresql@tisc.de>
  1 sibling, 0 replies; 7+ messages in thread

From: Tino Schwarze @ 2007-09-11 14:13 UTC (permalink / raw)
  To: pgsql-admin

On Tue, Sep 11, 2007 at 12:26:18PM +0000, Mateus Interciso wrote:

> Thanks for your reply.
> Is there any way on PostgreSQL that I can see the transaction that 
> haven't been commited?

The following queries shows open transactions and locks held by the
transaction.

select c.relname,l.transaction,l.pid,l.granted,l.mode from pg_class
c,pg_locks l where l.relation = c.oid order by l.pid, l.mode;

HTH,

Tino.


-- 
www.spiritualdesign-chemnitz.de
www.lebensraum11.de

Tino Schwarze * Parkstraße 17h * 09120 Chemnitz



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2007-09-11 14:13 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-11 11:15 Idle transactions Mateus Interciso <p.zarnick@gmail.com>
2007-09-11 11:37 ` Thomas Markus <t.markus@proventis.net>
2007-09-11 11:39 ` Tino Schwarze <postgresql@tisc.de>
2007-09-11 12:26   ` Mateus Interciso <p.zarnick@gmail.com>
2007-09-11 12:33     ` Thomas Markus <t.markus@proventis.net>
2007-09-11 13:43       ` Mateus Interciso <p.zarnick@gmail.com>
2007-09-11 14:13     ` Tino Schwarze <postgresql@tisc.de>

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