Oracle 死鎖排查與強制釋放完整操作手冊

死鎖現象與影響

當兩個或多個 Session 互相持有對方需要的鎖資源，形成循環等待，Oracle 會自動偵測並拋出 ORA-00060，犧牲其中一個交易回滾。但鎖持有者可能仍佔用資源，需 DBA 介入清理。

四步驟鎖定問題 Session

1. 查詢被鎖物件 (V$DB_OBJECT_CACHE)

SELECT *
FROM   v$db_object_cache
WHERE  owner = 'SCOTT'
AND    locks != '0';

記下 NAME (物件名)、TYPE。

2. 找出持有鎖的 SID (V$ACCESS)

SELECT sid
FROM   v$access
WHERE  owner = 'SCOTT'
AND    name  = '被鎖物件名';

3. 取得 SID 對應的 SERIAL# 與 OS Process ID

SELECT s.sid, s.serial#, p.spid
FROM   v$session s, v$process p
WHERE  s.paddr = p.addr
AND    s.sid = :上一步查到的_SID;

4. 兩階段強制終止

1. 資料庫層面：
   ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;
   標記 Session 為 KILLED，實際釋放資源需等待 PMON 清理。
2. 作業系統層面 (若資料庫層面無效)：
   Windows: ORAKILL SID SPID
   Linux/Unix: kill -9 SPID

完整腳本：一鍵產生 KILL 指令

SET LINES 200 PAGES 1000
COL kill_db   FOR A60
COL kill_os   FOR A60
SELECT 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' AS kill_db,
       'ORAKILL ' || s.sid || ' ' || p.spid                                AS kill_os  -- Windows
       -- 'kill -9 ' || p.spid                                             AS kill_os  -- Linux
FROM   v$session s, v$process p
WHERE  s.paddr = p.addr
AND    s.sid IN (
  SELECT sid FROM v$access
  WHERE owner = UPPER('&OWNER') AND name = UPPER('&OBJECT_NAME')
);

進階：查看鎖等待鏈 (V$SESSION_WAIT / V$LOCK)

SELECT blocking_session, sid, serial#, event, seconds_in_wait
FROM   v$session
WHERE  blocking_session IS NOT NULL;

預防死鎖最佳實踐

• 統一所有程式存取表的順序（如按表名字母序）。
• 縮短交易範圍，盡早 COMMIT。
• 避免長時間查詢中夾帶 DML。
• 使用 SELECT ... FOR UPDATE NOWAIT 或 SKIP LOCKED (11g+) 非阻塞鎖定。
• 定期收集統計資訊，減少全表掃描導致的鎖升級。