一.背景
最近在看《DBA攻坚指南》这本书，看到关于TX锁处理的脚本的时候感觉写的真好，把笔记分享记录一下。
二.脚本
2.1.模拟锁
/***** 模拟锁 *****/
conn scott/oracle
insert into dept values(50,'liang','xue');
commit;
session1: update dept set deptno=60 where dname='liang';
session2: update dept set deptno=60 where dname='liang';
复制2.2.查询锁信息
/***** 查询锁信息 *****/
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 会话所在的节点号。
--PROCESSES 客户端进程号，与v$process中的spid不是同一个。
--ISLEAF 是否源头，0代表否，1代表是
--TREE 树形结构，锁的层次。
--TREE_LEVEL 树形层次
复制2.3.查询结果
/***** 查询结果 *****/
SQL> select a.inst_id,
2 a.process,
3 a.sid,
4 a.serial#,
5 a.sql_id,
6 a.event,
7 a.status,
8 a.program,
9 a.machine,
10 connect_by_isleaf as isleaf,
11 sys_connect_by_path(a.SID || '@' || a.inst_id,' <- ') tree,
12 level as tree_level
13 from gv$session a
14 start with a.blocking_session is not null
15 connect by (a.sid || '@' || a.inst_id) = prior
16 (a.blocking_session || '@' || a.blocking_instance);
INST_ID PROCESS SID SERIAL# SQL_ID EVENT STATUS PROGRAM MACHINE ISLEAF TREE TREE_LEVEL
---------- ------------------------ ---------- ---------- ------------- ---------------------------------------------------------------- -------- ------------------------------------------------ ---------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- ----------
1 3416 10 21 8a2pqjd1vvj5d enq: TX - row lock contention ACTIVE sqlplus@single (TNS V1-V3) single 0 <- 10@1 1
1 2865 191 5 SQL*Net message from client INACTIVE sqlplus@single (TNS V1-V3) single 1 <- 10@1 <- 191@1 2
SQL>
复制2.4.查杀方法一
/***** 查杀方法一 *****/
--查杀ISLEAF=1的会话，即源头
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;
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;
复制2.5.查询结果
/***** 查询结果 *****/
SQL> select 'alter system kill session '''|| sid || '' || ',' || serial# || ',@' || inst_id || ''' immediate;' db_kill_session
2 from (select a.inst_id,
3 a.process,
4 a.sid,
5 a.serial#,
6 a.sql_id,
7 a.event,
8 a.status,
9 a.program,
10 a.machine,
11 connect_by_isleaf as isleaf,
12 sys_connect_by_path(a.SID || '@' || a.inst_id,' <- ') tree,
13 level as tree_level
14 from gv$session a
15 start with a.blocking_session is not null
16 connect by (a.sid || '@' || a.inst_id) = prior
17 (a.blocking_session || '@' || a.blocking_instance))
18 where isleaf = 1
19 order by tree_level asc;
DB_KILL_SESSION
--------------------------------------------------------------------------------
alter system kill session '191,5,@1' immediate;
SQL> select inst_id,'kill -9 ' || spid os_kill_session
2 from (select p.inst_id,
3 p.spid,
4 a.sid,
5 a.serial#,
6 a.sql_id,
7 a.event,
8 a.status,
9 a.program,
10 a.machine,
11 connect_by_isleaf as isleaf,
12 sys_connect_by_path(a.sid || '@' || a.inst_id,'<- ') tree,
13 level as tree_level
14 from gv$session a,gv$process p
15 where a.inst_id = p.inst_id
16 and a.paddr = p.addr
17 start with a.blocking_session is not null
18 connect by (a.sid || '@' || a.inst_id) = prior
19 (a.blocking_session || '@' || a.blocking_instance))
20 where isleaf = 1
21 order by tree_level asc;
INST_ID OS_KILL_SESSION
---------- --------------------------------
1 kill -9 3017
SQL>
复制2.6.查杀方法二
/***** 查杀方法二 *****/
--借助v$session中的final_blocking_instance和final_blocking_session定位锁源头
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;
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;
复制2.7.查询结果
/***** 查询结果 *****/
SQL> select 'alter system kill session ''' || ss.sid || '' || ',' || ss.serial# || ',@' || ss.inst_id || ''' immediate;' db_kill_session
2 from gv$session s,gv$session ss
3 where s.final_blocking_session is not null
4 and s.final_blocking_instance = ss.inst_id
5 and s.final_blocking_session = ss.sid
6 and s.sid <> ss.sid;
DB_KILL_SESSION
--------------------------------------------------------------------------------
alter system kill session '191,5,@1' immediate;
SQL>
SQL>
SQL> select p.inst_id,'kill -9 ' || p.spid os_kill_session
2 from gv$session s,gv$session ss,gv$process p
3 where s.final_blocking_session is not null
4 and s.final_blocking_instance = ss.inst_id
5 and s.final_blocking_session = ss.sid
6 and ss.paddr = p.addr
7 and ss.inst_id = p.inst_id
8 and s.sid <> ss.sid;
INST_ID OS_KILL_SESSION
---------- --------------------------------
1 kill -9 3017
SQL>
复制2.8.标记为KILLED会话
–当我们不加immediate的时候，会话会被标记为KILLED。
/***** 重新杀已经被标记为KILLED的会话 *****/
select ''' alter system kill session '''||c.sid||''||','||c.serial#|| ''' immediate;' kill_session
from v$session c where status='KILLED';
复制三.总结
这本书里分享的关于处理TX锁的脚本的好用之处在于利用了Oracle的SYS_CONNECT_BY_PTH函数。
3.1.SYS_CONNECT_BY_PATH函数
SYS_CONNECT_BY_PATH是一个函数，它在分层查询中用于检索在当前节点和根节点之间出现的指定列的值。
函数的特征为：
SYS_CONNECT_BY_PATH (column, delimiter)复制
该函数采用两个参数：
column 是位于分层查询中指定的表中且调用该函数的列的名称。
delimiter 是 varchar 值，用于分隔指定列中的每个条目。
3.2.scott.emp表介绍
```sql SQL> desc emp Name Type Nullable Default Comments -------- ------------ -------- ------- -------- EMPNO NUMBER(4) --员工编号 ENAME VARCHAR2(10) Y --员工姓名 JOB VARCHAR2(9) Y --员工职位（比如：打工人、老板等） MGR NUMBER(4) Y --上级的员工编号 HIREDATE DATE Y --任职时间 SAL NUMBER(7,2) Y --工资 COMM NUMBER(7,2) Y --佣金 DEPTNO NUMBER(2) Y --部门编号 SQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. SQL>复制
3.3.需求
假如就表EMP，我们需要找出每个员工的上级，按顺序排列。
如下：
张三–经理李四–老板王五
3.4.初步尝试
思路：MGR是上级的员工编号，就可以找到该员工对应的上级，从而列出来。
如下：
select b.ename||' <- '||a.ename from emp a,emp b where a.mgr = b.empno(+);
B.ENAME||'<-'||A.ENAME
------------------------
JONES <- FORD
JONES <- SCOTT
BLAKE <- JAMES
BLAKE <- TURNER
BLAKE <- MARTIN
BLAKE <- WARD
BLAKE <- ALLEN
CLARK <- MILLER
SCOTT <- ADAMS
KING <- CLARK
KING <- BLAKE
KING <- JONES
FORD <- SMITH
<- KING
14 rows selected.
复制但是结果只能显示一级，很明显，按照我自己这个办法，想更进一步需要重复一次，对于重复的工作，那肯定是递归解决。
3.5.函数实现
SQL> SELECT level, ename , SYS_CONNECT_BY_PATH(ename, '/') managers
2 FROM emp
3 CONNECT BY PRIOR empno = mgr
4 START WITH mgr IS NULL
5 ORDER BY level, ename, managers;
LEVEL ENAME MANAGERS
---------- ---------- -----------------------------------------------------
1 KING /KING
2 BLAKE /KING/BLAKE
2 CLARK /KING/CLARK
2 JONES /KING/JONES
3 ALLEN /KING/BLAKE/ALLEN
3 FORD /KING/JONES/FORD
3 JAMES /KING/BLAKE/JAMES
3 MARTIN /KING/BLAKE/MARTIN
3 MILLER /KING/CLARK/MILLER
3 SCOTT /KING/JONES/SCOTT
3 TURNER /KING/BLAKE/TURNER
3 WARD /KING/BLAKE/WARD
4 ADAMS /KING/JONES/SCOTT/ADAMS
4 SMITH /KING/JONES/FORD/SMITH
14 rows selected
SQL>
复制【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
目录
