Re: Why does my process hang?

I believe you are missing an index on one of the tables. If you donot have an index, oracle does a full table lock.

Francis Small wrote in message <79v0po$dkt_at_canyon.sr.hp.com>...
>
>Why oh why oh why oh why oh why oh why oh why oh why does my process
freeze?
>
>(I'm using Oracle 7.3.3)
>
>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';
>
>
>Session #1 freezes until session #2 either commits or rolls back.
>
> AAAAAAAAAAAAAAAAAAAAAAAAAAARRRRRRRRRRRGGGGGGGGGGGGGGGGHHHHHHH!
>
>Session #1 and #2 aren't contending over the same data, they appear to be
>'merely' contending over the foreign key relationship. Apparently session
#1
>is unable to verify that it isn't severing any ties to child_tab while
session
>#2 has an uncommitted transaction pending with child_tab.
>
>Isn't this a major hit against the principle of data concurrency support?
Am
>I missing something basic? Is this a bug? (Naaah, couldn't be, it seems
to
>basic.) What is going on?
>
> Francis
>
>--
>| . . . . 5 . . . . 10. . . . 15. . . . 20. . . . 25. . . . 30. . . . 35. .
.
>Padres Francis Small - M/S M1RA
>Giants Test Engineering
>Bums Microwave Instruments
Div.
>Rockies telnet 794-3305
>Diamondbacks fthes_at_sr.hp.com
>******************** Only 53 days left until Opening Day