Monday, November 22, 2010

Invisible Indexes and TM enq: Locks

Indexes could be created to prevent locking situations when foreign key columns are un-indexed.
Introducing the index could alter the execution plans and resulting plans may not be desirable.
In 11g indexes could be made invisible thus preventing the optimizer from using them (unless use invisible indexes set to true).
But this would defeat the purpose if the indexes were created to prevent the foreign key columns related locking situations.
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;
/

commit;
Create two sessions with autocommit off and run on session 1
insert into x values (11,21);
on session 2
update x set a = 12 where a = 7;
In this case session 2 will hang.

Now create an index on y.c
create index aidx on y(c);
Run the same scenario as above and this time both sessions will get executed without hanging.

Make the index invisible
alter index aidx invisible;
Run the above two session scenario and second session will start to hang.

Even though it is said, invisible indexes are maintained during DML, it appears invisible indexes cannot be used to prevent such locking situations.

If the index is made visible from the first session while the second session still hanging, second session will come out of the hang and proceed as normal.

This was tested on 11gR2 (11.2.0.1.3)