有經驗的DBA 在遇到TX 鎖時，第一反應就是查詢v$lock 和v$session 檢視，定位LMODE 和REQUEST 型別互斥的會話並進行查殺。然而，隨著資料庫版本不斷地迭代更新，v$session 檢視的內容越來越豐富，可以直接使用blocking_session 、blocking_instance 、 final_blocking_instance 和 final_blocking_session 欄位進行定位。對於鎖層次的排查可以重複查詢v$session 來確定，但如果鎖層次有100 層，我們就不可能透過人工遍歷100 次，顯然這種方式過於低效，不適用於生產環境。

下面就來介紹本節的主角：Oracle 的SYS_CONNECT_BY_PATH 函式。自Oracle 9i 開始，DBA 可以使用SYS_CONNECT_BY_PATH 函式，將父節點到當前行的內容以“路徑”或層次的形式顯示出來。該功能剛好符合我們遞迴查詢鎖層次的需求，在這裡筆者模擬了鎖環境，使用如下語句查詢鎖資訊：

SQL> select a.inst_id ,

a.process ,

a.sid ,

a.serial# ,

a.sql_id ,

a.event ,

a.status ,

a.program ,

a.machine ,

connect_by_isleaf as isleaf ,

sys_connect_by_path (a.SID || '@' || a.inst_id , ' <- ' ) tree ,

level as tree_level

from gv$session a

start with a.blocking_session is not null

connect by (a.sid || '@' || a.inst_id ) = prior

(a.blocking_session || '@' || a.blocking_instance );

INST_ID PROCESS SID SERIAL# EVENT STATUS ISLEAF TREE TREE_LEVEL

------- -------- ---- ---------- ------------------------------ ---------- ------ ----------------- ----------

1 7663 17 6749 enq: TX - row lock contention ACTIVE 0 <- 17@1 1

1 6198 25 9989 SQL*Net message from client INACTIVE 1 <- 17@1 <- 25@1 2

1 6310 28 23199 enq: TX - row lock contention ACTIVE 0 <- 28@1 1

1 6198 25 9989 SQL*Net message from client INACTIVE 1 <- 28@1 <- 25@1 2

程式碼段中，部分引數說明如下。

q INST_ID ：會話所在的節點號。

q PROCESS ：客戶端程式號，與v$process 中的spid 不是同一個。

q SID 、SERIAL# 、SQL_ID 、STATUS 、PROGRAM 、MACHINE ：會話資訊。

q ISLEAF ：是否為源頭，0 代表否，1 代表是。

TREE ：樹形結構，鎖的層次，例如，<- 152@2 <- 153@2 <- 161@1 ，從左到右表示為2 節點的會話152 被2 節點的會話153 堵塞，而2 節點的會話153 又被1 節點的會話161 堵塞。所以1 節點的會話161 是鎖的源頭。

q TREE_LEVEL ：樹形層次。

鎖源頭的查殺方法有兩種，說明如下。

1 ）透過ISLEAF 進行篩選，直接查殺鎖源頭，語句如下：

SQL> select 'alter system kill session ''' || sid || '' || ',' || serial# || ',@' ||

inst_id || ''' immediate;' db_kill_session

from ( select a.inst_id ,

a.process ,

a.sid ,

a.serial# ,

a.sql_id ,

a.event ,

a.status ,

a.program ,

a.machine ,

connect_by_isleaf as isleaf ,

sys_connect_by_path (a.SID || '@' || a.inst_id , ' <- ' ) tree ,

level as tree_level

from gv$session a

start with a.blocking_session is not null

connect by (a.sid || '@' || a.inst_id ) = prior

(a.blocking_session || '@' || a.blocking_instance ))

where isleaf = 1

order by tree_level asc ;

KILL_SESSION

---------------------------------------------------

alter system kill session '161,5579,@1' immediate;

SQL> select inst_id , 'kill -9 ' || spid os_kill_session

from ( select p.inst_id ,

p.spid ,

a.sid ,

a.serial# ,

a.sql_id ,

a.event ,

a.status ,

a.program ,

a.machine ,

connect_by_isleaf as isleaf ,

sys_connect_by_path (a.SID || '@' || a.inst_id , ' <- ' ) tree ,

level as tree_level

from gv$session a , gv$process p

where a.inst_id = p.inst_id

and a.paddr = p.addr

start with a.blocking_session is not null

connect by (a.sid || '@' || a.inst_id ) = prior

(a.blocking_session || '@' || a.blocking_instance ))

where isleaf = 1

order by tree_level asc ;

INST_ID OS_KILL_SESSION

---------- --------------------------------

1 kill -9 30049

2 ）藉助v$session 中的final_blocking_instance 和final_blocking_session 定位鎖源頭，語句如下：

SQL> select 'alter system kill session ''' || ss.sid || '' || ',' || ss.serial# || ',@' ||

ss.inst_id || ''' immediate;' db_kill_session

from gv$session s , gv$session ss

where s.final_blocking_session is not null

and s.final_blocking_instance = ss.inst_id

and s.final_blocking_session = ss.sid

and s.sid <> ss.sid

DB_KILL_SESSION

--------------------------------------------------

alter system kill session '161,5579,@1' immediate;

SQL> select p.inst_id , 'kill -9 ' || p.spid os_kill_session

from gv$session s , gv$session ss , gv$process p

where s.final_blocking_session is not null

and s.final_blocking_instance = ss.inst_id

and s.final_blocking_session = ss.sid

and ss.paddr = p.addr

and ss.inst_id = p.inst_id

and s.sid <> ss.sid

INST_ID OS_KILL_SESSION

---------- --------------------------------

1 kill -9 30049

執行拼接生成的語句，即可殺掉鎖的源頭。

日常運維之TX鎖處理（一）

相關文章