Re: Uncommited transaction problem

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(at)svana(dot)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.