Oracle 鎖機制深度解析：DML 鎖、等待鏈分析與死鎖處理實戰

鎖基礎

資料庫並發控制核心技術。兩大基本鎖模式：

• 排他鎖 (X Lock)：寫操作持有，阻擋其他讀/寫。
• 共享鎖 (S Lock)：讀操作持有，允許並發讀、阻擋寫。

Oracle DML 鎖分類

機制：執行 DML → 自動申請 TM 鎖 → 獲得後申請 TX 鎖 → 標記行鎖旗標。此設計避免逐行檢查鎖相容性，大幅提升效率。

常見 TM 鎖模式對應 SQL

TX 鎖等待監控與解決

關鍵動態視圖

• V$LOCK：當前鎖持有/等待狀態 (TYPE='TX', REQUEST>0 表示等待)。
• V$SESSION：等待事件 enq: TX - row lock contention。
• V$SESSION_WAIT / V$WAIT_CHAINS (11g+)：等待鏈分析。
• DBA_BLOCKERS / DBA_WAITERS：舊版簡易視圖。

鎖等待處理流程

1. 識別阻塞會話：SELECT * FROM v$lock WHERE request>0 AND type='TX'; 取得 BLOCK=1 的持有者 SID。
2. 查詢持有者 SQL：SELECT sql_text FROM v$sql WHERE sql_id = (SELECT sql_id FROM v$session WHERE sid = :blocker_sid);
3. 判斷業務合理性：長交易、忘記 Commit、邏輯死循環。
4. 處置：通知應用 Commit/Rollback、或 DBA 執行 ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; 強制釋放。

死鎖 (ORA-00060) 分析與解決

典型死鎖模式

-- Session 1
UPDATE accounts SET bal=bal-100 WHERE id=1;  -- 鎖定行 1
UPDATE accounts SET bal=bal+100 WHERE id=2;  -- 等待行 2

-- Session 2
UPDATE accounts SET bal=bal-50 WHERE id=2;   -- 鎖定行 2
UPDATE accounts SET bal=bal+50 WHERE id=1;   -- 等待行 1 → 死鎖

追蹤與定位

1. Alert Log 記錄：ORA-00060: deadlock detected while waiting for resource 並產生 *_ora_.trc 追蹤檔。
2. 追蹤檔關鍵段：Deadlock graph 列出雙方持有/等待資源、SQL 文本、物件 ID。
3. 11g+ V$WAIT_CHAINS 可即時查詢等待鏈環路。

預防措施

• 應用層**統一資源存取順序** (如永遠先鎖 ID=1 再鎖 ID=2)。
• 縮短交易長度，儘早 Commit。
• 避免在交易中執行耗時邏輯/外部呼叫。
• 適當使用 SELECT ... FOR UPDATE NOWAIT 或 SKIP LOCKED (11g+) 非阻塞鎖定。
• 啟用 LOCK_TIMEOUT (12.2+) 設定鎖等待上限。

結語

掌握 TM/TX 雙層鎖架構、善用 V$LOCK/V$WAIT_CHAINS 即時診斷、建立應用層統一存取順序規範，是解決 Oracle 鎖爭用與死鎖的系統性方法論。