Обсуждение: ' in transaction' problem in mod_perl/DBI/DBD-Pg environment.
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.
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.
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/ | +---------------------------------------------------+
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.
-----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-----