Re: Lock problem

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

>"Victor Sterpu" <victor@caido.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.