Oracle 死鎖檢測與分析：兩套核心 SQL 腳本 (V$LOCK/V$SESSION 深度關聯)

腳本一：快速識別 Blocker / Waiter

SELECT sid,
       DECODE(block, 0, 'NO', 'YES') blocker,
       DECODE(request, 0, 'NO', 'YES') waiter
FROM v$lock
WHERE request > 0 OR block > 0
ORDER BY block DESC;

輸出解讀：BLOCKER=YES 持有鎖阻塞他人；WAITER=YES 正在等待鎖。

腳本二：完整死鎖現場還原 (含用戶、SQL、鎖類型、模式、機器、OS用戶、阻塞鏈)

SELECT bs.username "Blocking User",
       ws.username "Waiting User",
       bs.sid "Blocking SID", ws.sid "Waiting SID",
       bs.serial# "Blocking Serial#", ws.serial# "Waiting Serial#",
       bs.sql_address "Blocking SQL Addr", bs.sql_hash_value "Blocking SQL Hash",
       bs.program "Blocking Program", ws.program "Waiting Program",
       bs.machine "Blocking Machine", ws.machine "Waiting Machine",
       bs.osuser "Blocking OS User", ws.osuser "Waiting OS User",
       DECODE(wk.type,
         'MR', 'Media Recovery', 'RT', 'Redo Thread',
         'UN', 'User Name', 'TX', 'Transaction',
         'TM', 'DML', 'UL', 'PL/SQL User Lock',
         'DX', 'Distributed Xaction', 'CF', 'Control File',
         'IS', 'Instance State', 'FS', 'File Set',
         'IR', 'Instance Recovery', 'ST', 'Disk Space Tx',
         'TS', 'Temp Segment', 'IV', 'Lib Cache Invalidation',
         'LS', 'Log Start/Switch', 'RW', 'Row Wait',
         'SQ', 'Sequence Number', 'TE', 'Extend Table',
         'TT', 'Temp Table', wk.type) lock_type,
       DECODE(hk.lmode,
         0, 'None', 1, 'NULL', 2, 'Row-S (SS)',
         3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)',
         6, 'Exclusive', TO_CHAR(hk.lmode)) mode_held,
       DECODE(wk.request,
         0, 'None', 1, 'NULL', 2, 'Row-S (SS)',
         3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)',
         6, 'Exclusive', TO_CHAR(wk.request)) mode_requested,
       TO_CHAR(hk.id1) lock_id1, TO_CHAR(hk.id2) lock_id2,
       DECODE(hk.block,
         0, 'Not Blocking', 1, 'Blocking', 2, 'Global',
         TO_CHAR(hk.block)) blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.block = 1
  AND hk.lmode NOT IN (0,1)
  AND wk.request != 0
  AND wk.type(+) = hk.type
  AND wk.id1(+) = hk.id1
  AND wk.id2(+) = hk.id2
  AND hk.sid = bs.sid(+)
  AND wk.sid = ws.sid(+)
  AND bs.username IS NOT NULL
  AND bs.username NOT IN ('SYS','SYSTEM')
ORDER BY 1;

關鍵列解讀

應急處理流程

1. 運行腳本二，鎖定 Blocking SID。
2. 確認業務影響：
   SELECT sql_text FROM v$sql WHERE hash_value = ;
3. 若確需干預：**聯繫應用負責人** → 正常提交/回滾 → 若無響應 → ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
4. 事後複盤：檢查應用邏輯 (提交頻率、索引缺失、長事務)、鎖順序一致性、隔離級別。

預防最佳實踐

• 統一訪問表順序 (按表名/ID 排序)，避免循環等待。
• 縮短事務長度，盡早提交/回滾。
• 合理索引減少全表掃描升級表鎖。
• 應用層設置鎖超時 (JDBC setQueryTimeout、OCI OCI_ATTR_NONBLOCKING_MODE)。
• 定期跑死鎖檢測腳本納入巡檢平台，告警推送。