Lock wait prevention-AnalyticDB(AnalyticDB)-阿里云帮助中心

View the locks held on a specified table and the SQL statements that hold the locks

Run the following command:

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

If you need to end a query to release a lock, you can run select pg_terminate_backend(lorpid). The following example shows how to do this.

Query the lock information, find the PID of the process that holds the lock from the lorpid column in the returned result, and run pg_terminate_backend to terminate the process. Then query the lock information 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)