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
;

二、杀掉依赖的sql

select pg_terminate_backend(进程id)

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。