有经验的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

执行拼接生成的语句，即可杀掉锁的源头。

ITPub博客

日常运维之TX锁处理（一）

最新文章