Oracle RAC环境下的阻塞(blocking blocked)介绍和实例演示_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;