查询是否锁表了
create view viewlocks as SELECT waiting.locktype AS waiting_locktype, --可锁定对象的类型:relation, extend, page, tuple,transactionid, virtualxid,object, userlock, advisory waiting.relation::regclass AS waiting_table, --等待表 waiting_stm.query AS waiting_query, --等待查询 waiting.mode AS waiting_mode, --这个进程持有的或者是期望持有的锁模式 waiting.pid AS waiting_pid, --持有或者等待这个锁的服务器进程的进程ID ,如果锁是被一个预备事务持有的,那么为空 other.locktype AS previous_locktype, --当前锁的上层锁 other.relation::regclass AS previous_table, other_stm.query AS previous_query, other_stm.state AS previous_state, other.mode AS Previous_mode, other.pid AS previous_pid, --等待该pid完成(kill) other.GRANTED AS previous_granted --如果持有锁,为真,如果等待锁,为假 FROM pg_catalog.pg_locks AS waiting JOIN pg_catalog.pg_stat_activity AS waiting_stm ON ( waiting_stm.pid = waiting.pid ) JOIN pg_catalog.pg_locks AS other ON ( ( waiting."database" = other."database" AND waiting.relation = other.relation ) OR waiting.transactionid = other.transactionid ) JOIN pg_catalog.pg_stat_activity AS other_stm ON ( other_stm.pid = other.pid ) WHERE NOT waiting.GRANTED AND waiting.pid <> other.pid; select * from viewlocks;
如果查询到了结果,表示该表被锁 则需要释放锁定
select pg_cancel_backend(pid) select pg_terminate_backend(pid)