Lock Management: Waiting on locks

From: Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Lock Management: Waiting on locks
Date: 2014-11-25 21:07:51
Message-ID: CALSLE1MWYwHe6orfCcWyVOKJGHLdqcdiGmMa=3xf0TckG=Qxhg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Am debugging a race condition scenario where multiple transaction are
running in parallel and there are insert/update these transactions are
performing.

I was able to identify the blocking query and blocked query using following
SQL.

However observed the blocking query is holding the locks on the tuple in
minutes thereby making the other transaction query to wait on locks and
slowness.

Can I get more insight why the blocking query is not releasing locks?

SELECT
kl.pid as blocking_pid,
ka.usename as blocking_user,
ka.query as blocking_query,
bl.pid as blocked_pid,
a.usename as blocked_user,
a.query as blocked_query,
to_char(age(now(), a.query_start),'HH24h:MIm:SSs') as age
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a
ON bl.pid = a.pid
JOIN pg_catalog.pg_locks kl
ON bl.locktype = kl.locktype
and bl.database is not distinct from kl.database
and bl.relation is not distinct from kl.relation
and bl.page is not distinct from kl.page
and bl.tuple is not distinct from kl.tuple
and bl.virtualxid is not distinct from kl.virtualxid
and bl.transactionid is not distinct from kl.transactionid
and bl.classid is not distinct from kl.classid
and bl.objid is not distinct from kl.objid
and bl.objsubid is not distinct from kl.objsubid
and bl.pid <> kl.pid
JOIN pg_catalog.pg_stat_activity ka
ON kl.pid = ka.pid
WHERE kl.granted and not bl.granted
ORDER BY a.query_start;

Regards...

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andy Colson 2014-11-25 21:19:31 Re: Best filesystem for a high load db
Previous Message Dev Kumkar 2014-11-25 21:02:09 Re: Transactions to create pg_multixact members and offsets