Oracle 資料完整性與鎖機制深度解析

事務、鎖、完整性約束是 Oracle 並發控制與資料一致性的三大支柱。本文系統整理 ACID 特性、隔離級別、鎖類型、死鎖處理與約束最佳實踐。

一、事務與 ACID

• Atomicity：全做或全不做。
• Consistency：事務前後資料庫滿足所有約束。
• Isolation：並發事務互不干擾。
• Durability：Commit 後永久生效。

事務結束：COMMIT (生效) / ROLLBACK (撤銷) / DDL 隱含提交 / Session 斷開隱含回滾。

二、隔離級別與讀一致性

Oracle 以多版本並發控制 (MVCC) 實現 Statement-Level Read Consistency (預設) 與 Transaction-Level Read Consistency (SET TRANSACTION ISOLATION LEVEL SERIALIZABLE)。

三、鎖機制

1. DML 鎖 (資料鎖)

• TM 鎖 (表級)：ROW SHARE (RS)、ROW EXCLUSIVE (RX)、SHARE (S)、SHARE ROW EXCLUSIVE (SRX)、EXCLUSIVE (X)。
  SELECT ... FOR UPDATE → RX；INSERT/UPDATE/DELETE → RX；LOCK TABLE ... EXCLUSIVE → X。
• TX 鎖 (行級/事務鎖)：每個 DML 事務持有唯一 TX 鎖，指向具體資料行。其他事務修改同行會等待 TX 鎖釋放。

2. DDL 鎖 (字典鎖)

• 排他 DDL 鎖：CREATE/DROP/ALTER TABLE，阻擋所有其他 DDL/DML。
• 共享 DDL 鎖：CREATE VIEW、編譯 PL/SQL，允許並發 DML、共享 DDL。
• 可中斷解析鎖：Library Cache 中 SQL/PLSQL 引用物件的鎖，DDL 變更物件時自動中斷導致重解析。

3. 內部鎖

Latch、Mutex、Buffer Pin 等保護 SGA 共享結構，使用者不可見。

四、死鎖與處理

Oracle 自動偵測死鎖 (ORA-00060)，自動回滾其中一個事務 (Statement-Level Rollback)。

手動排查步驟

-- 1. 查封鎖鏈
SELECT * FROM v$lock WHERE block = 1;

-- 2. 關聯 Session
SELECT s.sid, s.serial#, s.username, s.machine, s.program, l.type, l.id1, l.id2
FROM v$lock l, v$session s
WHERE l.sid = s.sid AND l.block = 1;

-- 3. Kill Session
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 4. OS 層強殺 (最後手段)
orakill SID spid  -- Windows
kill -9 spid      -- Linux

五、完整性約束最佳實踐

六、並行處理適用場景

• 大表 (＞100 萬行) 全表掃描、大索引建立、大量載入、彙總計算。
• 需 SMP/MPP 架構、多磁盤分佈、大量排序/雜湊記憶體操作。

理解 MVCC、鎖層級與約束權衡，是寫出高併發高可靠 Oracle 應用的關鍵。