REPOST: 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.

• WAS CANCELLED BY =======: From: morris_luk_at_my-deja.com (Morris Luk) Control: cancel <e3ccc19b.0201251544.7a762152_at_posting.google.com> Subject: cmsg cancel <e3ccc19b.0201251544.7a762152_at_posting.google.com> Date: Mon, 28 Jan 2002 00:15:28 GMT Message-ID: <cancel.e3ccc19b.0201251544.7a762152_at_posting.google.com> X-No-Archive: yes Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.misc NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88 Lines: 1 Path: news.uni-stuttgart.de!news.ruhr-uni-bochum.de!news-koe1.dfn.de!news-was.dfn.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!news.stealth.net!msrtrans1!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp03!u&n&a&c&anceller Xref: news.uni-stuttgart.de control:40716213

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Fri Jan 25 2002 - 17:44:32 CST