Oracle 鎖表故障診斷與處理實戰案例

Oracle 鎖表故障診斷與處理實戰案例

開發人員在業務高峰期直接在數據庫服務器執行 DELETE 操作導致長時間無響應，關閉客戶端後重試造成嚴重鎖表，數據庫極度緩慢。本文模擬該場景，演示從等待事件分析到定位阻塞會話、殺掉進程的完整排查流程。

1. 查看非空閒等待事件

SELECT sid, event FROM v$session_wait WHERE wait_class <> 'Idle';

輸出示例：enq: TX - row lock contention 明確指向行鎖爭用。

2. 確認阻塞鎖

SELECT * FROM v$lock WHERE block = 1;

3. 分析鎖等待鏈（誰鎖誰）

SELECT sid, id1, id2, lmode, block, request, type
FROM v$lock
WHERE id1 IN (SELECT id1 FROM v$lock WHERE lmode = 0)
ORDER BY id1, request;

BLOCK=1 表示持有鎖，REQUEST>0 表示等待鎖。

4. 定位阻塞 Session 詳細信息

SELECT s.sid, s.serial#, s.username, s.logon_time
FROM v$session s, v$locked_object l
WHERE s.sid = l.session_id
ORDER BY s.logon_time;

5. 進一步獲取 OS 進程號（便於 OS 層面 Kill）

SELECT s.username, s.osuser, s.sid, s.serial#, p.spid, s.program, s.status
FROM v$session s, v$process p
WHERE s.paddr = p.addr AND s.username IS NOT NULL;

6. 殺掉阻塞進程

資料庫層面：ALTER SYSTEM KILL SESSION 'sid,serial#';
作業系統層面（針對標記 KILLED 但未釋放的進程）：kill -9 spid

7. 查詢被鎖表及執行 SQL

SET PAGESIZE 999; SET LINESIZE 200;
SELECT s.status, s.sid, s.serial#, p.spid, s.last_call_et exec_seconds, t.sql_text curr_sql
FROM gv$session s, v$process p, v$instance i, v$lock k, v$sqltext t
WHERE s.paddr = p.addr AND s.type != 'BACKGROUND'
  AND s.lockwait = k.kaddr AND s.sql_hash_value = t.hash_value
  AND s.username IS NOT NULL;

可直接看到被鎖表的具體 SQL（如 UPDATE test_lock SET name='aa4' WHERE id=1）。