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.
