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...