Oracle RAC环境下的阻塞(blocking blocked)介绍和实例演示

scott@DEVDB> select * from v $version where rownum<2;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--在scott session中发布SQL语句，并未提交

scott@DEVDB> begin

  2 update emp set sal=sal+100 where empno=7788;

  3 update dept set dname= 'DBA' where deptno=10;

  4 end;

  5 /

PL /SQL procedure successfully completed.

--在leshami session中更新emp对象

leshami@DEVDB> update scott.emp set sal=sal-200 where empno=7788;

--在usr1 session中更新emp对象

usr1@DEVDB> update scott.dept set dname= 'DEV' where deptno=10;

scott@DEVDB> @block_session_rac

USER_STATUS   SID_SERIAL   CONN_INSTANCE   SID PROGRAM            OSUSER MACHINE     LOCK_TYPE    LOCK_MODE    CTIME OBJECT_NAME

--------------- --------------- ---------------- ---- ------------------------------ ------- --------------- --------------- ----------- ---------- -------------------------

Blocking ->   '20,1545'     devdb1       20 sqlplus@Linux-01 (TNS V1-V3)  oracle Linux-01    Transaction   Exclusive     666 DEPT

Blocking ->   '20,1545'     devdb1       20 sqlplus@Linux-01 (TNS V1-V3)  oracle Linux-01    Transaction   Exclusive     666 EMP

Waiting     '49,1007'     devdb1       49 sqlplus@Linux-01 (TNS V1-V3)  oracle Linux-01    Transaction   None        618 EMP

Waiting     '933,11691'    devdb2      933 sqlplus@Linux-02 (TNS V1-V3)  oracle Linux-02    Transaction   None        558 DEPT

--通过上述脚本我们可以看到session '20,1545' 锁住了对象DEPT以及EMP，而此时session '49,1007' 与 '933,11691' 处于等待状态。

--下面是另外的一种方式来获取阻塞的情形

scott@DEVDB> @block_session_rac2

BLOCKING_STATUS

----------------------------------------------------------------------------------------------------------------------------

SCOTT@Linux-01 ( INST=1 SID=20 Serail #=1545 ) IS BLOCKING USR1@Linux-02 ( INST=2 SID=933 Serial#=11691 )

SCOTT@Linux-01 ( INST=1 SID=20 Serail #=1545 ) IS BLOCKING LESHAMI@Linux-01 ( INST=1 SID=49 Serial#=1007 )

--Author : Leshami

--Blog  : http: //blog .csdn.net /leshami

[oracle@Linux-01 ~]$ more block_session_rac.sql

set linesize 180

col user_status format a15

col sid_serial format a15

col program format a30 wrapped

col machine format a15 wrapped

col osuser format a15 wrapped

col conn_instance format a15

col object_name format a25 wrapped

  SELECT DECODE (l.block, 0, 'Waiting' , 'Blocking ->' ) user_status,

      CHR (39) || s.sid || ',' || s.serial # || CHR (39) sid_serial,

      (SELECT instance_name

       FROM gv$instance

       WHERE inst_id = l.inst_id)

       conn_instance,

      s.sid,

      s.program,

      s.osuser,

      s.machine,

      DECODE (l.TYPE,

          'RT' , 'Redo Log Buffer' ,

          'TD' , 'Dictionary' ,

          'TM' , 'DML' ,

          'TS' , 'Temp Segments' ,

          'TX' , 'Transaction' ,

          'UL' , 'User' ,

          'RW' , 'Row Wait' ,

          l.TYPE)

       lock_type--,id1

            --,id2

      ,

      DECODE (l.lmode,

          0, 'None' ,

          1, 'Null' ,

          2, 'Row Share' ,

          3, 'Row Excl.' ,

          4, 'Share' ,

          5, 'S/Row Excl.' ,

          6, 'Exclusive' ,

          LTRIM (TO_CHAR (lmode, '990' )))

       lock_mode,

      ctime--,DECODE(l.BLOCK, 0, 'Not Blocking' , 1, 'Blocking' , 2, 'Global' ) lock_status

      ,

      object_name

   FROM gv$lock l

      JOIN gv$session s ON (l.inst_id = s.inst_id AND l.sid = s.sid)

      JOIN gv$locked_object o

       ON (o.inst_id = s.inst_id AND s.sid = o.session_id)

      JOIN dba_objects d ON (d.object_id = o.object_id)

   WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE

                    FROM gv$lock

                    WHERE request > 0)

ORDER BY id1, id2, ctime DESC;

[oracle@Linux-01 ~]$ more block_session_rac2.sql

SELECT DISTINCT

      s1.username

     || '@'

     || s1.machine

     || ' ( INST='

     || s1.inst_id

     || ' SID='

     || s1.sid

     || ' Serail#='

     || s1.serial #

     || ' ) IS BLOCKING '

     || s2.username

     || '@'

     || s2.machine

     || ' ( INST='

     || s2.inst_id

     || ' SID='

     || s2.sid

     || ' Serial#='

     || s2.serial #

     || ' ) '

      AS blocking_status

  FROM gv$lock l1,

     gv$session s1,

     gv$lock l2,

     gv$session s2

  WHERE   s1.sid = l1.sid

     AND s2.sid = l2.sid

     AND s1.inst_id = l1.inst_id

     AND s2.inst_id = l2.inst_id

     AND l1.block > 0

     AND l2.request > 0

     AND l1.id1 = l2.id1

     AND l1.id2 = l2.id2;