Re: Why does my process hang?

Francis Small asked why processes hung after doing the following :-

>Create a parent and child table:
>
>create table parent_tab (
> pfield varchar(20)
> primary key);
>
>create table child_tab (
> pfield varchar(20)
> referencing parent_tab);
>
>Start up two session of SQL-Plus:

.
>1) In session #1 execute:
>
> insert into parent_tab values ('a');
>
>2) In session #2 execute:
>
> insert into parent_tab values ('b');
> insert into child_tab values ('b');
>
>3) In session #1 execute:
>
> delete from parent_tab where pfield = 'a';
>

I believe that you may be suffering at the hands of an Oracle feature (!!) which causes statement 3 to lock the child table in order to confirm whether or not the delete from the parent table violates the foreign key constraint.

Once you issue a commit or rollback in session 2, session 1 will be able to acquire the table lock it needs and delete the row from the parent table.

Because of this feature, Oracle recommend that you index the foreign key column(s) in the child table - so that the index can be used for any foreign key constraint checking and therefore negate the need to acquire the table lock on the child table.

I hope this helps ....... Jerry Mckeon Received on Thu Feb 11 1999 - 16:21:53 CST