您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 鎖定會話秒查：三條 SQL 定位阻塞源頭並精準釋放

編輯：Oracle 資料庫基礎

核心視圖關係圖

鎖定診斷三大核心視圖：

V$LOCKED_OBJECT：當前被鎖定的物件（含 SESSION_ID、LOCKED_MODE）
DBA_OBJECTS：物件名稱、類型、OWNER
V$SESSION：會話詳細資訊（SID、SERIAL#、用戶、機器、程式、SQL_ID）

查詢 1：標準鎖定全景圖（最常用）

SELECT
  lo.SESSION_ID          AS sid,
  s.SERIAL#              AS serial,
  lo.ORACLE_USERNAME     AS db_user,
  lo.OS_USER_NAME        AS os_user,
  ao.OBJECT_NAME         AS locked_object,
  ao.OBJECT_TYPE         AS obj_type,
  DECODE(lo.LOCKED_MODE,
    0, 'None',
    1, 'Null (NULL)',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share (S)',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive (X)',
    TO_CHAR(lo.LOCKED_MODE)
  )                      AS lock_mode,
  s.MACHINE,
  s.TERMINAL,
  s.PROGRAM,
  s.SQL_ID,
  s.PREV_SQL_ID
FROM V$LOCKED_OBJECT lo
JOIN DBA_OBJECTS ao     ON ao.OBJECT_ID = lo.OBJECT_ID
JOIN V$SESSION s        ON s.SID = lo.SESSION_ID
ORDER BY lo.SESSION_ID;

輸出關鍵欄位：SID、SERIAL#（殺會話必備）、LOCK_MODE（6=排他鎖最嚴重）、SQL_ID（可關聯 V$SQL 看完整語句）。

查詢 2：極簡版（僅確認是否有鎖）

SELECT s.SID, s.SERIAL#, s.USERNAME, s.MACHINE, s.PROGRAM, lo.OBJECT_ID
FROM V$SESSION s
JOIN V$LOCKED_OBJECT lo ON lo.SESSION_ID = s.SID;

殺掉阻塞會話：標準與強制兩步走

步驟 1：優雅釋放（讓事務回滾）

ALTER SYSTEM KILL SESSION 'sid,serial#';

會話狀態變為 KILLED，事務隱性回滾，鎖最終釋放。
若會話正在執行不可中斷操作（如網路等待、長跑 PL/SQL），可能**長時間停留在 KILLED 狀態不釋放鎖**。

步驟 2：OS 層面強制終結（終極手段）

取得 OS 進程/執行緒 ID（SPID）：

SELECT p.SPID, s.OSUSER, s.PROGRAM
FROM V$SESSION s
JOIN V$PROCESS p ON p.ADDR = s.PADDR
WHERE s.SID = :target_sid;

作業系統下執行：

Linux/Unix：kill -9 <SPID>（需 root 或 oracle 用戶）
Windows：Oracle 提供專用工具 orakill.exe
```
orakill <SID> <SPID>
-- 例：orakill ORCL 12345
```
注意：Windows 下 SPID 是**執行緒 ID**，不可用 Task Manager 的 PID，必須用上述查詢取得。

批量產生 Kill 指令（謹慎使用）

SELECT 'ALTER SYSTEM KILL SESSION ''' || s.SID || ',' || s.SERIAL# || ''';'
FROM V$LOCKED_OBJECT lo
JOIN V$SESSION s ON s.SID = lo.SESSION_ID;

⚠️ 警告：請逐條人工核對後執行，**嚴禁直接管道給 SQL*Plus 批量執行**，誤殺核心會話（如 PMON、SMON、備份進程）會導致資料庫掛起或重啟。

進階：找出「阻塞鏈」根節點

若多會話互相等待，需找到鏈條最上游的 Blocker：

SELECT
  blocking_session AS blocker_sid,
  s1.USERNAME      AS blocker_user,
  s1.SQL_ID        AS blocker_sql,
  s2.SID           AS waiter_sid,
  s2.USERNAME      AS waiter_user,
  s2.SQL_ID        AS waiter_sql,
  l.TYPE           AS lock_type,
  l.LMODE          AS blocker_mode,
  l.REQUEST        AS waiter_mode
FROM V$LOCK l
JOIN V$SESSION s1 ON s1.SID = l.SID
JOIN V$SESSION s2 ON s2.SID = l.BLOCKING_SESSION
WHERE l.BLOCKING_SESSION IS NOT NULL
  AND l.REQUEST > 0;

優先處理 BLOCKER_SID，連鎖釋放所有 Waiter。

預防勝於治療：應用層面最佳實踐

縮短事務邊界：只在必要時開啟交易，盡快 COMMIT/ROLLBACK。
固定存取順序：多表操作時，所有程式遵循相同表順序（如先父表後子表），避免死結。
使用 SELECT ... FOR UPDATE NOWAIT/SKIP LOCKED：避免無限等待，應用層捕獲異常重試或提示用戶。
監控告警：針對 V$SESSION_WAIT 中 EVENT = 'enq: TX - row lock contention' 且 SECONDS_IN_WAIT > 30 設定告警。