Search Postgresql Archives

Lock Management: Waiting on locks

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux