有经验的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;
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;
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
执行拼接生成的语句，即可杀掉锁的源头。
