Oracle 数据库中终结锁定进程的 SQL 实践-CSDN博客

本文链接：https://blog.csdn.net/i042416/article/details/147961508

在 Oracle 数据库环境下，当某些会话因未提交事务或执行长时间操作而保持对表或行的锁定时，其他用户将因资源等待而无法继续正常工作。通过查询 V$SESSION、V$LOCK 等动态性能视图，可以明确锁冲突的阻塞者和被阻塞者；随后针对性地执行 ALTER SYSTEM KILL SESSION 'SID,SERIAL#' [IMMEDIATE] 或者 ALTER SYSTEM DISCONNECT SESSION 'SID,SERIAL#' POST_TRANSACTION，由 PMON 进程负责回滚事务并释放锁，从而恢复数据库并发性能(Oracle Docs, Oracle)。

一、锁和会话基础

Oracle 会话（session）是用户与数据库实例之间的连接实体，承载 SQL 执行和事务管理等功能(Oracle Docs)。锁（lock）机制在事务隔离和数据一致性中扮演关键角色，各种 DML 操作会在相应资源（如表、行）上加锁，以避免并发修改导致的不一致现象(DG Support)。当一个会话占有锁且未提交，其他会话若尝试访问同一资源，就会进入等待状态，甚至演化为死锁（deadlock）或长时间等待，影响系统可用性与性能(DG Support)。

动态性能视图 V$SESSION 提供所有当前会话的详细信息，包括 SID、SERIAL#、USERNAME、STATUS 等字段，可用于标识并分析目标会话(Oracle Docs)。而 V$LOCK 视图记录会话持有或请求的锁资源信息，通过与 V$SESSION 关联，可以了解锁的类型、模式以及会话状态(oracle-base.com)。

事务管理视图 V$TRANSACTION 帮助追踪未提交的事务，包括事务 ID、关联会话等，为锁冲突排查提供更多上下文信息(Oracle)。了解这些基础视图，有助于锁冲突的溯源，从而针对性地执行后续会话清理。

二、定位锁定会话

要准确找到导致锁冲突的会话，需要综合多张视图信息。通过交叉查询，能够清晰区分阻塞者和被阻塞者。

2.1 查找阻塞者会话

通过以下 SQL 查询，可获取正在等待资源且持有锁的阻塞者会话列表：

SELECT
    l1.SID AS blocker_sid,
    l1.SERIAL# AS blocker_serial,
    s1.USERNAME AS blocker_user,
    l1.TYPE AS lock_type
FROM
    V$LOCK l1
JOIN
    V$SESSION s1
ON
    l1.SID = s1.SID
WHERE
    l1.BLOCK = 1;

该查询以 V$LOCK 中 BLOCK=1 标识持有锁的会话为条件，再联结 V$SESSION，获取对应用户及会话信息(DG Support)。

2.2 查找被阻塞者会话

被阻塞者会话则位于等待锁定资源的状态，通过以下语句获取：

SELECT
    l2.SID AS waiter_sid,
    l2.SERIAL# AS waiter_serial,
    s2.USERNAME AS waiter_user,
    l2.TYPE AS wait_lock_type
FROM
    V$LOCK l2
JOIN
    V$SESSION s2
ON
    l2.SID = s2.SID
WHERE
    l2.REQUEST > 0;

V$LOCK 中 REQUEST>0 表示该会话正在等待未获取的锁资源，同样联结 V$SESSION 显示上下文详情(oracle-base.com)。

2.3 综合排查死锁

当出现死锁时，Oracle 会自动检测并选择一个“受害者”会话回滚事务，以解除循环等待。但在一些极端场景，管理员仍需手动干预，通过以下查询了解死锁情况：

SELECT * FROM V$LOCKED_OBJECT;

联结 DBA_OBJECTS 等视图，可进一步定位具体对象被锁定的行或表(DG Support)。

三、终止会话的 SQL 语句

确认目标会话后，可使用多种命令强制终止会话并释放所持锁。

3.1 ALTER SYSTEM KILL SESSION

核心命令格式为：

ALTER SYSTEM KILL SESSION 'sid,serial#' [IMMEDIATE];

若不加 IMMEDIATE，会话会被标记为 KILLED，由 PMON 在适当时机回滚事务并清理资源(Oracle Forums)。添加 IMMEDIATE 参数后，Oracle 尝试立刻断开会话，对应语法示例：

ALTER SYSTEM KILL SESSION '39,23' IMMEDIATE;

该语句将用户 session SID=39、SERIAL#=23 的会话立即终止，并释放其持有的锁资源(Oracle Docs)。

3.2 ALTER SYSTEM DISCONNECT SESSION

从 Oracle 11gR2 开始，新增 DISCONNECT SESSION 子句，可指定终止策略：

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

POST_TRANSACTION 会让会话在完成当前事务后断开，而 IMMEDIATE 则尝试立即断开，但不保证回滚完成再断开；相应的资源清理由 PMON 或 SMON 进程处理(Oracle Docs)。

3.3 PL/SQL 自动化脚本

如果需批量杀会话，可以编写 PL/SQL 区块，结合循环与动态 SQL 实现。例如，要终结所有 TEST_USER 的会话：

BEGIN
  FOR rec IN (
    SELECT sid, serial# FROM v$session WHERE username = 'TEST_USER'
  )
  LOOP
    EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || rec.sid || ',' || rec.serial# || ''' IMMEDIATE';
  END LOOP;
END;
/

此脚本遍历指定用户的所有会话，并逐个执行立即终止命令，简化批量操作(Stack Overflow, Ask Tom)。

四、注意事项与最佳实践

在生产环境下执行杀会话操作时，需谨慎评估业务影响。终止会话会导致未提交事务全部回滚，可能触发大量数据重演，给 PMON 带来较高负载，影响其他业务进程(Ask Tom)。为此，可采用以下策略：

与应用团队或业务负责人沟通，明确被中断会话的业务上下文，再决定是否执行杀会话操作。
在高峰期避免频繁杀会话，必要时结合资源监控工具评估系统负载后再行操作。
针对 RAC 环境，可在命令中指定实例 ID：
```
ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
```
实现跨实例精确定位并终结会话(Oracle Docs)。
如对业务有较高可用性要求，可先使用 DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION，让会话平滑结束，再在确认无异常后执行 IMMEDIATE。

五、小结

通过查询 V$SESSION、V$LOCK、V$TRANSACTION 等动态性能视图，可精准定位导致锁阻塞的会话；随后使用 ALTER SYSTEM KILL SESSION 或 ALTER SYSTEM DISCONNECT SESSION 等 SQL 语句执行终结操作，由 PMON/SMON 回滚事务并释放锁，从而恢复数据库并发能力。在执行过程中，应注意业务影响与资源负载，通过批量脚本及 RAC 实例 ID 精细化管理，进一步提升运维效率。