postgresql表死锁问题的排查方式

select *

from pg_stat_activity

where state = 'active' ;

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) = 'h5_game' ;

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' )

/* NoLock is not a lock mode, but a flag value meaning "don't get a lock" */

#define NoLock                 0

#define AccessShareLock         1        /* SELECT */

#define RowShareLock          2        /* SELECT FOR UPDATE / FOR SHARE */

#define RowExclusiveLock        3        /* INSERT , UPDATE , DELETE */

#define ShareUpdateExclusiveLock 4       /* VACUUM (non- FULL ),ANALYZE, CREATE

                                          * INDEX CONCURRENTLY */

#define ShareLock                5        /* CREATE INDEX (WITHOUT CONCURRENTLY) */

#define ShareRowExclusiveLock  6        /* like EXCLUSIVE MODE, but allows ROW

                                          * SHARE */

#define ExclusiveLock          7        /* blocks ROW SHARE/ SELECT ... FOR

                                          * UPDATE */

#define AccessExclusiveLock       8        /* ALTER TABLE , DROP TABLE , VACUUM

                                          * FULL , and unqualified LOCK TABLE */

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 等各种操作

select oid,relname from pg_class where relname= 'table name' ;

select locktype,pid,relation,mode,granted,* from pg_locks where relation= '上面查询出来的oid' ;

select pg_terminate_backend( '进程ID' );