Postgresql死锁及解锁

查询表中存在的锁：

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 b.relname = 'tb_student_info';

杀掉死锁进程：

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

补充：

查询被锁的pid以及语句， state这列如果显示成 idle in transaction 就表示被锁了

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 = 'tb_student_info'

);