PostgreSQL死锁表查询及杀死死锁进程命令

一、PostgreSQL中查询死锁的表

WITH
t_wait AS (
SELECT
a.mode,a.locktype,a.databASe,a.relation,a.page,a.tuple,a.clASsid,a.granted,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fAStpath,
b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
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.granted,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fAStpath,
b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
FROM pg_locks a,pg_stat_activity b
WHERE a.pid=b.pid AND a.granted
),
t_overlap AS
(
SELECT r.*
FROM t_wait w