JZTXT

pgsql查看数据库死锁脚本

发布时间 2024-01-04 19:03:41作者: 段江涛IT

数据库被锁定后，会导致其它用户无法使用，如下脚本可查询死锁。

SELECT
  pg_locks.pid,
    transactionid,
  usename,
  mode,
  granted,
    pg_stat_activity.backend_start,
  query,
    pg_stat_activity.*
FROM
  pg_locks
  JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
    where transactionid is not null
    ORDER BY backend_start

如下脚本可以停止被锁的线程。

select pg_terminate_backend(1602927)