Re: Lock problem

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

>On Wed, Apr 2, 2014 at 10:19 AM, Victor Sterpu <victor@caido.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?