Oracle數據庫鎖定監控與解鎖實戰：V$SESSION+V$LOCK聯查精準定位

為什麼需要專門的鎖監控查詢

PL/SQL Developer等工具Tools → Sessions視圖難以直觀展示「誰阻塞了誰」、鎖模式、對象名稱。SQL聯查可輸出結構化阻塞鏈，支援自動化巡檢與告警。

核心監控查詢（生產級）

SELECT 
  sn.username,
  m.sid,
  sn.serial#,
  m.type,
  DECODE(m.lmode,
    0, 'None', 1, 'Null', 2, 'Row Share',
    3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.',
    6, 'Exclusive', LTRIM(TO_CHAR(m.lmode, '990'))) lmode,
  DECODE(m.request,
    0, 'None', 1, 'Null', 2, 'Row Share',
    3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.',
    6, 'Exclusive', LTRIM(TO_CHAR(m.request, '990'))) request,
  m.id1, m.id2,
  o.object_name,
  o.object_type,
  sn.sql_id,
  sn.prev_sql_id,
  sn.logon_time,
  sn.module,
  sn.action
FROM v$session sn
JOIN v$lock m ON sn.sid = m.sid
LEFT JOIN dba_objects o ON m.id1 = o.object_id
WHERE (m.request != 0)  -- 等待者
   OR (m.request = 0 AND m.lmode != 4 
       AND (m.id1, m.id2) IN (
         SELECT id1, id2 FROM v$lock WHERE request != 0
       ))  -- 持有者且被等待（排除純Share鎖無衝突）
ORDER BY m.id1, m.id2, m.request;

輸出欄位解讀

解鎖執行

-- 獲取目標 SID, SERIAL#
ALTER SYSTEM KILL SESSION 'sid,serial#';

-- 強制立即釋放（慎用）
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

鎖表語法補充

-- 顯式鎖表
LOCK TABLE table_name IN lock_mode MODE [NOWAIT | WAIT n];

-- lock_mode 選項
ROW SHARE        -- 共享行級，允許並發查詢/修改
ROW EXCLUSIVE    -- DML默認隱式鎖
SHARE            -- 禁止DML，允許查詢
SHARE ROW EXCLUSIVE -- 禁止DML/查詢（除SELECT FOR UPDATE）
EXCLUSIVE        -- 完全獨佔，禁止一切

自動化巡檢腳本（每5分鐘跑一次）

-- 發現阻塞超過5分鐘告警
SELECT 
  blocking.sid block_sid,
  blocked.sid wait_sid,
  blocked.sql_id wait_sql,
  blocked.logon_time,
  SYSDATE - blocked.logon_time lock_duration_days
FROM v$lock l
JOIN v$session blocked ON l.sid = blocked.sid AND l.request > 0
JOIN v$lock l2 ON l.id1 = l2.id1 AND l.id2 = l2.id2 AND l2.request = 0
JOIN v$session blocking ON l2.sid = blocking.sid
WHERE (SYSDATE - blocked.logon_time) * 24 * 60 > 5;  -- 超過5分鐘

總結

掌握V$SESSION + V$LOCK + DBA_OBJECTS三表聯查，即可將模糊的「庫卡住了」轉化為具體的「SID=146持有TX排他鎖阻塞SID=203更新EMP表」。配合KILL SESSION 'sid,serial#'，實現從發現到解除的閉環。