Find Locks

From PostgreSQL wiki

Revision as of 13:47, 27 October 2009 by Intgr (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Performance Snippets

Find Locks

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing


SELECT
    l1.*,
    l2.virtualtransaction,
    l2.pid,
    l2.mode,
    l2.granted
FROM
    pg_locks l1
JOIN
    pg_locks l2 ON (
        (
            l1.locktype,
            l1.DATABASE,
            l1.relation,
            l1.page,
            l1.tuple,
            l1.virtualxid,
            l1.transactionid,
            l1.classid,
            l1.objid,
            l1.objsubid
        )
    IS NOT DISTINCT FROM
        (
            l2.locktype,
            l2.DATABASE,
            l2.relation,
            l2.page,
            l2.tuple,
            l2.virtualxid,
            l2.transactionid,
            l2.classid,
            l2.objid,
            l2.objsubid
        )
    )
WHERE
    NOT l1.granted
AND
    l2.granted;
Personal tools