On Wed, Apr 2, 2014 at 10:19 AM, Victor Sterpu <victor@xxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
