Re: 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