PostgreSQL死锁问题解决

一、概述

二、postgreSQL死锁解决

1、查询正在执行的SQL

看当前这个数据库里有哪些sql正在执行，或是刚刚执行结束，从这里可以分析是否存在资源竞争。

-- 查询正在进行的SQL操作
select * from pg_stat_activity;

上面查询结果中：

pid就是ACTIVITY的唯一标识
state就是活动状态
query就是正在执行的sql语句
query_start就是开始执行的时间

2、查询表锁

（1）查询死锁

select 
	* 
from 
	pg_stat_activity
where 
	datname = 'vcloud' 
AND wait_event_type = 'Lock'

（2）查询可能被锁的表

select * from pg_locks where relation= (select oid from pg_class where relname='可能锁表了的表');

3、取消函数和中断函数

-- 取消后台操作，回滚未提交事物 
select pg_cancel_backend(上面查到的pid)

-- 中断session，回滚未提交事物（pg_stat_activity表state字段中值为idle in transaction的，可以使用下面的中断函数解锁）
select pg_terminate_backend(上面查到的pid)

4、查询阻塞的sql

-- 查询阻塞的sql（死锁了，没有执行通过的sql）
select 
	pg_stat_activity.*,
	pg_locks.locktype,
	pg_locks.database,
	pg_locks.pid,
	pg_locks.mode,
	pg_locks.relation,
	pg_class.relname
from 
	pg_stat_activity,pg_locks,pg_class
where
	pg_stat_activity.pid=pg_locks.pid
and pg_locks.relation=pg_class.oid
and pg_stat_activity.waiting='t'
and upper(pg_class.relname)='AS_ASSETS_EXT'

SELECT
	w1.pid AS 等待进程,
	w1.MODE AS 等待锁模式,
	w2.usename AS 等待用户,
	w2.query AS 等待会话,
	b1.pid AS 锁的进程,
	b1.MODE 锁的锁模式,
	b2.usename AS 锁的用户,
	b2.query AS 锁的会话,
	b2.application_name 锁的应用,
	b2.client_addr 锁的 IP地址,
	b2.query_start 锁的语句执行时间 
FROM
	pg_locks w1
	JOIN pg_stat_activity w2 ON w1.pid = w2.pid
	JOIN pg_locks b1 ON w1.transactionid = b1.transactionid 
	AND w1.pid != b1.pid
	JOIN pg_stat_activity b2 ON b1.pid = b2.pid 
WHERE
	NOT w1.GRANTED;