How to check Postgres DB lock problem

最新推荐文章于 2021-08-27 10:10:00 发布

忽略的爱

最新推荐文章于 2021-08-27 10:10:00 发布

阅读量256

点赞数

分类专栏： PostgreSQL

本文链接：https://blog.csdn.net/huluedeai/article/details/104568466

版权

本文介绍了如何检查Postgres数据库的锁问题，包括通过pg_locks视图查看已授予的锁和等待锁的进程，以及结合pg_stat_activity视图来确定持有或等待锁的进程。

摘要由CSDN通过智能技术生成

How to check Postgres DB lock problems

当我们遇到Postgres DB lock的时候，我们想知道是那些表被lock住了，用以下的SQL可以得到我们想要的信息。
pg_locks View
Looking at pg_locks shows you what locks are granted and what processes are waiting for locks to be acquired. A good query to start looking for lock problems.

SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;

在这里插入图片描述
pg_stat_activity view
Figuring out what the processes holding or waiting for locks is easier if you cross-reference against the information in pg_sta_activity.

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,
         bl