postgresql 死锁处理

缘起

遇到一个奇怪的现象，select和delete表时正常执行，但truncate和drop表时会一直运行，也不报错。

原因

"drop table " 和 "truncate table " 需要申请排它锁 "ACCESS EXCLUSIVE "，执行这个命令卡住时，说明此时这张表上还有操作正在进行，比如查询等，
那么只有等待这个查询操作完成，"drop table" 或"truncate table"或者增加字段的SQL 才能获取这张表上的 "ACCESS EXCLUSIVE" 锁，操作才能进行下去。

解

1.检索出死锁进程的ID。

select oid,relname from pg_class where relname='all_data';

检索出来的字段中，【wating 】字段，数据为t的那条，就是死锁的进程。找到对应的【procpid 】列的值。

2.将进程杀掉。

SELECT pg_cancel_backend('死锁那条数据的procpid值 ');

结果：运行完后，再次更新这个表，sql顺利执行。

如果pg_stat_activity 没有记录，则查询pg_locks是否有这个对象的锁

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

杀掉进程

select pg_cancel_backend('进程ID');

另外pg_terminate_backend()函数也可以杀掉进程。

总结

select oid,relname from pg_class where relname='all_data';

select locktype,database,pid,relation ,mode from pg_locks where relation=上面oid;
select * from pg_stat_activity where pid = 上面pid
select pg_terminate_backend(上面pid);

删除数据库说被链接

异常

db_v43=> drop database test_db;
ERROR:  database "test_db" is being accessed by other users
DETAIL:  There is 1 other session using the database.

解决

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='数据库名' AND pid<>pg_backend_pid();

然后就可以删除数据库了

查看表结构依赖

在表结构修改（增删改）利于排除死锁

with t_wait as                     
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a,
transactionid,b.query,b.xact_start,b.query_start,b.usename,b.datname 
  from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted),
t_run as 
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,
a.classid,a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,
a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname from pg_locks a,pg_stat_activity b where 
a.pid=b.pid and a.granted) 
select r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,
r.relation::regclass,r.pid r_pid,
r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,
r.query_start r_query_start,
now()-r.query_start r_locktime,
r.query r_query,w.mode w_mode,
w.pid w_pid,w.page w_page,
w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,
now()-w.query_start w_locktime,w.query w_query  
from t_wait w,t_run r where
  r.locktype is not distinct from w.locktype and
  r.database is not distinct from w.database and
  r.relation is not distinct from w.relation and
  r.page is not distinct from w.page and
  r.tuple is not distinct from w.tuple and
  r.classid is not distinct from w.classid and
  r.objid is not distinct from w.objid and
  r.objsubid is not distinct from w.objsubid and
  r.transactionid is not distinct from w.transactionid and
  r.pid <> w.pid
  order by 
  ((  case w.mode
    when 'INVALID' then 0
    when 'AccessShareLock' then 1
    when 'RowShareLock' then 2
    when 'RowExclusiveLock' then 3
    when 'ShareUpdateEx
clusiveLock' then 4
    when 'ShareLock' then 5
    when 'ShareRowExclusiveLock' then 6
    when 'ExclusiveLock' then 7
    when 'AccessExclusiveLock' then 8
    else 0
  end  ) + 
  (  case r.mode
    when 'INVALID' then 0
    when 'AccessShareLock' then 1
    when 'RowShareLock' then 2
    when 'RowExclusiveLock' then 3
    when 'ShareUpdateExclusiveLock' then 4
    when 'ShareLock' then 5
    when 'ShareRowExclusiveLock' then 6
    when 'ExclusiveLock' then 7
    when 'AccessExclusiveLock' then 8
    else 0
  end  )) desc,r.xact_start;
  
  
  select count(1) from pg_stat_activity where state !='idle';
  
  
  
  SELECT
  locktype,
  pg_locks.pid,
  virtualtransaction,
  transactionid,
  nspname,
  relname,
  mode,
  granted,
  cast(date_trunc('second',query_start) AS timestamp) AS query_start,
  query AS query
  FROM
  pg_locks
  LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
  LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace),
  pg_stat_activity
  WHERE
  NOT pg_locks.pid=pg_backend_pid() AND
  pg_locks.pid=pg_stat_activity.pid;

批量删除

把pid拷贝到del文件

cat del |sort | uniq | awk '{print "select pg_terminate_backend("$1");"}'