postgresql 锁表查询语句

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

1 查找锁表的pid

select pid from pg_locks l join pg_class t on l.relation = t.oid where t.relkind = 'r' and t.relname = 'lockedtable';

2 查找锁表的语句

select pid, state, usename, query, query_start from pg_stat_activity where pid in ( select pid from pg_locks l join pg_class t on l.relation = t.oid and t.relkind = 'r' where t.relname = 'lockedtable');

3 查找所有活动的被锁的表

select pid, state, usename, query, query_start

from pg_stat_activity

where pid in (

  select pid from pg_locks l

  join pg_class t on l.relation = t.oid

  and t.relkind = 'r' 

);

4 解锁

SELECT pg_cancel_backend(pid);

5 批量(未排除自己)

select pg_cancel_backend(pid)

from pg_stat_activity

where pid in (

  select pid from pg_locks l

  join pg_class t on l.relation = t.oid

  and t.relkind = 'r' 

);

参考:

1 https://jaketrent.com/post/find-kill-locks-postgres/

2 https://www.postgresonline.com/journal/archives/134-Terminating-Annoying-Back-Ends.html