快速診斷流程
- 檢查告警視圖：
SELECT * FROM dba_outstanding_alerts
- 鎖定物件：
SELECT * FROM v$locked_object
- 依物件名查鎖：
SELECT sid, id1, type FROM v$lock WHERE id1 = (SELECT object_id FROM dba_objects WHERE object_name = UPPER('MY_TABLE'));
- 定位 Session：
SELECT saddr, sid, serial#, paddr, username, status FROM v$session WHERE sid = :sid;
- 關聯 OS 進程：
SELECT spid, osuser, s.program FROM v$session s, v$process p WHERE s.paddr = p.addr AND s.sid = :sid;
核心查詢腳本 (單次執行完整資訊)
SELECT
lo.object_id,
o.owner, o.object_name, o.object_type,
lo.session_id,
s.serial#,
s.username,
s.osuser,
s.machine,
s.program,
s.logon_time,
s.status,
l.type,
l.lmode,
l.request,
p.spid AS os_pid
FROM v$locked_object lo
JOIN dba_objects o ON lo.object_id = o.object_id
JOIN v$session s ON lo.session_id = s.sid
JOIN v$lock l ON l.sid = s.sid
JOIN v$process p ON s.paddr = p.addr
WHERE l.request > 0 -- 只顯示等待中的鎖請求
ORDER BY s.logon_time;
解鎖操作層級
| 層級 | 指令 | 風險 | 適用場景 |
|---|
| 1. 正常提交/回滾 | 應用程式自行 COMMIT/ROLLBACK | 無 | 正常業務邏輯結束 |
| 2. SQL 層殺 Session | ALTER SYSTEM KILL SESSION 'sid,serial#'; | 低 (交易回滾) | Session 正常但卡住 |
| 3. SQL 層立即殺 Session | ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; | 中 (立即斷開，回滾非同步) | Session 不響應 |
| 4. OS 層殺進程 | kill -9 (Linux) / Task Manager (Windows) | 高 (PMON 需清理，可能殘留鎖) | Session 已僵屍、資料庫無法處理 KILL |
RAC 環境特殊處理
v$locked_object / v$lock 僅顯示本節點鎖資訊- 需查詢
gv$locked_object / gv$lock (含 inst_id)
- 殺 Session 需指定實例：
ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
常見鎖類型對照
| TYPE | 名稱 | 典型原因 |
|---|
| TM | DML Enqueue | 未提交的 INSERT/UPDATE/DELETE、缺少外鍵索引 |
| TX | Transaction Enqueue | 行級鎖衝突、ITL 不足 |
| UL | User Lock | DBMS_LOCK 自訂鎖 |
| ST | Space Transaction | Extent 分配/釋放 (ASSM 環境較少) |
預防長鎖最佳實踐
- 外鍵欄位必建索引：避免父表刪除/更新時全表鎖定子表
- 調大 INITRANS / MAXTRANS (非 ASSM) 或確保 ASSM 正常運作
- 批次作業分批提交：每 100-1000 筆 COMMIT
- 設定資源限制：
PROFILE 限制 IDLE_TIME, CONNECT_TIME
- 監控告警：
DBA_OUTSTANDING_ALERTS / OEM / 自製腳本監控 v$session BLOCKING_SESSION 超過閾值
「鎖是保護資料一致性的盾，也是併發效能的敵。精準診斷、果斷處理、源頭預防，三者缺一不可。」
