postgresql 死锁处理方式

postgresql 死锁处理方式

1、查询

SELECT locker.pid,*
FROM pg_locks locker,pg_stat_activity locker_act, pg_class pc  
WHERE locker.pid=locker_act.pid  
        AND NOT locker.pid=pg_backend_pid()  
        AND application_name<>'pg_statsinfod'  
        AND locker.relation = pc.oid  
        AND pc.reltype<>0 

2、根据pid删除锁

方式1:select pg_cancel_backend(varchar pid);
方式2:select pg_terminate_backend(varchar pid);
-----------------------------------------------------
pg_terminate_backend（pid）过程需要superuser账号执行，会删除session，使用中感觉没啥问题，不会有影响，一般可以执行成功，pg_cancel_backend（pid），很多时候执行不生效

3、查、删组合

方式1:select 'select pg_terminate_backend('||pid||');' from pg_locks where relation in(select oid from pg_class where relname='table name');

方式2:select 'select pg_cancel_backend('||pid||');' from pg_locks where relation in(select oid from pg_class where relname='table name');