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

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

1、演示环境

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、寻找阻塞

@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、演示中用到的脚本

') 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,'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,'Not Blocking','Blocking','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,TYPE FROM gv$lock WHERE request > 0) ORDER BY id1,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;