Oracle 鎖機制與表分區策略深度解析

前言

鎖與分區是 Oracle 併發控制與大數據管理的兩大核心支柱。本文詳細剖析行級鎖、表級鎖（共享/獨占/行共享）、WAIT/NOWAIT/SKIP LOCKED 子句，以及四大分區類型（範圍、雜湊、複合、清單）與分區維護操作，提供實戰可用的完整語法範例。

一、鎖機制：併發控制的基石

1.1 行級鎖

Oracle 預設以行級鎖實現最高併發度，僅鎖定被修改的資料列，讀取不阻塞寫入，寫入不阻塞讀取（多版本一致性讀取）。

演示場景：兩用戶更新同一列

-- 建立測試用戶與權限
CONNECT system/manager;
CREATE USER user2 IDENTIFIED BY chenmp;
GRANT CREATE SESSION TO user2;
GRANT SELECT, INSERT, UPDATE, DELETE ON testlock TO user2;

-- Session 1 (user2) 更新 id=8，未提交
CONNECT user2/chenmp;
UPDATE yyaccp.testlock SET name = 'wrong' WHERE id = 8;

-- Session 2 (yyaccp) 嘗試更新同一列 → 等待
CONNECT yyaccp/accp;
UPDATE testlock SET name = 'jeff wrong' WHERE id = 8; -- 阻塞等待

-- Session 1 COMMIT 或 ROLLBACK 後，Session 2 才繼續

1.2 FOR UPDATE 子句：主動鎖定查詢列

• FOR UPDATE NOWAIT：立即失敗 (ORA-00054) 若資源被鎖。
• FOR UPDATE WAIT n：最多等待 n 秒再失敗（僅限 SELECT）。
• FOR UPDATE SKIP LOCKED (11g+)：跳過已鎖定列，適合工作佇列模式。

-- Session 1
CONNECT user2/chenmp;
SELECT * FROM yyaccp.testlock WHERE id = 8 FOR UPDATE;

-- Session 2 (另一視窗)
CONNECT yyaccp/accp;
SELECT * FROM testlock WHERE id = 8 FOR UPDATE WAIT 5; -- 等待 5 秒
-- 或
SELECT * FROM testlock WHERE id = 8 FOR UPDATE NOWAIT; -- 立即報錯

1.3 表級鎖：LOCK TABLE

手動鎖定整張表，模式決定相容性：

範例：SHARE MODE 阻塞 DML

-- Session 1
CONNECT user2/chenmp;
LOCK TABLE yyaccp.testlock IN SHARE MODE;

-- Session 2
CONNECT yyaccp/accp;
DELETE FROM testlock; -- 阻塞，直到 Session 1 COMMIT/ROLLBACK

多用戶同鎖 SHARE MODE 仍可共存，但任何 DML 會被阻塞

CONNECT yyaccp/accp;  LOCK TABLE testlock IN SHARE MODE;
CONNECT user2/chenmp; LOCK TABLE testlock IN SHARE MODE;
CONNECT user1/user1;  UPDATE testlock SET name='ping' WHERE id=8; -- 阻塞
-- 需 yyaccp 與 user2 皆 COMMIT/ROLLBACK 釋放鎖後，user1 才能繼續

ROW SHARE MODE 允許同表不同列並發更新

-- Session 1
CONNECT user2/chenmp;
LOCK TABLE yyaccp.testlock IN ROW SHARE MODE;
UPDATE yyaccp.testlock SET name='ping' WHERE id=8; -- 成功

-- Session 2
CONNECT yyaccp/accp;
UPDATE yyaccp.testlock SET name='ping' WHERE id=8; -- 阻塞（同列）
UPDATE yyaccp.testlock SET name='wrong' WHERE id=7; -- 成功（不同列）

獨占鎖：最高排他性

LOCK TABLE yyaccp.testlock IN EXCLUSIVE MODE NOWAIT;

二、表分區：大數據管理的利器

分區將大表切分為較小、可獨立管理的片段，提升查詢效能（分區裁剪）、維護彈性（分區級匯入/匯出/截斷/移動）與可用性。

2.1 範圍分區

依欄位值範圍對應分區，常用於日期、序號。

依年齡分區

DROP TABLE employeeinfo;
CREATE TABLE employeeinfo (
  empid     NUMBER(5),
  emp_name  VARCHAR2(20) NOT NULL,
  age       NUMBER(10)
)
PARTITION BY RANGE (age) (
  PARTITION p1 VALUES LESS THAN (20),
  PARTITION p2 VALUES LESS THAN (30),
  PARTITION p3 VALUES LESS THAN (40)
);

依出生日期分區

DROP TABLE personinfo CASCADE CONSTRAINTS;
CREATE TABLE personinfo (
  person_id   NUMBER(5),
  person_name VARCHAR2(20),
  birthday    DATE NOT NULL
)
PARTITION BY RANGE (birthday) (
  PARTITION p1 VALUES LESS THAN (TO_DATE('1978-10-10','YYYY-MM-DD')),
  PARTITION p2 VALUES LESS THAN (TO_DATE('1980-05-01','YYYY-MM-DD')),
  PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

2.2 雜湊分區

對分區鍵雜湊運算均勻分布，適合無明顯範圍特徵的鍵值，減少 I/O 熱點。

DROP TABLE my_emp CASCADE CONSTRAINTS;
CREATE TABLE my_emp (
  empno NUMBER(4),
  ename VARCHAR2(15)
)
PARTITION BY HASH (empno) (
  PARTITION part_1,
  PARTITION part_2
);

2.3 複合分區：範圍 + 雜湊

先範圍分區，再於各分區內雜湊子分區，兼具管理便利與並行效能。

DROP TABLE employees CASCADE CONSTRAINTS;
CREATE TABLE employees (
  emp_id     VARCHAR2(5),
  work_date  DATE NOT NULL,
  salary     NUMBER(10)
)
PARTITION BY RANGE (salary)
SUBPARTITION BY HASH (emp_id)
SUBPARTITIONS 5 (
  PARTITION p1 VALUES LESS THAN (1500),
  PARTITION p2 VALUES LESS THAN (2000),
  PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

2.4 清單分區

明確指定離散值對應分區，適合業務分類（如產品類型）。

DROP TABLE product CASCADE CONSTRAINTS;
CREATE TABLE product (
  product_id   NUMBER(4),
  product_type VARCHAR2(20),
  product_name VARCHAR2(50)
)
PARTITION BY LIST (product_type) (
  PARTITION p1 VALUES ('book'),
  PARTITION p2 VALUES ('cd', 'dvd')
);

三、分區維護操作

四、分區資料字典查詢

-- 表分區資訊
SELECT * FROM user_tab_partitions;

-- 索引分區資訊
SELECT * FROM user_ind_partitions;

五、總結與選型建議

• 鎖策略：預設行級鎖滿足 99% 場景；批次報表/結構變更才用 LOCK TABLE；高併發佇列用 FOR UPDATE SKIP LOCKED。
• 分區類型選擇：
  • 時間序列/流水號 → 範圍分區
  • 無序鍵值/需均勻分布 → 雜湊分區
  • 時間+高併發存取 → 複合分區 (Range-Hash)
  • 業務枚舉值（地區/類型/狀態） → 清單分區
• 維護窗口：利用分區截斷/交換實現秒級資料歸檔與載入，避免大量 DELETE/INSERT 產生 Redo/Undo 壓力。
• 局部索引優先：分區表建議優先使用局部分區索引，維護彈性最高；全域索引僅用於跨分區唯一性約束。

深入理解鎖與分區，是打造高併發、可擴展 Oracle 系統的關鍵能力。