SQL Sinppet : Inter Instance Lockings

Identify the inter instance locks in a RAC environment.

Select (Select locking.sid||','||se.serial# From Gv$session Se Where Se.Sid=Locking.Sid And Se.Inst_Id = Locking.Inst_Id) As Locking_Session_serial,
Locking.Type As holding_lock_Type,
Locking.Inst_Id As Lock_Instance,
Decode (Locking.Lmode,0,'None',1,'Null',2,'Row-S',3, 'Row-X',4,'Share',5,'S/Row',6,'Exclusive') Locking_Mode,
(Select waiting.sid||','||se.serial# From Gv$session Se Where Se.Sid=Waiting.Sid And Se.Inst_Id = Waiting.Inst_Id) As Waiting_Session,
Waiting.Type As Wait_Lock_Type,
Waiting.Inst_Id As Wait_Instance,
DECODE(waiting.REQUEST,0,'None',1,'Null',2,'Row-S',3, 'Row-X',4,'Share',5,'S/Row',6,'Exclusive') REQUEST_mode,
(Select Prev_Sql_Id||' @ '||Machine From Gv$session Se Where Se.Sid=Locking.Sid And Se.Inst_Id = Locking.Inst_Id) Locking_Sql_And_Machine,
(Select Sql_Id||' @ '||Machine From Gv$session Se Where Se.Sid=Waiting.Sid And Se.Inst_Id = Waiting.Inst_Id) Waiting_Sql_and_machine,
Case When Locking.Type='TM' Then
(select object_name from dba_objects where object_id=locking.id1)
end as TM_lock_Object
FROM Gv$lock Locking,
  Gv$lock Waiting
WHERE Locking.Id1   = Waiting.Id1
And Locking.Id2     = Waiting.Id2
And Locking.Block   > 0
And Waiting.Request >0;

Above gives sid,serial# from locking and waiting sessions (makes it easy to kill them), client machine names of the session in the lock situation as well as sql ids to identify the sql involved in the lock and instance ids.

There's also gv$global_blocked_locks view which gives global locks.

SELECT g.inst_id INST,
  s.sid,
  g.type,
  s.username,
  s.serial#,
  s.process,
  DECODE (LMODE,0,'None',1,'Null',2,'Row-S',3, 'Row-X',4,'Share',5,'S/Row',6,'Exclusive') LMODE,
  DECODE(REQUEST,0,'None',1,'Null',2,'Row-S',3, 'Row-X',4,'Share',5,'S/Row',6,'Exclusive') REQUEST
FROM gv$global_blocked_locks g,
  gv$session s
WHERE g.sid   = s.sid
And G.Inst_Id = S.Inst_Id
ORDER BY state;

To test this create the tables listed in TM Enq post.
From instance 1 run

update y set c = 8 where d=10;

and from instance 2 run

update x set a = 12 where a = 7;

second session will hang. From a third session run the first SQL above to identify the locks and objects involved.



Kill blocking sessions across all instances

Select (Select 'alter system kill session '''||locking.sid||','||se.serial#||',@'||locking.inst_id||''';' 
From Gv$session Se Where Se.Sid=Locking.Sid And Se.Inst_Id = Locking.Inst_Id) As kill_stmt
FROM Gv$lock Locking,
  Gv$lock Waiting
WHERE Locking.Id1   = Waiting.Id1
And Locking.Id2     = Waiting.Id2
And Locking.Block   > 0
And Waiting.Request >0;


KILL_STMT
--------------------------------------------------
alter system kill session '762,397,@2';
alter system kill session '16,8893,@1';