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

Hi,

Somehow those pids automatically got terminated
and my problem was solved. I did not issue any kill as i
do not have access to the server.

curious how it happened any idea??

Regds
mallah.

Tsirkin Evgeny wrote:

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

--

regds
Mallah.

Rajesh Kumar Mallah
+---------------------------------------------------+
| Tradeindia.com (3,11,246) Registered Users |
| Indias' Leading B2B eMarketPlace |
| http://www.tradeindia.com/ |
+---------------------------------------------------+