查杀根锁会话

场景：

在业务场景复杂的情况下，特别是行锁表锁并行等很多会话的时候，查出来的阻塞链条是很长的(级联锁)。并且，由于会话多，所以查出来的锁的源头也不一定是同一个，那么这就导致在很紧急的情况下，有杀错会话的可能，发现gv$session视图中存在FINAL_BLOCKING_SESSION和BLOCKING_SESSION的字段，充分理解并加以利用即可理清楚思路。
特提供个sql

其一：查出阻塞链条

col INST_ID for 99
col TREE for a30
select *
from (select a.inst_id, a.sid, a.serial#,
a.sql_id,
a.event,
a.status,
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;

其二：直接查杀根锁sql(为了防止查错dba的会话，多了一个过滤sqlplus的进程的条件)

col ksql for a40
set lines 199
set pages 9999
SELECT distinct
'alter system kill session '''||A.sid||','||A.serial#||',@'||A.inst_id||''' immediate;'
FROM GV$SESSION A,GV$SESSION B
WHERE A.SID=B.FINAL_BLOCKING_SESSION
AND A.USERNAME IS NOT NULL
AND A.INST_ID=B.INST_ID
AND A.AUDSID<>B.AUDSID
--and a.program not like '%sqlplus%' and
;

su - oracle
sqlplus / as sysdba
select * from v$mystat where rownum = 1;
conn SCOTT/SCOTT

17:27:35 SCOTT@zytrac2>SELECT * FROM EMP;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

14 rows selected.

Elapsed: 00:00:00.09

 

思路：

第一个会话先更新7788，然后第二个会话更新7839，然后再更新7788，这样第一个会话就阻塞了第二个会话;如果后面会话一直更新7788，就会是第一个会话阻塞后面所有会话；所以应该更新7839，这样，第二个会话就会阻塞后面的会话。即如下：

时间点1：61
select * from v$mystat where rownum = 1;
update emp set sal = 8000 where empno = 7788;

时间点2：29
select * from v$mystat where rownum = 1;
delete from emp where empno = 7839;
update emp set job = 'MANAGER' where empno = 7788;
rollback;

时间点3：43
select * from v$mystat where rownum = 1;
update emp set sal = 15000 where empno = 7839;
rollback;

时间点4：58
select * from v$mystat where rownum = 1;
update emp set job = 'CEO' where empno = 7839;
rollback;

--查出阻塞链条
col INST_ID for 99
col TREE for a30
select *
from (select a.inst_id, a.sid, a.serial#,
a.sql_id,
a.event,
a.status,
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;

INST_ID SID SERIAL# SQL_ID EVENT STATUS ISLEAF TREE TREE_LEVEL
------- -------- ------- -------------------- ------------------------------ ---------- ---------- ------------------------------ ----------
2 61 65 SQL*Net message from client INACTIVE 1 <- 29@2 <- 61@2 2
2 61 65 SQL*Net message from client INACTIVE 1 <- 58@2 <- 29@2 <- 61@2 3
2 61 65 SQL*Net message from client INACTIVE 1 <- 43@2 <- 29@2 <- 61@2 3

Elapsed: 00:00:00.05

可以看出，第一个会话61，阻塞了第二个会话29，第二个会话29，阻塞了第三四个会话58和43，即产生级联锁。

 

17:32:22 SYS@zytrac2>select inst_id, sid, FINAL_BLOCKING_SESSION,BLOCKING_SESSION from gv$session where SID=61;
INST_ID SID FINAL_BLOCKING_SESSION BLOCKING_SESSION
------- -------- ---------------------- ----------------
2 61

Elapsed: 00:00:00.60
17:33:11 SYS@zytrac2>17:33:11 SYS@zytrac2>select inst_id, sid, FINAL_BLOCKING_SESSION,BLOCKING_SESSION from gv$session where SID=29;
INST_ID SID FINAL_BLOCKING_SESSION BLOCKING_SESSION
------- -------- ---------------------- ----------------
2 29 61 61

Elapsed: 00:00:00.03
17:33:21 SYS@zytrac2>select inst_id, sid, FINAL_BLOCKING_SESSION,BLOCKING_SESSION from gv$session where SID=58;
INST_ID SID FINAL_BLOCKING_SESSION BLOCKING_SESSION
------- -------- ---------------------- ----------------
2 58 61 29

Elapsed: 00:00:00.01
17:33:35 SYS@zytrac2>select inst_id, sid, FINAL_BLOCKING_SESSION,BLOCKING_SESSION from gv$session where SID=43;
INST_ID SID FINAL_BLOCKING_SESSION BLOCKING_SESSION
------- -------- ---------------------- ----------------
2 43 61 29

进一步查看，第三四个会话43和58，根据gv$session视图中存在FINAL_BLOCKING_SESSION和BLOCKING_SESSION的字段可知，阻塞第三个的会话(58)是29，阻塞第三个的会话(58)的根锁是61;
第四个会话也是这样。

--直接查杀根锁sql(为了防止查错dba的会话，多了一个过滤sqlplus的进程的条件)
col ksql for a40
set lines 199
set pages 9999
SELECT distinct
'alter system kill session '''||A.sid||','||A.serial#||',@'||A.inst_id||''' immediate;'
FROM GV$SESSION A,GV$SESSION B
WHERE A.SID=B.FINAL_BLOCKING_SESSION
AND A.USERNAME IS NOT NULL
AND A.INST_ID=B.INST_ID
AND A.AUDSID<>B.AUDSID
--and a.program not like '%sqlplus%' and
;

'ALTERSYSTEMKILLSESSION'''||A.SID||','||A.SERIAL#||',@'||A.INST_ID||'''IMMEDIATE;'
----------------------------------------------------------------------------------------------------------------------------------------
alter system kill session '61,65,@2' immediate;

参考：
https://www.cnblogs.com/jyzhao/p/8716546.html