Re: Help on integrity constraint issue

Hi Mike:

I think I've observed this behavior from my test result already. But I'm more interested in knowing why doing it this way makes more sense than returning a constraint violation error. When a transaction has not committed yet, it means it's not real yet and not ready for other sessions to look at. So returning an error in my view is more natural or intuitive.

mike2322_at_hotmail.com (Mike Liu) wrote in message news:<2262aa8e.0201281312.2f5287eb_at_posting.google.com>...
> morris_luk_at_my-deja.com (Morris Luk) wrote in message news:<e3ccc19b.0201251544.7a762152_at_posting.google.com>...
> > 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.
>
> When you do 'insert into test1_emp values(200, 100)' in session 2,
> Oracle need to acquire an exclusive lock on the row (where dept_no =
> 100 ) in test1_dept table because it has to make sure no body can
> delete or update this row before you commit in session 2. Since you
> already locked the row in test1_dept table in session 1 with your
> insert statement, session 2 has to wait for the lock. If you try
> 'insert into test1_emp values(200, 200)' in session 2, you are going
> to get a constraint violation error right away.
>
> hth,
> Mike
Received on Mon Jan 28 2002 - 21:28:07 CST