Check DATABASE BLOCKING on Oracle RAC

Use GV$LOCK to check about Database Blocking

SQL> select INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK

from gv$lock where (ID1,ID2,TYPE) in

(select ID1,ID2,TYPE from gv$lock where request>0);   

  INST_ID       SID TY       ID1       ID2     LMODE   REQUEST     CTIME     BLOCK

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

        1       141 TX    589854     27560         6         0        66         2

        2       111 TX    589854     27560         0         6        51         0

SQL> /

  INST_ID       SID TY       ID1       ID2     LMODE   REQUEST     CTIME     BLOCK

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

        1       141 TX    589854     27560         6         0        81         1

        1       117 TX    589854     27560         0         6         6         0

        2       111 TX    589854     27560         0         6        66         0

Locking Conflict Types

  1. Local locking conflict (block) - conflicting sessions are connected to the same instance (also applies to one instance of RAC)

        Drawback: V$LOCK column BLOCK contains value 1 for blocking lock (session)

  2. Global locking conflict (block) - conflicting sessions are connected to different instances (multi instance RAC only)

        Drawback: V$LOCK column BLOCK contains value 2 to mark potential conflict (value is always 2 in RAC environments unless there is local conflict)

Detecting Object from SID

select OBJECT_ID, OWNER,OBJECT_NAME from dba_objects o, GV$LOCK l where l.SID=&sid and l.ID1=o.OBJECT_ID;

Use GV$SESSION

SQL> select INST_ID, SID,BLOCKING_INSTANCE, BLOCKING_SESSION from gv$session where  BLOCKING_SESSION is not null;

  INST_ID       SID BLOCKING_INSTANCE BLOCKING_SESSION

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

        2       111                 1              141

        1       117                 1              141                

SQL> /

  INST_ID       SID BLOCKING_INSTANCE BLOCKING_SESSION

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

        1       141                 2              111

SQL> select 'Session ID=' ||SID ||'(Inst '||INST_ID||') IS BLOCKED BY Session ID='|| BLOCKING_SESSION || '(Inst '||BLOCKING_INSTANCE||')' from gv$session where  BLOCKING_SESSION is not null;

'SESSIONID='||SID||'(INST'||INST_ID||')ISBLOCKEDBYSESSIONID='||BLOCKING_SESSION||'(INST'||BLOCKING_INSTANCE||')'

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

Session ID=141(Inst 1) IS BLOCKED BY Session ID=111(Inst 2)

By Surachart