Re: Lock problem

------ Original Message ------
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Victor Sterpu" <victor(at)caido(dot)ro>
Cc: pgsql-general(at)postgresql(dot)org
Sent: 4/2/2014 6:31:13 PM
Subject: Re: [GENERAL] Lock problem

>"Victor Sterpu" <victor(at)caido(dot)ro> writes:
>> 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.
>
>You haven't actually explained what your problem is.
>
>> 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;
>
>This query proves little. It might find two different transactions
>waiting for the same transactionid, but it doesn't show that one is
>waiting for the other. They could both be waiting for some third
>transaction.
>
> regards, tom lane

Problem is that my application is hanging because of this locks and I
can't point the problem.
What query would prove more?
All my transactions have commit or rollback.
I don't know how to fix or how to begin to find the problem

Thank you.