Kingbase中如何安全取消长时间运行的SQL查询？

Question

在Kingbase中，当SQL查询因数据量大、索引缺失或锁等待等原因长时间运行（如超过30秒），直接终止可能引发事务不一致或会话僵死。常见问题：`pg_cancel_backend(pid)` 调用后查询仍处于“active”状态，或误杀关键后台进程导致服务异常；使用 `kill -9` 强制终止则可能破坏WAL日志连续性，引发实例恢复失败。此外，普通用户默认无权取消他人会话，而DBA执行取消操作时若未先通过 `SELECT pid, usename, application_name, state, query_start, query FROM sys_stat_activity WHERE state = 'active' AND now() - query_start > interval '60 seconds';` 精准定位目标会话，易造成误操作。更隐蔽的风险是：对持有行级锁的长事务贸然取消，可能触发级联回滚阻塞其他业务。如何在保障事务原子性、避免系统震荡的前提下，安全、精准、可审计地终止异常查询？这是Kingbase生产环境中高频且高危的运维痛点。

Answer 1

```html

一、现象识别：从“active”假象到真实阻塞链

在Kingbase（兼容PostgreSQL 10+内核）中，sys_stat_activity.state = 'active'仅表示后端进程正在执行查询，不等于事务可安全中断。常见误判场景包括：查询处于I/O等待（如大表顺序扫描）、锁等待（state = 'active'但wait_event_type = 'Lock'）、或WAL写入阻塞。需结合backend_start、state_change、wait_event三字段交叉验证。例如：

SELECT pid, usename, application_name, state, 
       query_start, state_change, wait_event_type, wait_event,
       substring(query, 1, 80) AS query_snippet
FROM sys_stat_activity 
WHERE state = 'active' 
  AND now() - query_start > INTERVAL '30 seconds'
  AND (wait_event_type IS NULL OR wait_event_type != 'Client');

二、风险建模：终止操作的四维影响矩阵

维度	低风险行为	高危行为	Kingbase特有表现
事务一致性	事务级取消（pg_cancel_backend()）	进程级强杀（kill -9）	WAL record header校验失败导致recovery hang
锁资源释放	持有锁的事务被取消后自动回滚释放	未完成回滚即终止进程	行锁残留触发deadlock_timeout误判
系统稳定性	仅中断用户会话	误杀bgwriter/walwriter等后台进程	bgwriter崩溃引发checkpoint_timeout连锁超时
审计合规性	记录pg_log中的CANCEL事件	无审计痕迹的OS级终止	默认不记录pg_cancel_backend调用者身份

三、精准定位：基于锁依赖图的会话拓扑分析

Kingbase提供sys_locks与sys_stat_activity联合视图，构建阻塞关系图。以下SQL可生成锁等待拓扑（支持递归深度≤3）：

WITH RECURSIVE lock_chain AS (
  SELECT a.pid AS blocker_pid, a.usename AS blocker_user,
         a.query AS blocker_query, 
         l2.pid AS blocked_pid, a2.usename AS blocked_user,
         1 AS depth
  FROM sys_stat_activity a
  JOIN sys_locks l1 ON a.pid = l1.pid AND l1.granted
  JOIN sys_locks l2 ON l1.locktype = l2.locktype 
    AND l1.database = l2.database 
    AND l1.relation = l2.relation 
    AND l1.page = l2.page 
    AND l1.tuple = l2.tuple 
    AND l1.virtualxid = l2.virtualxid 
    AND l1.transactionid = l2.transactionid 
    AND l1.classid = l2.classid 
    AND l1.objid = l2.objid 
    AND l1.objsubid = l2.objsubid 
    AND l2.granted = false
  JOIN sys_stat_activity a2 ON l2.pid = a2.pid
  WHERE a.state = 'active' AND a2.state = 'active'
  UNION ALL
  SELECT lc.blocker_pid, lc.blocker_user, lc.blocker_query,
         a.pid, a.usename, lc.depth + 1
  FROM lock_chain lc
  JOIN sys_stat_activity a ON lc.blocked_pid = a.pid
  JOIN sys_locks l ON a.pid = l.pid AND l.granted
  WHERE lc.depth < 3
)
SELECT * FROM lock_chain ORDER BY depth;

四、分级处置：三阶熔断策略（含Kingbase专属适配）

1. 第一阶：软中断（推荐优先执行）
   调用pg_cancel_backend(pid)，并轮询state变为'idle'或'idle in transaction (aborted)'；Kingbase v8.6+支持pg_terminate_backend()强制结束空闲事务，但对活跃查询仍需等待回滚完成。
2. 第二阶：锁感知终止
   若检测到目标会话持有AccessExclusiveLock且阻塞≥3个会话，则先执行SELECT pg_advisory_unlock_all();清理应用层锁，再取消——避免因advisory lock残留导致回滚卡死。
3. 第三阶：实例级熔断（仅限DBA应急）
   启用Kingbase独有参数kingbase.cancel_safe_mode = on（v8.7+），该模式下pg_cancel_backend()自动跳过持有RelationLock的会话，返回ERROR: cannot cancel backend holding relation lock而非静默失败。

五、可审计闭环：全链路操作留痕方案

Kingbase需通过三重机制保障审计完整性：

• 数据库层：启用log_statement = 'ddl' + log_min_duration_statement = 1000，并定制日志格式包含%u %d %p %a %x（用户/库/PID/应用名/事务ID）
• 会话层：创建AUDIT_CANCEL事件触发器，捕获所有pg_cancel_backend调用：
  CREATE OR REPLACE FUNCTION log_cancel_event() RETURNS event_trigger AS $$
BEGIN
INSERT INTO audit.cancel_log VALUES (current_user, current_database(), clock_timestamp(), tg_event, ...);
END;
$$ LANGUAGE plpgsql;
• OS层：部署auditd规则监控/proc/*/fd/下Kingbase socket文件访问，关联ppid追溯调用链

六、防御前置：长查询治理的Kingbase增强实践

graph TD
  A[SQL提交] --> B{执行计划成本 > threshold?}
  B -->|Yes| C[触发auto_explain]
  B -->|No| D[正常执行]
  C --> E[写入pg_log + 自动采样]
  E --> F[入库至performance_schema.query_history]
  F --> G[每日生成TOP10长查询报告]
  G --> H[自动推送至企业微信/钉钉]
  H --> I[DBA确认是否添加hint或重建索引]

Kingbase v8.6+支持auto_explain.log_min_duration = '1000ms'与performance_schema插件联动，将执行计划、实际耗时、I/O统计持久化存储，形成可回溯的性能基线。配合pg_hint_plan扩展，可对高频长查询注入/*+ IndexScan(t idx_t_col) */强制走索引，从源头降低取消需求频次。

```