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