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> |
Subject: | Re: Lock problem |
Date: | 2014-04-02 15:49:28 |
Message-ID: | CAHyXU0yrj9+BVk4ZiowVM+BXEkiOEb_-8ZyGvOxXj8+5b4CUdw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Victor Sterpu | 2014-04-02 15:59:45 | Re: Lock problem |
Previous Message | Hannes Erven | 2014-04-02 15:46:32 | Re: Table Vacuum Taking a Long Time |