欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-29 13:20:17
rac环境下的阻塞不同于单实例情形,因为我们需要考虑到位于不同实例的session。也就是说之前查询的v$session,v$lock相应的应变化为全局范围来查找。本文提供...

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;