FIND AND KILL LOCKS ON POSTGRES TABLES.md

gistfile1.txt

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

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

# MATCHING QUERIES WITH LOCKS
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 = 'search_hit'
);

# KILLING LOCKS
SELECT pg_cancel_backend(11929);
SELECT pg_terminate_backend(11929);