Обсуждение: ' in transaction' problem in mod_perl/DBI/DBD-Pg environment.

Поиск
Список
Период
Сортировка

' in transaction' problem in mod_perl/DBI/DBD-Pg environment.

От
Rajesh Kumar Mallah
Дата:
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?

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

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

Regds
Mallah.


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

От
Tsirkin Evgeny
Дата:
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.

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

От
Rajesh Kumar Mallah
Дата:
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/                |
+---------------------------------------------------+


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

От
Tsirkin Evgeny
Дата:
probably you had a deadlock and the postgresql solved it
by itself if that is true - you will have another one soon.

On Mon, 25 Oct 2004, Rajesh Kumar Mallah wrote:

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

--
Evgeny.

Re: ' in transaction' problem in mod_perl/DBI/DBD-Pg environment.

От
"Greg Sabino Mullane"
Дата:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

     

     
> Are there some precuation to be taken in perl/DBI programming
> in mod_perl environment to avoid backends getting into '<IDLE> in
> transaction' mode?

     
If you are not using the latest version of DBD::Pg (1.32), then you
may be experiencing the problem where the "AutoCommit" setting
causes "execute->commit->begin->wait." This is fixed in 1.32: it
now does "begin->execute->commit->wait".

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200410252136

-----BEGIN PGP SIGNATURE-----

iD8DBQFBfapWvJuQZxSWSsgRAn/aAJsFNzN1ru5GpjTL30iM1Oj2EXQ9/QCfTFYa
LxQN3UVonFcFpKYEkf0ypog=
=Hc+B
-----END PGP SIGNATURE-----