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