Help on integrity constraint issue

Hi:

I'm using oracle8i on Solaris 2.7 and did the followings:

create table test1_dept(dept_no number(5) primary key, dept_name varchar2(30));

create table test1_emp(emp_no number(5) primary key, dept_no number(5), constraint dept_fk foreign key (dept_no) references test1_dept );

Then I did the following in one of the session (session 1): insert into test1_dept values(100, 'test1');

I did not commit or rollback

Then I did the following in another session (session 2): insert into test1_emp values(200, 100);

Why did session 2 start hanging or waiting for session 1 to either rollback or commit? Why didn't or can't oracle return integrity constraint violation to session 2 instead of creating deadlock if session 1 never commits or rollbacks? Returning an error seems more natural because when you do select * from test1_dept on session 2, you receive 0 rows returned anyway.

Any ideas are welcome because I would like to learn more and want to make sure the way Oracle is doing now makes sense.

Thanks in advance. Received on Fri Jan 25 2002 - 17:44:32 CST