Skip site navigation (1) Skip section navigation (2)

Re: '<IDLE> in transaction' problem in mod_perl/DBI/DBD-Pg

From: Tsirkin Evgeny <tsurkin(at)mail(dot)jct(dot)ac(dot)il>
To: Rajesh Kumar Mallah <mallah(at)trade-india(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: '<IDLE> in transaction' problem in mod_perl/DBI/DBD-Pg
Date: 2004-10-25 07:48:24
Message-ID: Pine.LNX.4.58.0410250931130.15606@p-roman.jct.ac.il (view raw or flat)
Thread:
Lists: pgsql-admin
On Mon, 25 Oct 2004, Rajesh Kumar Mallah wrote:

> 
> Hi,
> 
> We run our webserver using mod_perl and perl/DBI/DBD-Pg .
> I have observed that most of the pg backends that block other queries
> are in '<IDLE> in transaction' state.  Usually sending a -INT or -TERM
> to such blocking backends solve the problem.
> 
> My question is
> 
> 1. Are there some precuation to be taken in perl/DBI programming
>     in mod_perl environment to avoid backends getting into '<IDLE> in 
> transaction'
>     mode?
Yes.Be very carefull with transaction - that is:
[1] try the backends that are entering transactions to make the updates in 
the same order ,so you will not get a deadlock.(read the manual for more
info).
[2] try to make the transactions as short as you can,if you don't need 
transactions - don't use it ,turn the Autocommit on!

[3] befor your updates /deletes it sometimes VERY usefull to do a select 
for update which will lock the rows befor entering the transactions ,that
will avoid two backends updating same rows to enter transaction and ,so
avoid deadlocks.

> 
> 2. Is it advisable/safe to run a daemon that TERMs such blocking backend
>     at regular interval.

I don't think so - that will not let postgres end the transactions and 
so no changes will be applied,more then that - this may kill the all the
postgres processes .

> 
> 3. Most Importantly , If a do not have access to to the host is it 
> possible to
>     TERMinate such backeds from  psql>

Don't know.
> 
> Regds
> Mallah.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html
> 

-- 
Evgeny.

In response to

Responses

pgsql-admin by date

Next:From: Nikhil ParvaDate: 2004-10-25 08:01:32
Subject: unregister
Previous:From: amol chandekarDate: 2004-10-25 07:40:02
Subject: unsubscribe

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group