Oracle Blocking locks.

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
------- ------- -- ---------- ---------- ----- ------- ---------- -----
3 220 TX 1376290 8505052 6 0 22 2
4 237 TX 1376290 8505052 0 6 22 0
3 220 TX 2621476 11211311 0 4 22 0
4 237 TX 2621476 11211311 6 0 22 2

Finding root blocker

Run query provided in Script section and do one of the following.

(1) Find and kill root blockers

a) - Find oldest resource request - row with highest CTIME (this is row L1)

b) - Exists there another row with the same SID as L1? (this is row L2 if exists)

NOT - this is root blocker, kill it

YES - Find row with the same values of ID1,ID2 columns as in L2 where LOCK > 0 (this is row L3)

- Repeat (b) with L3 (L3 becomes L1) until You find root blocker

(2) Or use simple rule (may not be best)

a) Kill oldest blocking session (highest CTIME)
b) Run script again and repeat (a) until blocking session exists

reference : MetaLink : Detecting Blocking Sessions in RAC and non-RAC (Enqueue Locks) Environments [ID 398519.1]

== TaTsHuYa ==