gaussdb 解决锁表问题

最新推荐文章于 2024-10-13 10:01:25 发布

jxw201615060094

最新推荐文章于 2024-10-13 10:01:25 发布

阅读量4.5k

点赞数 4

文章标签：数据库

本文链接：https://blog.csdn.net/w201615060094/article/details/124021399

版权

--gaussdb 解决锁表问题
select oid from pg_class where relname='表名';
select pid from pg_locks where relation = 'oid';
select query_id from pg_stat_activity where pid = 'pid';
select pgxc_terminate_query('query_id');

CREATE OR REPLACE PROCEDURE pgxc_terminate_query(IN queryId TEXT)
AS    
    nodeName TEXT;
    kpid TEXT;
    query_str TEXT;
BEGIN
    FOR nodeName, kpid IN  SELECT node_name, tid  FROM  pgxc_thread_wait_status WHERE query_id = queryId LOOP 
       query_str  = 'EXECUTE DIRECT ON ('|| nodeName||') '' SELECT  PG_TERMINATE_BACKEND('''''||kpid||''''')'';'; 
       --dbms_output.put_line(query_str); 
       EXECUTE query_str;
    END LOOP;
END;