Oracle 鎖衝突快速定位與釋放：V$LOCKED_OBJECT 實戰查詢

提交交易時遇到「資源忙碌、要求指定資源的鎖已被佔用」 (ORA-00054) 或應用無回應，通常是被其他 Session 持有鎖導致。本文提供標準定位腳本：從鎖對象 → 阻塞 Session → Kill Session，三步解決鎖衝突。

一、標準定位查詢

-- 1. 查看被鎖對象與持有 Session
SELECT object_id, session_id, locked_mode
FROM v$locked_object;

-- 2. 關聯 Session 詳細資訊 (用戶、登入時間、SQL)
SELECT s.username, s.sid, s.serial#, s.logon_time,
       s.sql_id, s.prev_sql_id, s.status, s.machine, s.program
FROM v$locked_object l
JOIN v$session s ON l.session_id = s.sid
ORDER BY s.logon_time;

二、鎖模式對照表 (LOCKED_MODE)

三、釋放鎖：Kill Session

確認目標 SID、SERIAL# 後：

ALTER SYSTEM KILL SESSION 'sid,serial#';

範例：ALTER SYSTEM KILL SESSION '7,10839';

注意：

• KILL SESSION 標記 Session 為「待終止」，實際釋放鎖需等待 Session 回應 (可能因長時間運算、I/O 等待延遲)。
• 若需強制立即終止 (11g+)：ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
• RAC 環境需指定實例：ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

四、進階：查看阻塞鏈 (Blocker → Waiter)

SELECT blocking_session, sid, serial#, username, sql_id, event, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY seconds_in_wait DESC;

或使用 Oracle Enterprise Manager / SQL Developer 「鎖管理器」圖形化介面。

五、預防與最佳實踐

1. 應用層：縮短交易範圍、避免長交易、按固定順序存取表 (防死結)。
2. 索引外鍵欄位：避免父表 DML 引發子表全表掃描鎖定。
3. 設定 DDL_LOCK_TIMEOUT (11g+) 讓 DDL 等待短暫釋放而非立即報錯。
4. 監控腳本：定期查詢 V$SESSION_WAIT 事件 enq: TX - row lock contention、enq: TM - contention。