Oracle 高效交易撰寫四大準則：減少鎖衝突、提升併發效能

交易機制回顧

Oracle 以交易 (Transaction) 保證 ACID。交易期間對修改資料列加排他鎖 (Exclusive Lock)，直到 COMMIT/ROLLBACK 才釋放。長交易 = 長鎖定 = 高併發風險。

準則一：最小化存取記錄數

核心觀念

鎖定粒度預設為 Row。存取越多列 → 鎖定越多列 → 衝突機率指數上升。

實作技巧

-- ❌ 差：全表掃描更新
UPDATE accounts SET balance = balance - 100 WHERE cust_id = 123;

-- ✅ 好：精確主鍵定位 (單列鎖定)
UPDATE accounts SET balance = balance - 100 WHERE account_id = 456;

-- ✅ 好：批次作業分批提交
FOR r IN (SELECT account_id FROM accounts WHERE cust_id = 123) LOOP
    UPDATE accounts SET balance = balance - 100 WHERE account_id = r.account_id;
    IF sql%rowcount MOD 100 = 0 THEN COMMIT; END IF;  -- 每 100 筆提交
END LOOP;

應用層配合

• 查詢介面提供分類/篩選條件 (如產品類別、日期區間)
• 避免「全選」操作直接觸發大量更新
• 報表查詢使用 SELECT ... FOR UPDATE SKIP LOCKED (11g+) 非阻塞讀取

準則二：保持交易最短執行時間

時間維度思考

鎖持有時間 = 交易執行時間。拆解長交易、延遲非必要工作。

拆解策略

-- ❌ 差：單一巨大交易更新多欄位
BEGIN
    UPDATE emp SET sal = sal * 1.1, comm = sal * 0.1, bonus = 500 WHERE deptno = 10;
    -- 假設 10 萬筆，耗時 30 秒，鎖定 30 秒
END;

-- ✅ 好：拆分為多個短交易
BEGIN
    UPDATE emp SET sal = sal * 1.1 WHERE deptno = 10;  -- 10 秒
    COMMIT;
END;
BEGIN
    UPDATE emp SET comm = sal * 0.1 WHERE deptno = 10; -- 8 秒
    COMMIT;
END;
BEGIN
    UPDATE emp SET bonus = 500 WHERE deptno = 10;      -- 5 秒
    COMMIT;
END;

時機選擇

• 離峰時段 (夜間/維護窗口) 執行大批量更新
• 應用層採用非同步佇列：前台收請求 → 寫入佇列 → 後台 Worker 慢慢處理
• 利用 DBMS_SCHEDULER / DBMS_JOB 排程批次作業

準則三：交易期間嚴禁等待使用者輸入

反模式

-- ❌ 災難：前台彈窗確認在交易中間
BEGIN
    UPDATE orders SET status = 'PROCESSING' WHERE order_id = :id;
    -- 這裡停頓等待使用者按「確認」...
    UPDATE orders SET status = 'CONFIRMED' WHERE order_id = :id;
    COMMIT;
END;

正確模式

1. 前台蒐集所有輸入 (表單驗證、彈窗確認)
2. 使用者按下「送出」→ 後台單一短交易完成所有更新
3. 交易內零互動，純資料庫操作

實例：ERP 訂單異動

使用者編輯畫面 → 修改欄位 → 點「儲存」 → 後台一個 MERGE 或多個 UPDATE 於單一交易提交 → 回傳結果。編輯過程完全不開啟資料庫交易。

準則四：瀏覽/分析階段不開啟交易

• 查詢、試算、報表預覽 → 唯讀，不需交易
• 唯有「確認寫入」瞬間才啟動交易
• 善用 Read Consistency (Oracle 預設 Statement-Level 一致性)，無需額外鎖定

進階補充技巧

「交易像手術刀：精準、快速、一次切乾淨，縫合就走人。」