pg死锁查询

1、查询激活的执行中的sql

select *
from pg_stat_activity
where state = 'active';

state 字段解释
(pg_stat_activity 是 PostgreSQL 内置的一个系统视图， 是 PostgreSQL 实例维护的一个进程相关的视图，是实时变化的)

• active（活动）:进程正在执行某个语句
• idle(空闲): 进程正在等待客户端的指令.
• idle in transaction （事务空闲）: 进程在处理事务过程中，但当前未执行任何语句.
• idle in transaction (aborted)（事务空闲-退出）:当事务中某个语句执行出错的情况外，其余情况与idle in transaction相同.
• fastpath function call（快通道函数调用）: 后台正在执行某个快通道函数.
• disabled（禁用）: This state is reported if track_activities is disabled in this backend.

2.查询表中存在的锁

select a.locktype, a.database, a.pid, a.mode, a.relation, b.relname
from pg_locks a
join pg_class b on a.relation = b.oid
where lower(b.relname) = '表名称';

3.删除锁

select pg_terminate_backend(pid)
from pg_stat_activity
where state = 'active'
and pid != pg_backend_pid()
--and pid = 14172
and pid in (select a.pid
from pg_locks a
join pg_class b on a.relation = b.oid
where lower(b.relname) = 'news_content')

4.表死锁

select pid,query,* from pg_stat_activity where datname='死锁的数据库' and wait_event_type = 'Lock';
select pg_cancel_backend('死锁那条数据的pid值');##只能杀死select 语句, 对其他语句不生效
pg_terminate_backend('死锁那条数据的pid值');#select,drop等各种操作