Oracle 錯誤 ORA-00054 案例實戰：資源忙碌 NOWAIT 死鎖分析與解決方案

錯誤定義

ORA-00054: resource busy and acquire with NOWAIT specified
Cause:  NOWAIT 關鍵字強制立即返回，因資源被鎖定無法取得。
Action: 稍後重試，或移除 NOWAIT 讓 Session 等待鎖釋放。

核心機制

• LOCK TABLE ... NOWAIT / SELECT ... FOR UPDATE NOWAIT / ALTER INDEX ... REBUILD (隱含 NOWAIT) — **不等待，立即報錯**。
• DDL (ALTER INDEX, ALTER TABLE MOVE 等) **隱含 NOWAIT**，遇鎖直接失敗。
• 鎖類型：TM (DML Enqueue), TX (Row Transaction), UL (User Lock)。

案例一：長交易取消後重建索引遭遇 ORA-00054

現場還原

1. 用戶執行大量更新交易，耗時超過預期。
2. 用戶以 Ctrl+Alt+Del 強制關閉 Client (Session 未正常斷開)。
3. 資料庫偵測 Session 斷開，**PMON 啟動回滾** — 回滾時間 ≈ 交易已執行時間。
4. DBA 立即執行 ALTER INDEX idx REBUILD，**DDL 隱含 NOWAIT**，發現表持有 **Row-X (SX) Lock**，拋出 ORA-00054。

鎖資訊查詢

SELECT s.sid, s.serial#, s.username, s.status,
       l.type, l.lmode, l.request, l.id1, l.id2,
       o.object_name, o.object_type
FROM   v$session s, v$lock l, dba_objects o
WHERE  s.sid = l.sid
AND    l.id1 = o.object_id
AND    l.type IN ('TM','TX')
AND    s.username IS NOT NULL;

解決方案：等待回滾完成

**不要嘗試 Kill Session 再試** — Session 已標記 KILLED，PMON 正在回滾，Kill 只會讓 PMON 重新接手，無法加速。**唯一辦法是等待**。

-- 監控回滾進度 (9i+)
SELECT used_urec "Rollback Records Remaining"
FROM   v$transaction
WHERE  addr = (SELECT taddr FROM v$session WHERE sid = :sid);

-- 或查詢 V$FAST_START_TRANSACTIONS (10g+)
SELECT usn, slt, seq, state, undoblockstotal "Total", undoblocksdone "Done",
       ROUND(undoblocksdone/undoblockstotal*100,2) "Pct_Complete"
FROM   v$fast_start_transactions;

案例二：應用程式 SELECT FOR UPDATE NOWAIT 衝突

場景

高併發搶單系統，多 Session 同時 SELECT * FROM inventory WHERE item_id=:1 FOR UPDATE NOWAIT，第二個 Session 直接報 ORA-00054。

解決策略

1. 移除 NOWAIT → SKIP LOCKED (11g+)：

   
   SELECT * FROM inventory WHERE item_id=:1 FOR UPDATE SKIP LOCKED;
   -- 直接跳過被鎖行，不等待、不報錯，極適合搶單/佇列消費
           

2. 設定等待超時：

   
   ALTER SESSION SET DDL_LOCK_TIMEOUT = 10;  -- 10 秒後報 ORA-00054
   -- 或
   SELECT * FROM inventory WHERE item_id=:1 FOR UPDATE WAIT 5; -- 5 秒
           

3. 樂觀鎖重試：Application 層捕捉 ORA-00054，指數退避重試 (max 3 次)。

案例三：DDL 維護窗口衝突

場景

凌晨 2 點排程 ALTER TABLE big_table MOVE，恰逢長跑 ETL 未提交，DDL 立即失敗。

解決策略

1. DBMS_REDEFINITION (Online Redefinition)：零停機重組，無鎖衝突。
2. DDL_LOCK_TIMEOUT (11g+)：ALTER SESSION SET DDL_LOCK_TIMEOUT = 300; 等待 5 分鐘再失敗，給 ETL 提交機會。
3. 排程前檢查鎖：

   
   SELECT COUNT(*) FROM v$lock WHERE type='TM' AND id1 = (SELECT object_id FROM dba_objects WHERE object_name='BIG_TABLE');
   -- >0 則延遲或跳過本次維護
           

預防性監控：鎖等待告警

-- 企業級監控腳本 (每分鐘)
SELECT blocking_session, sid, serial#, username, event, seconds_in_wait
FROM   v$session
WHERE  blocking_session IS NOT NULL
AND    seconds_in_wait > 30
AND    event NOT LIKE 'SQL*Net%';
-- 觸發告警：郵件/簡訊/Slack 通知 DBA

結語

ORA-00054 **不是 Bug，是設計** — NOWAIT 語意明確。「等待」或「跳過」取決於業務：**金融扣款必須等待 (WAIT/無 NOWAIT)**、**搶單/佇列消費必須跳過 (SKIP LOCKED)**、**DDL 維護設定超時 (DDL_LOCK_TIMEOUT)**。建立鎖監控儀表板，讓阻塞可視化、可預警、可追溯。