Oracle 死鎖處理與強制釋放 Session 實務操作

Oracle 死鎖處理與強制釋放 Session 實務操作

死鎖發生後，標準處理是提交/回滾阻塞交易。但實務上往往無法即時聯繫持有鎖的使用者，需 DBA 強制釋放 Session。本文提供分級處理流程：優雅終止 → 強制殺除 → OS 層面清理。

分級處理策略

Level 1：優雅終止（首選）

通知應用擁有者在應用層取消操作或提交/回滾交易。若無法聯繫，進入 Level 2。

Level 2：ALTER SYSTEM KILL SESSION

步驟 1：查詢死鎖 Session 詳細資訊

SELECT s.username,
       lo.object_id,
       lo.session_id,
       s.serial#,
       lo.oracle_username,
       lo.os_user_name,
       lo.process
FROM   v$locked_object lo
JOIN   v$session s ON lo.session_id = s.sid;

步驟 2：標記 Session 為 KILLED

ALTER SYSTEM KILL SESSION 'sid,serial#';

範例：ALTER SYSTEM KILL SESSION '145, 32768';

機制：Session 狀態變為 KILLED，下次發出資料庫呼叫時終止並回滾交易。若 Session 正在執行不可中斷操作（如網路 I/O、Rollback），可能延遲釋放。

Level 3：OS 層面強制終止進程（最後手段）

步驟 1：取得 Server Process ID (SPID)

SELECT p.spid
FROM   v$session s
JOIN   v$process p ON s.paddr = p.addr
WHERE  s.sid = :deadlocked_sid;

步驟 2：OS 層面 Kill 進程

# Linux/Unix
kill -9 

# Windows (需管理員權限)
taskkill /PID  /F

步驟 3：驗證釋放

SELECT * FROM v$locked_object WHERE session_id = :deadlocked_sid;

關鍵差異對比

死鎖預防長效機制

1. 應用層：統一多表更新順序、縮短交易範圍、使用樂觀鎖（版本號/時間戳）
2. 資料庫層：為外鍵建立索引、設定 DDL_LOCK_TIMEOUT、啟用 BLOCKING_SESSION 監控告警
3. 架構層：讀寫分離、分庫分表減少熱點競爭、引入分散式鎖（Redis/ZooKeeper）
4. 監控層：部署死鎖自動偵測腳本（每 30 秒掃描 V$LOCKED_OBJECT），整合至告警平台

事後複盤清單

• 死鎖發生時間、持續時長
• 涉及 SQL 文本、執行計畫
• 阻塞 Session 來源（機器、程式、使用者）
• 處理動作與時間點
• 業務影響範圍
• 根因分析與改善措施