鎖類型總覽
| 層級 | 類型 | 用途 | 自動/手動 |
|---|
| 內部 | Latches, Mutexes | 保護 SGA 共享記憶體結構 | 自動 |
| DDL | Dictionary/Parse Locks | 保護資料字典、物件定義 | 自動 |
| DML | Row/Table Locks | 並發交易資料一致性 | 自動/手動 |
DML 鎖模式詳解
1. 共享鎖 (Share Lock, S)
LOCK TABLE tab IN SHARE MODE [NOWAIT];- 允許：多重共享鎖共存、查詢
- 阻擋：獨佔鎖、UPDATE/DELETE/INSERT
- 釋放：COMMIT/ROLLBACK/離線
- 用途：一致性查詢、防止資料變更
2. 獨佔鎖 (Exclusive Lock, X)
LOCK TABLE tab IN EXCLUSIVE MODE [NOWAIT];- 允許：持有者查詢與更新
- 阻擋：所有其他鎖模式
- 隱含獲得：執行 INSERT/UPDATE/DELETE 時自動獲得行獨佔鎖 (RX) + 表共享獨佔鎖 (SX/SSX)
- 用途：大量更新、避免死鎖預先鎖定多表
3. 共享更新鎖 (Share Update / Row Share, RS)
- 即
SELECT ... FOR UPDATE 獲得的行鎖
- 允許：並發查詢、其他 RS 鎖
- 阻擋：獨佔鎖、共享鎖
- 釋放：COMMIT/ROLLBACK
鎖相容性矩陣
| 請求 \ 持有 | NULL | RS (SS) | RX (SX) | S | SRX (SSX) | X |
|---|
| RS | ✓ | ✓ | ✓ | ✓ | ✓ | ✗ |
| RX | ✓ | ✓ | ✓ | ✗ | ✗ | ✗ |
| S | ✓ | ✓ | ✗ | ✓ | ✗ | ✗ |
| SRX | ✓ | ✓ | ✗ | ✗ | ✗ | ✗ |
| X | ✓ | ✗ | ✗ | ✗ | ✗ | ✗ |
死鎖診斷與處理
識別死鎖
- Alert Log 記錄：
ORA-00060: deadlock detected
- Trace 檔案：
USER_DUMP_DEST 下 *_ora_*.trc 含死鎖圖
查詢當前阻塞鏈
SELECT blocking_session, sid, serial#,
sql_id, event, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY seconds_in_wait DESC;
查詢鎖住物件詳細資訊
SELECT l.session_id, o.object_name, l.locked_mode,
l.oracle_username, l.os_user_name, s.machine, s.program
FROM v$locked_object l
JOIN dba_objects o ON l.object_id = o.object_id
JOIN v$session s ON l.session_id = s.sid
ORDER BY l.session_id;
LOCKED_MODE：0=None, 1=Null, 2=RS, 3=RX, 4=S, 5=SRX, 6=X
解除阻塞
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
最佳實踐
- 固定存取順序：所有交易按相同順序存取表，避免循環等待
- 縮短交易：提早 COMMIT，減少持鎖時間
- 索引外鍵欄位：避免子表 DML 導致父表全表掃描鎖升級
- 避免長時間 SELECT FOR UPDATE：改用樂觀鎖 (版本號/時間戳)
- 監控：設定
DB_BLOCKING_ALERT 或 Enterprise Manager 告警
