Oracle 死鎖排查實戰：從 V$ 視圖定位到會話終結的標準化流程

死鎖 vs 阻塞：概念釐清

• 阻塞：Session A 持有鎖，Session B 等待同一資源。**單向等待**，時間久會轉為死鎖或超時。
• 死鎖：Session A 持有資源 1 等資源 2，Session B 持有資源 2 等資源 1。**循環等待**，Oracle 自動偵測並犧牲一邊 (ORA-00060)，回滾其事務。

本文著重 **阻塞排查與手動干預**；死鎖由 Oracle 自動處理，僅需分析 Trace 文件 (`USER_DUMP_DEST`) 找出根因 SQL。

標準排查三步走 (方法一：物件導向)

1. 找被鎖物件

SELECT * FROM v$db_object_cache
WHERE owner = 'SCHEMA_NAME' AND locks != '0';

2. 找持有鎖的 Session (SID)

SELECT * FROM v$access
WHERE owner = 'SCHEMA_NAME' AND object = 'OBJECT_NAME_FROM_STEP1';

3. 獲取 SID、SERIAL#、OS Process ID (SPID)

SELECT sid, serial#, paddr FROM v$session WHERE sid = ;
SELECT spid FROM v$process WHERE addr = ;

4. 終結會話 (依序)

-- 4.1 資料庫層面優雅終結 (觸發回滾)
ALTER SYSTEM KILL SESSION ',';

-- 4.2 若標記為 KILLED 但未釋放 (長回滾)，OS 層面強制殺進程
-- Linux/Unix:
kill -9 
-- Windows (需 Oracle 安裝目錄 bin 下的 orakill.exe):
orakill  

高效單查詢法 (方法二：V$LOCKED_OBJECT 聯查)

-- 一條 SQL 直接拿到所有關鍵資訊
SELECT
  s.username,
  lo.object_id,
  lo.session_id  AS sid,
  s.serial#,
  lo.oracle_username,
  lo.os_user_name,
  lo.process     AS os_pid
FROM v$locked_object lo
JOIN v$session s ON lo.session_id = s.sid
WHERE s.username IS NOT NULL;

輸出即含 `SID, SERIAL#, OS_PID`，直接套用上述 `KILL SESSION` / `kill -9`。

進階：找出阻塞鏈 (誰在阻塞誰)

SELECT
  blocking.sid       AS blocker_sid,
  blocked.sid        AS blocked_sid,
  blocking.sql_id    AS blocker_sql_id,
  blocked.sql_id     AS blocked_sql_id,
  blocking.module    AS blocker_module,
  blocked.module     AS blocked_module
FROM v$session blocking
JOIN v$session blocked ON blocking.sid = blocking.sid
WHERE blocking.blocking_session = blocked.sid;  -- 11g+ 欄位

死鎖 Trace 分析 (事後根因)

1. 告警日誌出現 `ORA-00060: deadlock detected`，記錄 Trace 檔路徑。
2. 打開 Trace，關注 **Deadlock graph** 區塊：顯示雙方持有/等待的鎖模式 (TX/ TM)、Rowid、SQL 文本。
3. 典型模式：TX - Row Exclusive (TX) 互相等待 → 應用邏輯更新順序不一致導致。
4. 修正：統一多表更新順序 (按表名/主鍵排序)、縮短事務、避免長事務中查詢未提交資料。

避坑與最佳實踐

結語

掌握 `V$LOCKED_OBJECT` + `V$SESSION` + `V$PROCESS` 聯查單 SQL 定位阻塞源頭，配合 `KILL SESSION` → `OS KILL` 兩階段終結流程，是 DBA 日常運維的核心技能。長遠治本在於 **應用層統一資源訪問順序、縮短事務邊界、合理索引設計**。