Oracle 鎖機制實戰：SELECT FOR UPDATE 實現資料列鎖定與併發控制

為何需要顯式鎖定？

Oracle 預設讀不阻寫、寫不阻讀 (MVCC)。但在「查詢後立即更新/刪除同一筆資料」的業務流程中，需防止併發事務在中間竄改資料，導致邏輯錯誤或更新遺失 — 此時使用 SELECT ... FOR UPDATE 獲取排他鎖。

語法與變體

-- 鎖定滿足條件的所有列
SELECT * FROM emp WHERE deptno = 10 FOR UPDATE;

-- 僅鎖定特定欄位 (減少鎖衝突)
SELECT sal FROM emp WHERE deptno = 10 FOR UPDATE OF sal;

-- NOWAIT：無法獲取鎖立即報錯 ORA-00054
SELECT * FROM emp WHERE deptno = 10 FOR UPDATE NOWAIT;

-- WAIT n：等待 n 秒後報錯
SELECT * FROM emp WHERE deptno = 10 FOR UPDATE WAIT 5;

-- SKIP LOCKED (11g+)：跳過已被鎖定的列，適合佇列消費者模式
SELECT * FROM task_queue WHERE status='PENDING' FOR UPDATE SKIP LOCKED;

實戰演示：兩個 Session 互動

-- Session 1
CONN test/test
SELECT * FROM a FOR UPDATE;  -- 獲得排他鎖
-- 未 Commit/Rollback 前保持鎖定

-- Session 2 (新視窗)
CONN test/test
DELETE FROM a;  -- 阻塞等待，直到 Session 1 釋放鎖

Session 1 執行 COMMIT 或 ROLLBACK 即釋放鎖，Session 2 的 DELETE 才會繼續執行。

鎖的層級與相容性

• FOR UPDATE 獲取 Row Exclusive (RX) 表鎖 + 排他列鎖 (TX)。
• 相容性：多個 Session 可同時持有同一表的 RX 鎖，但同一列同一時間僅能被一個 Session 持有 TX 鎖。
• DDL (ALTER TABLE) 需 Exclusive (X) 表鎖，會被任何 RX 阻塞。

常見應用模式

1. 樂觀鎖輔助：查詢時 FOR UPDATE，應用層檢查版本號再更新。
2. 佇列消費：SELECT ... FOR UPDATE SKIP LOCKED 實現高併發任務分發。
3. 避免死鎖：多表操作時固定順序加鎖（如先父表後子表）。

監控與排查

-- 查詢正在等待的 Session
SELECT s.sid, s.serial#, s.username, l.type, l.lmode, l.request,
       o.object_name, s.sql_id
FROM v$session s
JOIN v$lock l ON s.sid = l.sid
LEFT JOIN dba_objects o ON l.id1 = o.object_id
WHERE l.request > 0;

-- 釋放鎖 (慎用)
ALTER SYSTEM KILL SESSION 'sid,serial#';

總結

SELECT FOR UPDATE 是 Oracle 併發控制的利器，配合 NOWAIT/WAIT/SKIP LOCKED 可精準滿足不同業務對一致性與吞吐的平衡需求。