Oracle 存儲過程大量數據導致表鎖定，會話中查不到阻塞進程如何排查？

現象

存儲過程處理大量數據時卡死，表被鎖定，但 V$SESSION/V$LOCK 查無阻塞會話，Kill 不了進程。

可能原因

1. 自主事務/遞歸調用：存儲過程中開啟了自主事務 (PRAGMA AUTONOMOUS_TRANSACTION) 或調用其他過程，導致鎖持有者非當前會話。
2. DDL 隱式提交：過程中執行 TRUNCATE/CREATE INDEX 等 DDL，導致隱式提交，鎖釋放時機難預測。
3. 分布式事務/DB Link：涉及遠程數據庫，協調器進程持有鎖，本地不可见。
4. ITL 槽不足：大量併發更新導致 Interested Transaction List 滿，會話等待 ITL，不顯示為行鎖。

排查步驟

-- 1. 查找持有鎖的對象與會話（含遞歸）
SELECT s.sid, s.serial#, s.username, s.program, o.object_name, l.type, l.lmode, l.request
FROM v$session s
JOIN v$lock l ON s.sid = l.sid
JOIN dba_objects o ON l.id1 = o.object_id
WHERE l.request > 0 OR l.lmode > 0;

-- 2. 檢查 ITL 等待
SELECT * FROM v$session_wait WHERE event = 'enq: TX - allocate ITL entry';

-- 3. 查看長時間活躍事務
SELECT s.sid, s.serial#, t.used_ublk, t.start_time, s.sql_id
FROM v$transaction t
JOIN v$session s ON t.addr = s.taddr
WHERE t.used_ublk > 1000;  -- 大量 undo 塊

解決策略

• 分批提交：循環中每處理 N 筆 COMMIT，避免單事務過大鎖表過久。
• 增加 INITRANS：ALTER TABLE t INITRANS 20; 預留更多 ITL 槽。
• 使用 DBMS_PARALLEL_EXECUTE：並行分塊處理大表更新。
• 監控腳本：定時輪詢 V$SESSION_WAIT 記錄等待鏈，定位根因。

參考資源

CSDN 討論帖：Oracle 大事務鎖定排查實戰