POSTGRESQL 鎖表的問題

標籤：style   blog   io   color   ar   os   for   sp   div   

一、找出所的語句

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.queryfrom    (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‘    ) waitjoin    (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‘    ) grantedon (    ( 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 con ( c.relfilenode = wait.relation )left join    pg_namespace don ( c.relnamespace = d.oid )order bygranted.query_start;

二、殺掉依賴的sql

select pg_terminate_backend(進程id)

 

POSTGRESQL 鎖表的問題