如何查询并释放指定表的锁-云原生数据仓库AnalyticDB-阿里云-云原生数据仓库AnalyticDB(AnalyticDB)-阿里云帮助中心

查看指定表上当前锁持有情况和对应持有锁的SQL

命令如下：

select * from gp_toolkit.gp_locks_on_relation where lorrelname='<table>';

如果需要结束查询来释放锁，可以通过select pg_terminate_backend(lorpid)来结束，示例如下。

查询锁信息，根据返回结果中的 lorpid 列找到持锁进程 ID，使用 pg_terminate_backend 终止该进程后再次查询确认锁已释放。

i17adb=# select * from gp_toolkit.gp_locks_on_relation where lorrelname='t1';
 lorlocktype | lordatabase | lorrelname | lorrelation | lortransaction | lorpid |    lormode      | lorgranted |  lorcurrentquery
------------+-------------+------------+-------------+----------------+--------+-----------------+------------+---------------------
 relation   |    16392    | t1         |       16407 |                | 106099 | AccessShareLock | t          | select * from tn1.t1;
(1 row)
i17adb=# select pg_terminate_backend(106099);
 pg_terminate_backend
----------------------
 t
(1 row)
i17adb=# select * from gp_toolkit.gp_locks_on_relation where lorrelname='t1';
 lorlocktype | lordatabase | lorrelname | lorrelation | lortransaction | lorpid | lormode | lorgranted | lorcurrentquery
------------+-------------+------------+-------------+----------------+--------+---------+------------+-----------------
(0 rows)