Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

indexing FK to avoid locks

Anthony.PSep 14 2012 — edited Sep 17 2012
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!

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 15 2012
Added on Sep 14 2012
6 comments
455 views