POSTGRESQL 锁表的问题

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

一、找出所的语句

select

    wait.pid,

    wait.query as wait_query,

    wait.query_start as wait_query_start,

    wait.locktype,

    granted.pid as waitfor_pid,

    granted.relation,

    granted.datname || '.' || d.nspname || '.' || c.relname as name,

    granted.transactionid,

    granted.virtualxid,

    granted.usename,

    granted.client_addr,

    granted.query_start,

    granted.query

from

    (select

          a.query,

          a.query_start,

          b.pid,

          b.relation,

          b.transactionid,

          b.page,

          b.tuple,

          b.locktype,

          b.virtualxid

     from

          pg_stat_activity a,

          pg_locks b

     where

          a.waiting = 't'

          and a.pid = b.pid

          and granted = 'f'

    ) wait

join

    (select

        b.pid,

        b.usename,

        b.client_addr,

        b.backend_start,

        b.query_start,

        b.waiting,

        b.query,

        b.datname,

        a.relation,

        a.transactionid,

        a.page,

        a.tuple,

        a.locktype,

        a.virtualxid

    from

        pg_locks a,

        pg_stat_activity b

    where

        a.pid = b.pid

        and a.granted = 't'

    ) granted

on (

    ( wait.locktype = 'transactionid'

    and granted.locktype = 'transactionid'

    and wait.transactionid = granted.transactionid )

    or

    ( wait.locktype = 'relation'

    and granted.locktype = 'relation'

    and wait.relation = granted.relation

    )

    or

    ( wait.locktype = 'virtualxid'

    and granted.locktype = 'virtualxid'

    and wait.virtualxid = granted.virtualxid )

    or

    ( wait.locktype = 'tuple'

    and granted.locktype = 'tuple'

    and wait.relation = granted.relation

    and wait.page = granted.page

    and wait.tuple = granted.tuple )

)

left join

    pg_class c

on ( c.relfilenode = wait.relation )

left join

    pg_namespace d

on ( c.relnamespace = d.oid )

order by

granted.query_start

;