測試環境建立
CREATE TABLE test (a NUMBER, b NUMBER);
ALTER TABLE test ADD CONSTRAINT pk_a PRIMARY KEY (a);
CREATE TABLE test_child (c NUMBER, a NUMBER NOT NULL CONSTRAINT pk_a_ref REFERENCES test(a));
鎖類型對照表
| 鎖類型 | 代碼 | 模式 | 說明 |
|---|
| Transaction Lock | TX | 6 (Exclusive) | 交易排他鎖，每個 DML 交易持有 |
| Table Lock (Intent) | TM | 2 (Row Share - RS) | 表級共享意向鎖（SELECT FOR UPDATE） |
| Table Lock (Intent) | TM | 3 (Row Exclusive - RX) | 表級排他意向鎖（INSERT/UPDATE/DELETE） |
| Table Lock (Intent) | TM | 4 (Share - S) | 表級共享鎖（CREATE INDEX 等） |
| Table Lock (Intent) | TM | 6 (Exclusive - X) | 表級排他鎖（DDL、DROP TABLE） |
關鍵驗證場景
1. 未提交 INSERT/UPDATE
- 產生 TM (RX, mode 3) + TX (X, mode 6)
- COMMIT 後鎖釋放
2. SELECT 無鎖
- Oracle 透過 Undo 實現一致性讀，**純 SELECT 不加任何鎖**
- 這是 Oracle 高並發優勢核心
3. SELECT FOR UPDATE
- 產生 TM (RS, mode 2) + TX (X, mode 6)
- TM 模式較 DML 低一級（RS vs RX）
4. FOR UPDATE vs UPDATE 互斥
- Session 1 執行
SELECT ... FOR UPDATE 持有 TM(RS)+TX(X)
- Session 2 執行
UPDATE 等待 TM(RX)，因 TM(RS) 與 TM(RX) 衝突
- V$LOCK 顯示 Session 1 BLOCK=1 阻塞 Session 2
5. 實體完整性導致阻塞
- Session 1
INSERT 未提交，Session 2 同值 INSERT 等待
- Session 2 持有 TX(mode 0, request=4 Share) 等待 PK 鎖
6. 參照完整性鎖行為
- 父表
INSERT 會同時鎖定父表 (RX) 與子表 (RS)
- 子表
INSERT 會同時鎖定子表 (RX) 與父表 (RS)
- 雙向鎖定易導致死鎖
7. 子表外鍵欄位建立索引消除死鎖
CREATE INDEX idx_child ON test_child(a);
- 建立索引後，父子表並發更新不再死鎖
- 鎖模式調整為：父表更新持有 TM(RX)+TX，子表持有 TM(RS)；子表更新持有 TM(RX)+TX，父表持有 TM(RS)
- 雙方皆為 RS/RX 組合，相容不阻塞
總結
- Oracle 採用**多粒度鎖**（表級意向鎖 + 列級 TX 鎖）實現並發控制。
- DML 實際封鎖粒度在列級，表級僅為意向鎖（RS/RX），大幅降低鎖維護開銷。
- 純 SELECT 無鎖，透過 Undo 實現一致性讀，極大提升並發度。
- 子表外鍵務必建立索引，否則父子表並發 DML 極易死鎖。
- 鎖升級機制：Oracle 無鎖升級，始終維持列級鎖，避免鎖升級帶來的並發驟降。
