Oracle 死鎖檢測與定位實戰指令集
死鎖導致應用程式無響應且無報錯，排查時效性要求極高。本文提供標準化死鎖檢測查詢，可快速定位阻塞 Session、SQL 文本與來源機器。
死鎖現象識別
- 應用程式點擊「確定/儲存」後長時間無響應
- 無錯誤訊息彈出，資料庫 Session 處於等待狀態
- 同一資料列被多 Session 競爭修改，先到者未提交/回滾
核心檢測查詢
1. 識別被阻塞 Session 與來源機器
SELECT s.username,
s.lockwait,
s.status,
s.machine,
s.program
FROM v$session s
WHERE s.sid IN (SELECT session_id FROM v$locked_object);
欄位解讀
| 欄位 | 含義 | 死鎖指標 |
|---|
| USERNAME | 資料庫使用者 | 持有鎖的使用者 |
| LOCKWAIT | 等待鎖的位址 | 非 NULL 表示正在等待鎖 |
| STATUS | Session 狀態 | ACTIVE 通常表示被阻塞 |
| MACHINE | 客戶端機器名/IP | 定位問題來源主機 |
| PROGRAM | 客戶端程式名稱 | 識別應用程式模組 |
2. 取得被阻塞的具體 SQL 文本
SELECT sql_text
FROM v$sql
WHERE hash_value IN (
SELECT sql_hash_value
FROM v$session
WHERE sid IN (SELECT session_id FROM v$locked_object)
);
進階診斷查詢（含鎖類型、物件資訊）
SELECT lo.object_id,
lo.session_id,
s.serial#,
lo.oracle_username,
lo.os_user_name,
lo.process,
o.object_name,
o.object_type,
l.locked_mode,
DECODE(l.locked_mode,
0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)', 'Unknown') lock_mode_desc
FROM v$locked_object lo
JOIN v$session s ON lo.session_id = s.sid
JOIN dba_objects o ON lo.object_id = o.object_id
JOIN v$lock l ON l.sid = s.sid AND l.id1 = lo.object_id;
鎖模式對照表
| 模式碼 | 名稱 | 兼容性 | 典型操作 |
|---|
| 0 | None | - | - |
| 1 | Null (NULL) | 所有模式 | 查詢無鎖 |
| 2 | Row-S (SS) | SS, SX, S | SELECT ... FOR UPDATE |
| 3 | Row-X (SX) | SS, SX | INSERT/UPDATE/DELETE |
| 4 | Share (S) | SS, S | CREATE INDEX |
| 5 | S/Row-X (SSX) | SS | 鎖定整表 |
| 6 | Exclusive (X) | 無 | DDL、DROP TABLE |
常見死鎖成因與預防
- 多表更新順序不一致：規範所有交易按固定順序鎖定表（如按表名字母序）
- 長交易持有鎖過久：縮短交易範圍，避免在交易中呼叫外部服務/人工操作
- 缺少索引導致全表掃描鎖定：為外鍵、WHERE 條件欄位建立索引
- 應用層邏輯缺陷：同一業務流程並發執行時無序列化機制
緊急處理流程
- 執行上述查詢確認死鎖 Session
- 通知應用擁有者確認業務影響
- 若需強制釋放：
ALTER SYSTEM KILL SESSION 'sid,serial#';
- 記錄死鎖 SQL、時間、涉及表、處理方式，納入知識庫
