关于行锁等待问题的理解

                                    

                                                                      关于行锁等待问题的理解

                                                                                                                                                     参考msdnchina检查行锁等待问题的脚本

                                

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

这样分层就比较明显了可以看到１.１０９４阻塞两个会话进程。