Thread: Uncommited transaction problem
Hi all, I've the following problem: We have client server application. Some clients are connecter via dialup modem, so theirs connections are not so stable. Sometimes happens that dialup connection hangs and if it is in the middle of a transaction tables (rows) stay locked. I'm tunneling postgres through secure tunnel, so I set up keep alive on that connection. If connection is really broken backend is killed by keep alive packet and locks are released. But this doesn't solve my problem entirely. Sometimes when client hangs (it's on M$ windows so it is normal) and a user is just having coffee, connection is ok so tables/rows stay locked. Problem is that I'm not able to get rid of these "dead" backends, even I don't know method how to find them. Question: Is there any general advice how to solve/avoid this problem ? Is there any options how to set keep alive option on PG connections ? Is possible to find backend which is blocking ? Any other ideas ? ;) Regards, Vak
On Wed, Apr 10, 2002 at 10:20:08AM +0200, Vaclav Kulakovsky wrote: > > Hi all, > > I've the following problem: > > We have client server application. Some clients are connecter via dialup > modem, so theirs connections are not so stable. Sometimes happens that > dialup connection hangs and if it is in the middle of a transaction tables > (rows) stay locked. > > I'm tunneling postgres through secure tunnel, so I set up keep alive on > that connection. If connection is really broken backend is killed by keep > alive packet and locks are released. But this doesn't solve my problem > entirely. Sometimes when client hangs (it's on M$ windows so it is normal) > and a user is just having coffee, connection is ok so tables/rows stay > locked. This is an application problem. You should write your application so that it does not need to hold the transaction for more than a few seconds. Surely killing the hung client will kill off the connection? > Problem is that I'm not able to get rid of these "dead" backends, even I > don't know method how to find them. The implication is that you can kill these backends. Killing them will rollback any changes they've made. If this doesn't bother you, why are the applications in a transaction at all. > Question: > > Is there any general advice how to solve/avoid this problem ? > > Is there any options how to set keep alive option on PG connections ? > > Is possible to find backend which is blocking ? You need to avoid the problem completely. Look at your system and decide what needs to be changed. What exactly are you trying to acheive? HTH, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Ignorance continues to thrive when intelligent people choose to do > nothing. Speaking out against censorship and ignorance is the imperative > of all intelligent people.
Yes you can minimize risk by design of your application, but you can never guarantee that your connection won't be broken in the middle of a transaction, so I thing that there should be a method how to find blocking backend (transaction). Vak On Wed, 10 Apr 2002, Martijn van Oosterhout wrote: > On Wed, Apr 10, 2002 at 10:20:08AM +0200, Vaclav Kulakovsky wrote: > > > > Hi all, > > > > I've the following problem: > > > > We have client server application. Some clients are connecter via dialup > > modem, so theirs connections are not so stable. Sometimes happens that > > dialup connection hangs and if it is in the middle of a transaction tables > > (rows) stay locked. > > > > I'm tunneling postgres through secure tunnel, so I set up keep alive on > > that connection. If connection is really broken backend is killed by keep > > alive packet and locks are released. But this doesn't solve my problem > > entirely. Sometimes when client hangs (it's on M$ windows so it is normal) > > and a user is just having coffee, connection is ok so tables/rows stay > > locked. > > This is an application problem. You should write your application so that it > does not need to hold the transaction for more than a few seconds. Surely > killing the hung client will kill off the connection? > > > Problem is that I'm not able to get rid of these "dead" backends, even I > > don't know method how to find them. > > The implication is that you can kill these backends. Killing them will > rollback any changes they've made. If this doesn't bother you, why are the > applications in a transaction at all. > > > Question: > > > > Is there any general advice how to solve/avoid this problem ? > > > > Is there any options how to set keep alive option on PG connections ? > > > > Is possible to find backend which is blocking ? > > You need to avoid the problem completely. Look at your system and decide > what needs to be changed. What exactly are you trying to acheive? > > HTH, > -- > Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > > Ignorance continues to thrive when intelligent people choose to do > > nothing. Speaking out against censorship and ignorance is the imperative > > of all intelligent people. >