postgresql 表锁定问题处理

问题描述

数据库中某张表无法查询以及字段设计修改，查询访问显示超时

分析与处理

1、查询被锁定的表

--查询被锁住的进程
SELECT
    pg_class.relname AS table_name,
    pg_locks.mode AS lock_mode
FROM pg_locks
         JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE pg_locks.granted = false
  AND pg_class.relkind = 'r'
ORDER BY pg_class.relname;

2、语句解析

这个 SQL 查询语句用来查看正在被锁定但是没有被授权的关系型表的名称和锁定模式。其含义如下：
（1）pg_class.relname AS table_name：将pg_class系统目录表中的relname列（关系表的名称）重命名为table_name。
（2）pg_locks.mode AS lock_mode：将pg_locks系统视图中的mode列（锁定模式）重命名为lock_mode。
（3）JOIN pg_class ON pg_locks.relation = pg_class.oid：将pg_locks.relation列（对象 ID）与pg_class.oid列（关系表的对象 ID）连接起来，以获取关系表的名称。
（4）WHERE pg_locks.granted = false AND pg_class.relkind = 'r'：过滤出未被授予锁定权限的关系型表，并根据关系表类型（relkind）的值为r（关系型表）进行筛选。
（5）ORDER BY pg_class.relname：按照关系表的名称进行排序。

这个查询语句通常用于排查数据库中锁定操作导致的性能问题。如果发现某个表处于锁定状态，您可以尝试找出具有锁定权限的连接并与他们联系，以释放表锁以及解决问题。

消除锁定进程

SELECT pg_cancel_backend(pid) FROM pg_locks WHERE relation='table_name'::regclass;

这段 SQL 语句的含义是查询 t_service_obj 表上的锁，并取消持有该锁的后台进程。pg_locks 是一个系统表，它用于跟踪数据库中的锁定情况。pid 是一个列，它包含了锁定持有进程的 ID。pg_cancel_backend(pid) 函数用于结束指定进程的执行。在这个例子中，如果某个后台进程正在持有 t_service_obj 表上的锁，那么该进程的执行将被结束。