解决postgresql数据库锁表问题

查询是否锁表了

create view viewlocks as
SELECT
    waiting.locktype           AS waiting_locktype, --可锁定对象的类型:relation, extend, page, tuple,transactionid, virtualxid,object, userlock, advisory
    waiting.relation::regclass AS waiting_table,        --等待表
    waiting_stm.query          AS waiting_query,        --等待查询
    waiting.mode               AS waiting_mode,            --这个进程持有的或者是期望持有的锁模式
    waiting.pid                AS waiting_pid,            --持有或者等待这个锁的服务器进程的进程ID ,如果锁是被一个预备事务持有的,那么为空
    other.locktype             AS previous_locktype,        --当前锁的上层锁
    other.relation::regclass   AS previous_table,
    other_stm.query            AS previous_query,
    other_stm.state               AS previous_state,
    other.mode                 AS Previous_mode,
    other.pid                  AS previous_pid,            --等待该pid完成(kill)
    other.GRANTED              AS previous_granted            --如果持有锁,为真,如果等待锁,为假
FROM
    pg_catalog.pg_locks AS waiting
JOIN
    pg_catalog.pg_stat_activity AS waiting_stm
    ON (
        waiting_stm.pid = waiting.pid
    )
JOIN
    pg_catalog.pg_locks AS other
    ON (
        (
            waiting."database" = other."database"
        AND waiting.relation  = other.relation
        )
        OR waiting.transactionid = other.transactionid
    )
JOIN
    pg_catalog.pg_stat_activity AS other_stm
    ON (
        other_stm.pid = other.pid
    )
WHERE
    NOT waiting.GRANTED
AND
    waiting.pid <> other.pid;
select * from viewlocks;

如果查询到了结果,表示该表被锁 则需要释放锁定

select  pg_cancel_backend(pid) 

select  pg_terminate_backend(pid)
参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值