RAC环境下的堵塞(blocking blocked)

RAC环境下的堵塞不同于单实例情形，由于我们须要考虑到位于不同实例的session。也就是说之前查询的v$session，v$lock对应的应变化为全局范围来查找。本文提供了2个查询脚本，并给出实例演示那些session为堵塞者。哪些为被堵塞者。有关堵塞的概念以及单实例环境下的堵塞请參考：Oracle 堵塞(blocking blocked)

1、演示环境

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;

2、寻找堵塞

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

3、演示中用到的脚本

[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;