Prevent lock waits-AnalyticDB(AnalyticDB)-阿里云帮助中心

View lock holders and queries

Run the following query:

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

If you need to release a lock, you can terminate the process holding it by using theselect pg_terminate_backend(lorpid) function.

Query for lock information to find the process ID in the lorpid column. Use pg_terminate_backend to terminate the process, and then query again to confirm that the lock has been released.

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)