Oracle 鎖機制全景圖：六大模式、SQL 查詢鎖衝突與殺會話實戰

一、六大鎖模式層級 (數字越大、限制越強)

二、常見操作對應鎖模式對照表

三、關鍵行為特徵

• 模式 2,3,4 (RS, RX, S)：不阻塞 DML (INSERT/UPDATE/DELETE/SELECT)，但 阻塞 DDL (ALTER/DROP) → 報 ORA-00054 "resource busy and acquire with NOWAIT specified"
• 模式 5 (SRX)：外鍵約束觸發，父表修改主鍵/刪除時鎖定子表
• 模式 6 (X)：完全獨佔，所有操作等待

四、DBA 實戰：查詢當前鎖情況

1. 物件級鎖視圖

COL OWNER FOR A12
COL OBJECT_NAME FOR A16
SELECT B.OWNER, B.OBJECT_NAME, L.SESSION_ID, L.LOCKED_MODE
FROM V$LOCKED_OBJECT L, DBA_OBJECTS B
WHERE B.OBJECT_ID = L.OBJECT_ID;

2. 會話級鎖視圖 (含登入時間排序)

SELECT T2.USERNAME, T2.SID, T2.SERIAL#, T2.LOGON_TIME
FROM V$LOCKED_OBJECT T1, V$SESSION T2
WHERE T1.SESSION_ID = T2.SID
ORDER BY T2.LOGON_TIME;

3. 綜合深度查詢 (含阻塞鏈)

SELECT 
  S.SID, S.SERIAL#, S.USERNAME, S.MACHINE, S.PROGRAM, S.LOGON_TIME,
  L.LMODE, L.REQUEST, L.TYPE, L.ID1, L.ID2,
  O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE,
  DECODE(L.LMODE, 0,'NONE',1,'NULL',2,'RS',3,'RX',4,'S',5,'SRX',6,'X') HELD_MODE,
  DECODE(L.REQUEST, 0,'NONE',1,'NULL',2,'RS',3,'RX',4,'S',5,'SRX',6,'X') REQUESTED_MODE
FROM V$LOCK L, V$SESSION S, DBA_OBJECTS O
WHERE L.SID = S.SID
  AND L.ID1 = O.OBJECT_ID(+)
  AND (L.LMODE > 0 OR L.REQUEST > 0)
ORDER BY S.LOGON_TIME;

五、處理長期鎖衝突：殺會話最佳實踐

正確殺會話語法

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

IMMEDIATE (10g+) 使會話立即終止並回滾，不等待當前調用完成。

禁忌：OS 層面 Kill Process

# 絕對禁止
kill -9 
kill 

原因：一個用戶進程可能持有多個鎖 (多個 V$LOCKED_OBJECT 記錄)，OS Kill 只清除進程，**不會自動釋放鎖結構**，導致殘留鎖阻塞其他會話，甚至需重啟 Instance 才能清理。

六、鎖等待診斷查詢

-- 當前阻塞鏈
SELECT 
  BLOCKING_SESSION, BLOCKING_SESSION_STATUS,
  SID, SERIAL#, USERNAME, EVENT, SECONDS_IN_WAIT,
  SQL_ID, PREV_SQL_ID
FROM V$SESSION
WHERE BLOCKING_SESSION IS NOT NULL
ORDER BY SECONDS_IN_WAIT DESC;

-- 歷史鎖等待 (需診斷包授權)
SELECT * FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE EVENT LIKE 'enq:%' AND SESSION_STATE = 'WAITING'
ORDER BY SAMPLE_TIME DESC;

七、預防鎖衝突設計原則

1. 縮短交易長度：COMMIT 越快，鎖持有時間越短
2. 避免長查詢中執行 DML：SELECT 後再 UPDATE 易造成自死鎖
3. 合理設計外鍵：大量子表記錄時，父表 PK 修改/刪除會大面積鎖定子表
4. 使用 NOWAIT/SKIP LOCKED：SELECT ... FOR UPDATE NOWAIT 或 SKIP LOCKED (11g+) 避免無限等待
5. 分區表局部索引：減少 Global Index 維護鎖範圍
6. 監控告警：鎖等待 > 30 秒告警，> 300 秒自動殺會話 (謹慎啟用)