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

前端技术 2023/09/05 Oracle

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;

本文地址：https://www.stayed.cn/item/14641

转载请注明出处。

本站部分内容来源于网络,如侵犯到您的权益,请 联系我

我的博客

人生若只如初见，何事秋风悲画扇。

我的标签

随笔档案