Oracle RAC环境下的阻塞(blocking blocked)介绍和实例演示
RAC环境下的阻塞不同于单实例情形，因为我们需要考虑到位于不同实例的session。也就是说之前查询的v$session，v$lock相应的应变化为全局范围来查找。本文提供了2个查询脚本，并给出实例演示那些session为阻塞者，哪些为被阻塞者。有关阻塞的概念以及单实例环境下的阻塞请参考：Oracle阻塞(blockingblocked)
1、演示环境
scott@DEVDB>select*fromv$versionwhererownum<2; BANNER -------------------------------------------------------------------------------- OracleDatabase11gEnterpriseEditionRelease11.2.0.3.0-64bitProduction --在scottsession中发布SQL语句，并未提交 scott@DEVDB>begin 2updateempsetsal=sal+100whereempno=7788; 3updatedeptsetdname='DBA'wheredeptno=10; 4end; 5/ PL/SQLproceduresuccessfullycompleted. --在leshamisession中更新emp对象 leshami@DEVDB>updatescott.empsetsal=sal-200whereempno=7788; --在usr1session中更新emp对象 usr1@DEVDB>updatescott.deptsetdname='DEV'wheredeptno=10;
2、寻找阻塞
scott@DEVDB>@block_session_rac USER_STATUSSID_SERIALCONN_INSTANCESIDPROGRAMOSUSERMACHINELOCK_TYPELOCK_MODECTIMEOBJECT_NAME ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Blocking->'20,1545'devdb120sqlplus@Linux-01(TNSV1-V3)oracleLinux-01TransactionExclusive666DEPT Blocking->'20,1545'devdb120sqlplus@Linux-01(TNSV1-V3)oracleLinux-01TransactionExclusive666EMP Waiting'49,1007'devdb149sqlplus@Linux-01(TNSV1-V3)oracleLinux-01TransactionNone618EMP Waiting'933,11691'devdb2933sqlplus@Linux-02(TNSV1-V3)oracleLinux-02TransactionNone558DEPT --通过上述脚本我们可以看到session'20,1545'锁住了对象DEPT以及EMP，而此时session'49,1007'与'933,11691'处于等待状态。 --下面是另外的一种方式来获取阻塞的情形 scott@DEVDB>@block_session_rac2 BLOCKING_STATUS ---------------------------------------------------------------------------------------------------------------------------- SCOTT@Linux-01(INST=1SID=20Serail#=1545)ISBLOCKINGUSR1@Linux-02(INST=2SID=933Serial#=11691) SCOTT@Linux-01(INST=1SID=20Serail#=1545)ISBLOCKINGLESHAMI@Linux-01(INST=1SID=49Serial#=1007) --Author:Leshami --Blog:http://blog.csdn.net/leshami
3、演示中用到的脚本
[oracle@Linux-01~]$moreblock_session_rac.sql setlinesize180 coluser_statusformata15 colsid_serialformata15 colprogramformata30wrapped colmachineformata15wrapped colosuserformata15wrapped colconn_instanceformata15 colobject_nameformata25wrapped SELECTDECODE(l.block,0,'Waiting','Blocking->')user_status, CHR(39)||s.sid||','||s.serial#||CHR(39)sid_serial, (SELECTinstance_name FROMgv$instance WHEREinst_id=l.inst_id) conn_instance, s.sid, s.program, s.osuser, s.machine, DECODE(l.TYPE, 'RT','RedoLogBuffer', 'TD','Dictionary', 'TM','DML', 'TS','TempSegments', 'TX','Transaction', 'UL','User', 'RW','RowWait', l.TYPE) lock_type--,id1 --,id2 , DECODE(l.lmode, 0,'None', 1,'Null', 2,'RowShare', 3,'RowExcl.', 4,'Share', 5,'S/RowExcl.', 6,'Exclusive', LTRIM(TO_CHAR(lmode,'990'))) lock_mode, ctime--,DECODE(l.BLOCK,0,'NotBlocking',1,'Blocking',2,'Global')lock_status , object_name FROMgv$lockl JOINgv$sessionsON(l.inst_id=s.inst_idANDl.sid=s.sid) JOINgv$locked_objecto ON(o.inst_id=s.inst_idANDs.sid=o.session_id) JOINdba_objectsdON(d.object_id=o.object_id) WHERE(l.id1,l.id2,l.TYPE)IN(SELECTid1,id2,TYPE FROMgv$lock WHERErequest>0) ORDERBYid1,id2,ctimeDESC; [oracle@Linux-01~]$moreblock_session_rac2.sql SELECTDISTINCT s1.username ||'@' ||s1.machine ||'(INST=' ||s1.inst_id ||'SID=' ||s1.sid ||'Serail#=' ||s1.serial# ||')ISBLOCKING' ||s2.username ||'@' ||s2.machine ||'(INST=' ||s2.inst_id ||'SID=' ||s2.sid ||'Serial#=' ||s2.serial# ||')' ASblocking_status FROMgv$lockl1, gv$sessions1, gv$lockl2, gv$sessions2 WHEREs1.sid=l1.sid ANDs2.sid=l2.sid ANDs1.inst_id=l1.inst_id ANDs2.inst_id=l2.inst_id ANDl1.block>0 ANDl2.request>0 ANDl1.id1=l2.id1 ANDl1.id2=l2.id2;