Re: Lock problem

From: "Victor Sterpu" <victor(at)caido(dot)ro>
To: "Victor Sterpu" <victor(at)caido(dot)ro>, "Merlin Moncure" <mmoncure(at)gmail(dot)com>
Cc: "PostgreSQL General" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Lock problem
Date: 2014-04-02 18:24:43
Message-ID: em2eb47971-88dd-460f-9ad7-d17f5599378f@victor-pc
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm sure is not right, but is a there a server side solution for such
sitations?
A configuration - timeout for idle transactions.

------ Original Message ------
From: "Victor Sterpu" <victor(at)caido(dot)ro>
To: "Victor Sterpu" <victor(at)caido(dot)ro>; "Merlin Moncure"
<mmoncure(at)gmail(dot)com>
Cc: "PostgreSQL General" <pgsql-general(at)postgresql(dot)org>
Sent: 4/2/2014 9:13:22 PM
Subject: Re[2]: [GENERAL] Lock problem

>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(at)caido(dot)ro>
>To: "Merlin Moncure" <mmoncure(at)gmail(dot)com>
>Cc: "PostgreSQL General" <pgsql-general(at)postgresql(dot)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(at)gmail(dot)com>
>>To: "Victor Sterpu" <victor(at)caido(dot)ro>
>>Cc: "PostgreSQL General" <pgsql-general(at)postgresql(dot)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(at)caido(dot)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(at)postgresql(dot)org)
>>To make changes to your subscription:
>>http://www.postgresql.org/mailpref/pgsql-general
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bala Venkat 2014-04-02 19:00:20 pg_stat_activity
Previous Message Victor Sterpu 2014-04-02 18:13:22 Re: Lock problem