RAC环境中的阻塞查找锁
RAC环境下的阻塞不同于单实例情形，因为我们需要考虑到位于不同实例的session。也就是说之前查询的v$session，v$lock相应的应变化为全局范围来查找。本文提供了2个查询脚本，并给出实例演示那些session为阻塞者，哪些为被阻塞者。有关阻塞的概念以及单实例环境下的阻塞请参考：Oracle 阻塞(blocking blocked)
成都创新互联公司是一家集网站建设,静宁企业网站建设,静宁品牌网站建设,网站定制,静宁网站建设报价,网络营销,网络优化,静宁网站推广为一体的创新建站企业，帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿，时刻以成就客户成长自我，坚持不断学习、思考、沉淀、净化自己，让我们为更多的企业打造出实用型网站。
1、演示环境
[sql] view plain copy
print?
- 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、寻找阻塞
[sql] view plain copy
print?
- 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、演示中用到的脚本
[sql] view plain copy
print?
- [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;
分享标题：RAC环境中的阻塞查找锁
文章出自：http://scyanting.com/article/jdscos.html
