Blockingblocked in RAC Environment)

The blocking in the RAC environment is different from that in a single instance, because we need to consider the session in different instances. That is to say, the v $ session and v $ lock in the previous query should change accordingly.

The blocking in the RAC environment is different from that in a single instance, because we need to consider the session in different instances. That is to say, the v $ session and v $ lock in the previous query should change accordingly.

The blocking in the RAC environment is different from that in a single instance, because we need to consider the session in different instances. That is to say, the v $ session, and v $ lock previously queried are changed to a global range for search. This article provides two query scripts, and provides an example to demonstrate which sessions are blocked and which are blocked. For the concept of blocking and blocking in a single instance environment, see Oracle blocking (blocking blocked)

1. Demo Environment

Scott @ DEVDB> select * from v $ version where rownum <2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

-- Publish the SQL statement in scott session and do not submit it
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.

-- Update the emp object in the leshami session
Leshami @ DEVDB> update scott. emp set sal = sal-200 where empno = 7788;

-- Update the emp object in usr1 session
Usr1 @ DEVDB> update scott. dept set dname = 'dev' where deptno = 10;

2. Search for blocking

Scott @ DEVDB> @ block_session_rac

USER_STATUS SID_SERIAL CONN_INSTANCE sid program osuser machine LOCK_TYPE LOCK_MODE CTIME OBJECT_NAME
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Blocking-> '1970 5' devdb1 20 sqlplus @ Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusive 20,154 DEPT
Blocking-> '1970 5' devdb1 20 sqlplus @ Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusive 20,154 EMP
Waiting '49,100 7' devdb1 49 sqlplus @ Linux-01 (TNS V1-V3) oracle Linux-01 Transaction None 618 EMP
Waiting '100 91 'devdb2 933,116 sqlplus @ Linux-02 (TNS V1-V3) oracle Linux-02 Transaction None 933 DEPT

-- Through the above script, we can see that session '123456' locks the object DEPT and EMP, and the session '1234567' and '1234569' are in the waiting state.

-- The following is another way to obtain blocking information.
Scott @ DEVDB> @ block_session_rac2

BLOCKING_STATUS
Bytes ----------------------------------------------------------------------------------------------------------------------------
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:

3. scripts used in the demo

[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 ',
'U', '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, 'clusive ',
LTRIM (TO_CHAR (lmode, '20140901 ')))
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;