postgresql 死锁检查与处理

今天在 postgresql 上执行一个 DDL 的时候卡死，其实就是一个 alter table add column 的语句

那么，首先可以用这个 SQL 来看有哪些执行中的查询

select query, * from pg_stat_activity where query != '' order by query_start asc;

但是从这里有两个问题，一是只能看到自己名下的，别人的 SQL 看不到具体 query，二是通过这个结果看不出到底是啥 SQL 阻塞了自己的 DDL

Google 一下，看到这里， https://stackoverflow.com/questions/26489244/how-to-detect-query-which-holds-the-lock-in-postgres

select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

但是可惜，这种方法只能支持到 postgresql 9.6 以上的，而我们是 9.5，但是没关系，下面又给出了另外一种方式

SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
  blockeda.query as blocked_query, blockedl.mode as blocked_mode,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
  blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
  (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
  AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database()

通过这个的结果，可以清楚的看到是哪个 pid 造成了阻塞

locked_item	waiting_duration	blocked_pid	blocked_query	blocked_mode	blocking_pid	blocking_query	blocking_mode
...	00:02:21.268013	8131	alter table  add column ;
	AccessExclusiveLock	5227	<insufficient privilege>	AccessShareLock

接下来，就是处理了，看了一下，那个是个 select，那就不客气了

杀掉有两种方式

select pg_cancel_backend(<pid of the process>)

If the process cannot be killed, try:

select pg_terminate_backend(<pid of the process>)