Re: Lock problem - Mailing list pgsql-general

There may pass a few days or weeks until next lock.
But I don't  undesrtand why the whole table is locked if there is one
uncommited transaction.

The following scenario might be the cause:
1. Transaction is started
2. the client application is closed because of a power surge, the
started transaction will never be commited
3. from the server point of view there is a unfinished transaction that
will block future statements

Is this normal behaviour?


------ Original Message ------
From: "Victor Sterpu" <victor@caido.ro>
To: "Merlin Moncure" <mmoncure@gmail.com>
Cc: "PostgreSQL General" <pgsql-general@postgresql.org>
Sent: 4/2/2014 7:19:06 PM
Subject: Re: [GENERAL] Lock problem

>I followed all your advice and it is obiuos that this log will show
>exactly what I need to debug the situation.
>Great tip, thank you.
>
>------ Original Message ------
>From: "Merlin Moncure" <mmoncure@gmail.com>
>To: "Victor Sterpu" <victor@caido.ro>
>Cc: "PostgreSQL General" <pgsql-general@postgresql.org>
>Sent: 4/2/2014 7:08:08 PM
>Subject: Re: [GENERAL] Lock problem
>
>>On Wed, Apr 2, 2014 at 11:00 AM, Victor Sterpu <victor@caido.ro>
>>wrote:
>>>  All my transactions have commit or rollback.
>>
>>Well, you have to verify that. There's a couple of ways to do it.
>>Probably the most direct is to:
>>1. Make sure database is logging pids in log_line_prefix (this is a
>>good idea all around)
>>2. turn on all statement logging (be advised: this can eat a lot of
>>log space and slow down the server).
>>
>>Those two changes do not require a restart. A pg_ctl reload should
>>be sufficient.
>>
>>Once you can do that, you should be able to locate database sessions
>>per pg_stat_activity that are 'idle in transaction' for a long time
>>without activity (anything over a second or so should be suspicious).
>>That will give the pid which you can then use to grep through the
>>statement log.
>>
>>Common culprits are:
>>*) Dubious connection pooling solutions (php pconnect comes to mind)
>>*) Bad error handling logic in application (say, badly handled thrown
>>exception)
>>
>>merlin
>
>
>
>-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general