关于行锁等待问题的理解
SQL> with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiter
from gv$session
where blocking_instance is not null
and blocking_session is not null)
select lpad(' ',2*(level-1))||waiter lock_tree from
(select * from lk
union all
select distinct 'root', blocker from lk
where blocker not in (select waiter from lk))
connect by prior waiter=blocker start with blocker='root';
①查询库里有哪些阻塞会话和被阻塞会话。
②查询不在被阻塞会话列表里的阻塞会话。
③将①和②合并为一个表递归查询被阻塞会话。
我创建一个表来模拟下。创建一个表lk有两个字段
1)SQL> select * from lk ;
BLOCKER WAITER
---------- ----------
1.1094 1.1077
1.1094 1.1086
2)SQL> select distinct 'root', blocker from lk where blocker not in (select waiter from lk);
'ROO BLOCKER
---- ----------
root 1.1094
3)select lpad(' ',2*(level-1))|| waiter from (select * from lk union all select distinct 'root', blocker from lk where blocker not in (select waiter from lk))
connect by prior waiter=blocker start with blocker='root';
SQL> select waiter from (select * from lk union all select distinct 'root', blocker from lk where blocker not in (select waiter from lk)) ;
WAITER
----------
1.1077
1.1086
1.1094
4)SQL> select lpad(' ',2*(level-1))|| waiter from (select * from lk union all select distinct 'root', blocker from lk where blocker not in (select waiter from lk))
2 connect by prior waiter=blocker start with blocker='root';
LPAD('',2*(LEVEL-1))||WAITER
--------------------------------------------------------------------------------
1.1094
1.1077
1.1086
这样分层就比较明显了可以看到1.1094阻塞两个会话进程。