kingbase数据库解决执行更新字段时锁表问题

原创于 2025-12-10 17:57:21 发布 · 84 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #kingbase

2025博客之星年度评选已开启 10w+人浏览 454人参与

在终止时，请查看执行的语句是否对数据有影响，谨慎操作

--查看哪些表锁定了，查询到后复制其pid，可进行以下操作
SELECT 
    relname AS table_name,
    l.pid,
    mode,
    granted,
    age(now(), query_start) AS duration
FROM sys_locks l
JOIN pg_class c ON l.relation = c.oid
JOIN sys_stat_activity a ON l.pid = a.pid
WHERE l.mode = 'AccessExclusiveLock'
  AND c.relkind = 'r'  -- 只查看普通表
ORDER BY duration DESC;

--查看1638594进程的详细信息
-- 查看等待锁的进程详情
SELECT 
    pid,
    usename,
    datname,
    application_name,
    client_addr,
    state,
    query,
    now() - query_start AS waiting_time,
    pg_blocking_pids(pid) AS blocking_pids
FROM sys_stat_activity
WHERE pid = 1638594;

SELECT pid, usename, query, state, now() - query_start 
FROM sys_stat_activity 
WHERE pid = 1638594;

-- 优雅终止，允许事务回滚
SELECT pg_cancel_backend(1638594);

-- 强制终止，立即断开连接
SELECT pg_terminate_backend(1638594);