Hi,
I've seen many many posts here where guys have troubles with locks created on parent table when editing a child table.
As I can see here: http://docs.oracle.com/cd/B19306_01/server.102/b14220/data_int.htm#sthref3103 and on this forum, creating index on FK columns will resolve this issue.
I wanted to test this behaviour, but I can't reproduce it. Is there someone who could tell me what I'm doing wrong in this test?
create table t1 (id NUMBER primary key, txt VARCHAR2(10));
create table t2 (id NUMBER primary key, idt references t1(id) );
insert into t1 values (1,'one');
insert into t1 values (2,'two');
insert into t1 values (3,'three');
insert into t2 values (1,1);
insert into t2 values (2,2);
commit;
update t2 set idt=1 where id=2;
As you can see, I have no index. So when I check locks, I can see both table are locked with SX:
Sid/Serial Name lkType lkMode lkReq Seconds Blocking? ID1 ID2 Object
---------- ---------------- ------ ------ ------ -------- --------- --------- --------- ----------------
29,110 PAUL TX X 3 65580 680 T1
29,110 PAUL TM SX 3 16306 0 T1
29,110 PAUL TM SS 3 16304 0 T1
29,110 PAUL TX X 3 65580 680 T2
29,110 PAUL TM SX 3 16306 0 T2
29,110 PAUL TM SS 3 16304 0 T2
Now, I perform the following:
rollback;
create index ii on T2(idt);
commit;
update t2 set idt=1 where id=2;
But then I still have the same locks:
Sid/Serial Name lkType lkMode lkReq Seconds Blocking? ID1 ID2 Object
---------- ---------------- ------ ------ ------ -------- --------- --------- --------- ----------------
29,110 PAUL TX X 0 524305 889 T1
29,110 PAUL TM SX 0 16306 0 T1
29,110 PAUL TM SS 0 16304 0 T1
29,110 PAUL TX X 0 524305 889 T2
29,110 PAUL TM SX 0 16306 0 T2
29,110 PAUL TM SS 0 16304 0 T2
If I update "id" rather than "idt", then I only see locks on T2 with or without index.
I obviously forgot something or doing something wrong... Could someone help? (I'm using 10.2.0.5)
Thanks!