RAC环境中的阻塞 查找锁

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

1、演示环境

1. scott@DEVDB> select * from v$version where rownum<2;
2. BANNER
3. --------------------------------------------------------------------------------
4. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
5. --在scott session中发布SQL语句，并未提交
6. scott@DEVDB> begin
7. 2 update emp set sal=sal+100 where empno=7788;
8. 3 update dept set dname='DBA' where deptno=10;
9. 4 end;
10. 5 /
11. PL/SQL procedure successfully completed.
12. --在leshami session中更新emp对象
13. leshami@DEVDB> update scott.emp set sal=sal-200 where empno=7788;
14. --在usr1 session中更新emp对象
15. usr1@DEVDB> update scott.dept set dname='DEV' where deptno=10;

2、寻找阻塞

1. scott@DEVDB> @block_session_rac
2. USER_STATUS SID_SERIAL CONN_INSTANCE SID PROGRAM OSUSER MACHINE LOCK_TYPE LOCK_MODE CTIME OBJECT_NAME
3. --------------- --------------- ---------------- ---- ------------------------------ ------- --------------- --------------- ----------- ---------- -------------------------
4. Blocking -> '20,1545' devdb1 20 sqlplus@Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusive 666 DEPT
5. Blocking -> '20,1545' devdb1 20 sqlplus@Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusive 666 EMP
6. Waiting '49,1007' devdb1 49 sqlplus@Linux-01 (TNS V1-V3) oracle Linux-01 Transaction None 618 EMP
7. Waiting '933,11691' devdb2 933 sqlplus@Linux-02 (TNS V1-V3) oracle Linux-02 Transaction None 558 DEPT
8. --通过上述脚本我们可以看到session '20,1545' 锁住了对象DEPT以及EMP，而此时session '49,1007'与'933,11691'处于等待状态。
9. --下面是另外的一种方式来获取阻塞的情形
10. scott@DEVDB> @block_session_rac2
11. BLOCKING_STATUS
12. ----------------------------------------------------------------------------------------------------------------------------
13. SCOTT@Linux-01 ( INST=1 SID=20 Serail#=1545 ) IS BLOCKING USR1@Linux-02 ( INST=2 SID=933 Serial#=11691 )
14. SCOTT@Linux-01 ( INST=1 SID=20 Serail#=1545 ) IS BLOCKING LESHAMI@Linux-01 ( INST=1 SID=49 Serial#=1007 )
15. --Author : Leshami
16. --Blog : http://blog.csdn.net/leshami

3、演示中用到的脚本

1. [oracle@Linux-01 ~]$ more block_session_rac.sql
2. set linesize 180
3. col user_status format a15
4. col sid_serial format a15
5. col program format a30 wrapped
6. col machine format a15 wrapped
7. col osuser format a15 wrapped
8. col conn_instance format a15
9. col object_name format a25 wrapped
10. SELECT DECODE (l.block, 0, 'Waiting', 'Blocking ->') user_status,
11. CHR (39) || s.sid || ',' || s.serial# || CHR (39) sid_serial,
12. (SELECT instance_name
13. FROM gv$instance
14. WHERE inst_id = l.inst_id)
15. conn_instance,
16. s.sid,
17. s.program,
18. s.osuser,
19. s.machine,
20. DECODE (l.TYPE,
21. 'RT', 'Redo Log Buffer',
22. 'TD', 'Dictionary',
23. 'TM', 'DML',
24. 'TS', 'Temp Segments',
25. 'TX', 'Transaction',
26. 'UL', 'User',
27. 'RW', 'Row Wait',
28. l.TYPE)
29. lock_type--,id1
30. --,id2
31. ,
32. DECODE (l.lmode,
33. 0, 'None',
34. 1, 'Null',
35. 2, 'Row Share',
36. 3, 'Row Excl.',
37. 4, 'Share',
38. 5, 'S/Row Excl.',
39. 6, 'Exclusive',
40. LTRIM (TO_CHAR (lmode, '990')))
41. lock_mode,
42. ctime--,DECODE(l.BLOCK, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') lock_status
43. ,
44. object_name
45. FROM gv$lock l
46. JOIN gv$session s ON (l.inst_id = s.inst_id AND l.sid = s.sid)
47. JOIN gv$locked_object o
48. ON (o.inst_id = s.inst_id AND s.sid = o.session_id)
49. JOIN dba_objects d ON (d.object_id = o.object_id)
50. WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE
51. FROM gv$lock
52. WHERE request > 0)
53. ORDER BY id1, id2, ctime DESC;
54. [oracle@Linux-01 ~]$ more block_session_rac2.sql
55. SELECT DISTINCT
56. s1.username
57. || '@'
58. || s1.machine
59. || ' ( INST='
60. || s1.inst_id
61. || ' SID='
62. || s1.sid
63. || ' Serail#='
64. || s1.serial#
65. || ' ) IS BLOCKING '
66. || s2.username
67. || '@'
68. || s2.machine
69. || ' ( INST='
70. || s2.inst_id
71. || ' SID='
72. || s2.sid
73. || ' Serial#='
74. || s2.serial#
75. || ' ) '
76. AS blocking_status
77. FROM gv$lock l1,
78. gv$session s1,
79. gv$lock l2,
80. gv$session s2
81. WHERE s1.sid = l1.sid
82. AND s2.sid = l2.sid
83. AND s1.inst_id = l1.inst_id
84. AND s2.inst_id = l2.inst_id
85. AND l1.block > 0
86. AND l2.request > 0
87. AND l1.id1 = l2.id1
88. AND l1.id2 = l2.id2;