JProfiler Setup

set Both x86 and x64 on the LD_LIBRARY_PATH

/home/demo/test/jprofiler4/bin/linux-x86:/home/demo/test/jprofiler4/bin/linux-x64

if not following error happens

Error occurred during initialization of VM
Could not find agent library on the library path or in the local directory: jprofilerti

TM enq: Locking

If an index does not exists on a foreign key column then,
when an update happens on that foreign key column the referenced column on the parent table is locked. No update will be possible on this column. Other columns in the parent table are not affected.

create table x (a number primary key, b number);
create table y (c number,d number, foreign key (c) references x(a));

begin
for i in 1 .. 10
loop
insert into x values (i, i + 10);
end loop;
end;
/

begin
for i in 1 .. 5
loop
insert into y values (i, i + 5);
end loop;
end;
/

session 1
update y set c = 8 where d=10;

1 row updated.

session 2
update x set a = 12 where a = 7;

hangs.....

query dba_waiters from session 1
select lock_type,mode_held,mode_requested,lock_id1 from dba_waiters;

LOCK_ MODE_HELD MODE_REQUE LOCK_ID1
----- ---------- ---------- ----------
DML Row-X (SX) Share 227738

lock_id1 gives the object id of the child table which has the lock

select object_name from user_objects where object_id=227738;
object_name
-----------
Y

quering v$lock it is possible to find the parent table as well.

There's also a insert, update/delete combination which will leave one session hanging.

session 1
insert into x values (11,21);

session 2
update x set a = 12 where a = 7;

or

delete from x where b = 18;

hangs....

query dba_waiters from session 1
select lock_type,mode_held,mode_requested,lock_id1 from dba_waiters;

LOCK_ MODE_HELD MODE_REQUE LOCK_ID1
----- ---------- ---------- ----------
DML Row-X (SX) Share 227738

select object_name from user_objects where object_id=227738;
object_name
-----------
Y

running the delete or update statement first and insert statement second doesn't cause this blocking.