Re: Help on integrity constraint issue

Perhaps the Oracle architectural model has totally escaped you. Oracle is NOT SQL Server. There are no dirty reads. Session two hasn't a clue what session 1 is doing until session 1 commits.

And please reconsider creating primary keys as you are doing it. All this does is produce objects with nonsense names. Use alter table in the future.

Daniel Morgan

Morris Luk wrote:

> 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 - 15:14:09 CST