Blocking blocked in RAC Environment)

Blocking blocked 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 corresponding strain of the previously queried v $ session and v $ lock is global. 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:

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------