Re: Lock problem

------ 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 6:49:28 PM
Subject: Re: [GENERAL] Lock problem

>On Wed, Apr 2, 2014 at 10:19 AM, Victor Sterpu <victor(at)caido(dot)ro> wrote:
>>
>> Hello
>>
>> I have a problem that it seems to be very hard to debug.
>> Problem is from some postgresql locks. I use PostgreSQL 9.1.8.
>>
>> I runned this query to fid the locks:
>>
>> SELECT bl.pid AS blocked_pid,
>> a.usename AS blocked_user,
>> kl.pid AS blocking_pid,
>> ka.usename AS blocking_user,
>> a.current_query AS blocked_statement
>> FROM pg_catalog.pg_locks bl
>> JOIN pg_catalog.pg_stat_activity a ON a.procpid = bl.pid
>> JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid
>>AND kl.pid != bl.pid
>> JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
>> WHERENOT bl.granted;
>>
>> The result is a recursive lock.
>> Pid 10665 is blocked by pid 9844 and pid 9844 is blocked by 10665.
>> These 2 inserts are in 2 separate transactions.
>> Can this be a postgresql bug?
>>
>> blocked_pid blocked_user blocking_statement blocking_duration
>>blocking_pid blocking_user blocked_statement blocked_duration
>
>> 10665 postgres <IDLE> in transaction
>
>"<IDLE> in transaction" is a locking red flag. It means your
>application has opened a transaction and is sitting there holding the
>transaction open. This is a very common cause of subtle application
>locking bugs. It can be legit if the application is doing heavy
>processing during a transaction or you simply raced to an idle
>transaction in pg_stat_activity, but in my experience 95%+ of the time
>it means transaction leakage which in turn leads to locking problems.
>
>merlin

This is weird because all of my transaction have commit or rollback. I
don't leave unterminated transactions.
I can't see how this is happening.
Is there a possibility that some network problems generate this problem?
If this is the case is these some server protection for this situation?

But why a unterminated transaction blocks all table operations?