Postgresql数据库如何解决死锁问题

当我们使用postgresql数据库时，如果遇到数据库表死锁的情况，该如何解决？

1.查询数据库信息

我们可以运行以下的SQL语句，来查询锁住的数据表信息。

# 查询活跃状态	
SELECT * FROM pg_stat_activity;
# 查询正在锁的事务
SELECT * FROM pg_stat_activity WHERE datname='database_name' and wait_event_type='Lock';
-- 查询锁表的名称
SELECT blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS current_statement_in_blocking_process,
		blocked_locks.*,
		blocked_activity.*
FROM pg_locks blocked_locks
     JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
     JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype 
       AND NOT blocking_locks.database IS DISTINCT FROM blocked_locks.database 
       AND NOT blocking_locks.relation IS DISTINCT FROM blocked_locks.relation 
       AND NOT blocking_locks.page IS DISTINCT FROM blocked_locks.page 
       AND NOT blocking_locks.tuple IS DISTINCT FROM blocked_locks.tuple 
       AND NOT blocking_locks.virtualxid IS DISTINCT FROM blocked_locks.virtualxid 
       AND NOT blocking_locks.transactionid IS DISTINCT FROM blocked_locks.transactionid 
       AND NOT blocking_locks.classid IS DISTINCT FROM blocked_locks.classid 
       AND NOT blocking_locks.objid IS DISTINCT FROM blocked_locks.objid 
       AND NOT blocking_locks.objsubid IS DISTINCT FROM blocked_locks.objsubid 
       AND blocking_locks.pid <> blocked_locks.pid
     JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
  WHERE NOT blocked_locks.granted
	order by state_change; 
-- 查询某张被锁的表的oid
select oid from pg_class where relname='table_name';
-- 利用表的oid查询表的进程id
select pid from pg_locks where relation='oid';
--查询锁表时间和锁表信息
SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa
    ON pl.pid = psa.pid;
-- 查询需要释放的锁表信息		
SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
    ON pl.virtualtransaction = '-1/' || ppx.transaction
		where pl.fastpath!='t';	
# 查询账号及状态
SELECT
  pg_locks.pid,
  pg_roles.rolname,
  gp_resqueue_status.rsqname,
  pg_stat_activity.query,
  pg_stat_activity.STATE 
FROM
  pg_roles,
  gp_toolkit.gp_resqueue_status,
  pg_locks,
  pg_stat_activity 
WHERE
  pg_roles.rolresqueue = pg_locks.objid 
  AND pg_locks.objid = gp_toolkit.gp_resqueue_status.queueid 
  AND pg_stat_activity.pid = pg_locks.pid;

2.释放数据库的数据表锁

# 2.1 拼接所有需要中止的进程信息
-- 方法1：构建拼接语句(强制取消，需要管理员权限)
select concat('select pg_terminate_backend( ',pid,');' ) pid  from (		SELECT distinct pid as pid   FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
    ON pl.virtualtransaction = '-1/' || ppx.transaction
		where pl.fastpath!='t'	) temp;
-- 方法2：利用oid查询出对应的pid，构建取消查询语句
select concat('select pg_cancel_backend( ',pid,');' ) pid from pg_locks where relation='oid';
-- 方法3：利用oid查询出对应的pid，构建取消进程语句(需管理员权限)
select concat('select pg_terminate_backend( ',pid,');' ) pid from pg_locks where relation='oid';
# 2.2 复制2.1语句的运行结果，并运行以下语句
kill 3256;