Oracle 行級鎖定：SELECT FOR UPDATE 實戰與交易控制

核心語法

SELECT column_list
FROM table_name
WHERE condition
FOR UPDATE [OF column_list] [NOWAIT | WAIT n | SKIP LOCKED];

鎖定行為詳解

• 行級排他鎖 (Row Exclusive Lock / TX Lock)：被選取的列被鎖定，其他 Session 無法 UPDATE/DELETE/SELECT FOR UPDATE 這些列
• 不阻塞單純 SELECT：Oracle 讀取一致性機制，一般查詢不受影響
• 鎖定持續到交易結束：COMMIT 或 ROLLBACK 釋放
• OF column_list：僅鎖定指定欄位 (較少用)

等待模式選項

JDBC 實作範例

Connection conn = dataSource.getConnection();
try {
    // 1. 關閉自動提交，開啟交易
    conn.setAutoCommit(false);
    
    // 2. 查詢並鎖定目標列
    String sql = "SELECT id, balance FROM accounts WHERE id = ? FOR UPDATE NOWAIT";
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setLong(1, accountId);
        try (ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                long id = rs.getLong("id");
                BigDecimal balance = rs.getBigDecimal("balance");
                
                // 3. 執行業務邏輯 (扣款、轉帳等)
                BigDecimal newBalance = balance.subtract(amount);
                
                // 4. 更新資料
                String updateSql = "UPDATE accounts SET balance = ? WHERE id = ?";
                try (PreparedStatement ups = conn.prepareStatement(updateSql)) {
                    ups.setBigDecimal(1, newBalance);
                    ups.setLong(2, id);
                    ups.executeUpdate();
                }
                
                // 5. 提交交易，釋放鎖
                conn.commit();
            } else {
                throw new RuntimeException("帳戶不存在");
            }
        }
    }
} catch (SQLException e) {
    // 處理鎖定超時 ORA-00054 / ORA-30006
    if (e.getErrorCode() == 54 || e.getErrorCode() == 3006) {
        // 重試邏輯或拋出業務異常
    }
    conn.rollback();
    throw e;
} finally {
    conn.setAutoCommit(true);  // 恢復預設
    conn.close();
}

常見陷阱與最佳實踐

❌ 錯誤：查詢後未立即更新，交易過長

-- 錯誤模式：中間夾雜複雜運算、外部 API 呼叫、使用者互動
SELECT ... FOR UPDATE;
-- 做很多事...
UPDATE ...;
COMMIT;

後果：長時間持有鎖 → 併發效能嚴重下降、死結風險增加。

✅ 正確：縮小交易範圍，鎖定→更新→提交一氣呵成

-- 正確模式：單一交易內完成
BEGIN TRANSACTION;
SELECT ... FOR UPDATE;
-- 僅做必要運算
UPDATE ...;
COMMIT;

死結預防

1. 固定存取順序：所有交易按相同順序鎖定資源 (如按 ID 排序)
2. 使用 NOWAIT/WAIT n：避免無限等待
3. 縮短交易時間：減少持鎖期間
4. 避免不必要的 FOR UPDATE：樂觀鎖 (版本號/時間戳) 替代悲觀鎖

進階：樂觀鎖替代方案

-- 表加版本號欄位
ALTER TABLE accounts ADD (version NUMBER DEFAULT 0);

-- 更新時檢查版本
UPDATE accounts 
SET balance = :new_bal, version = version + 1
WHERE id = :id AND version = :old_version;

-- 若 0 筆更新 → 衝突，重新讀取重試

優點：無資料庫鎖、高併發效能佳。缺點：需應用層重試邏輯、不適用強一致性需求場景。

監控鎖定狀況

-- 查看被鎖定物件
SELECT o.object_name, l.session_id, l.locked_mode, l.oracle_username
FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id;

-- 查看等待鎖的 Session
SELECT s.sid, s.serial#, s.username, s.sql_id, w.event, w.seconds_in_wait
FROM v$session s, v$session_wait w
WHERE s.sid = w.sid AND w.event LIKE '%enq%';