Oracle 會話鎖定查殺實戰：V$SESSION + V$LOCK 精準定位，ALTER SYSTEM KILL SESSION 秒級釋放

業務報「應用卡住」，DBA 需在 30 秒內定位阻塞會話並釋放。本文提供生產級標準化查殺腳本，覆蓋鎖類型識別、阻塞鏈追蹤、安全殺會話、事務回滾預估。

一、標準查鎖 SQL（含阻塞鏈）

COL blocking_sid FOR 99999
COL blocked_sid  FOR 99999
COL lock_type    FOR A20
COL mode_held    FOR A12
COL mode_req     FOR A12
COL sql_text     FOR A80

SELECT
  l1.sid        AS blocking_sid,
  s1.username   AS blocker_user,
  s1.machine    AS blocker_machine,
  s1.program    AS blocker_program,
  l1.type       AS lock_type,
  DECODE(l1.lmode, 0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',4,'Share',5,'S/Row-X (SSX)',6,'Exclusive',l1.lmode) AS mode_held,
  l2.sid        AS blocked_sid,
  s2.username   AS blocked_user,
  s2.machine    AS blocked_machine,
  DECODE(l2.request, 0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',4,'Share',5,'S/Row-X (SSX)',6,'Exclusive',l2.request) AS mode_req,
  sq.sql_text
FROM v$lock l1
JOIN v$session s1 ON l1.sid = s1.sid
JOIN v$lock l2 ON l1.id1 = l2.id1 AND l1.id2 = l2.id2
JOIN v$session s2 ON l2.sid = s2.sid
LEFT JOIN v$sql sq ON s2.sql_address = sq.address AND s2.sql_hash_value = sq.hash_value
WHERE l1.block = 1
  AND l2.request > 0
  AND l1.sid != l2.sid
ORDER BY l1.sid, l2.sid;

關鍵欄位解讀：
• BLOCKING_SID：持有鎖的會話（阻塞源）。
• BLOCKED_SID：等待鎖的會話（受害者）。
• LOCK_TYPE：TM (DML Enqueue)、TX (Transaction)、UL (User Lock)、DX (Distributed Transaction) 等。
• MODE_HELD/REQ：6=Exclusive (X) 最常見於行鎖衝突。

二、按終端/用戶/程序快速過濾（如原文示例）

SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine, s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
  AND s.username IS NOT NULL
  AND l.type IN ('TX','TM')  -- 僅關注事務/DML 鎖
  AND s.terminal = 'CHINA-C742905EE';  -- 可替換為 machine/osuser/program

三、安全殺會話三步法

1. 確認業務影響：聯繫應用擁有者，評估該會話執行的業務邏輯、事務大小、是否可重入。
2. 執行殺會話
   ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
IMMEDIATE 關鍵字（10g+）讓 PMON 立即回滾事務並釋放鎖，無需等待客戶端發現連接斷開。
3. 驗證鎖釋放：再次運行查鎖 SQL，確認阻塞鏈消失；檢查 V$TRANSACTION 確認回滾完成。

四、進階：避免誤殺與自動化

• 識別長事務：SELECT sid, start_time, used_ublk, used_urec FROM v$transaction WHERE used_ublk > 10000; 大事務殺掉回滾時間長，需權衡。
• 只殺阻塞源：優先殺 BLOCKING_SID，被阻塞會話通常自動繼續。
• 自動化腳本（Python + cx_Oracle）：
  每分鐘掃描 V$LOCK，若阻塞超過 5 分鐘且阻塞會話非核心批處理（可維護白名單表），自動 KILL SESSION IMMEDIATE 並發送企業微信/釘釘告警。
• DDL 阻塞特例：TM 鎖模式 3/4/5/6 可能由未提交 DDL 或 LOCK TABLE 引起，殺會話前確認是否為維護窗口操作。

五、原文示例補充：完整殺會話命令

-- 查詢被鎖會話（按終端過濾）
SELECT s.sid||','||s.serial#, s.username, s.machine, s.terminal, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
  AND s.username IS NOT NULL
  AND s.terminal = 'CHINA-C742905EE';

-- 殺掉具體會話（假設查得 SID=383, SERIAL#=32773）
ALTER SYSTEM KILL SESSION '383,32773' IMMEDIATE;

建立「查鎖→評估→殺會話→驗證→記錄」標準化 SOP，納入值班手冊，讓初級 DBA 也能在壓力下精準、安全地處理鎖衝突事件。