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