Re: Help on integrity constraint issue

morris_luk_at_my-deja.com (Morris Luk) wrote in message news:<e3ccc19b.0201281928.482331c5_at_posting.google.com>...
> 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

I posted earlier but it probably got lost. I'm sorry if you see this twice.

I was wrong about the lock. I should have tried it myself. When inserting into the child table in session 2, it doesn't need the exclusive lock on the row in the parent table. It just need to wait on the TX lock created in session 1. Once session 1 commits, session 2 doesn't need any lock on the parent table. If session 1 try to delete the record where dept_no = 100 before session 2 commits, it just need to acquire a table lock on the child table or wait on the TX lock created in session 2 depending on whether there is an index on the foreign key column.

Now the question is why Oracle doesn't use the "consistent" value to enforce the constraint but rather wait on the lock. I didn't find any documents on this, so I'm guessing here. Suppose we have a record in dept table where dept_no = 100, we do

1. in session 1, delete from dept where dept_no = 100;
2. in session 2, insert into emp values (1, 'mike', 100);

If Oracle use the "consistent" value to varify the constraint, step 2 should be fine because we still see the record in the dept table where dept_no = 100 in session 2. However, if we commit in session 1 and session 2 now, one of those commits has to fail, which means you have to check the constraint again at commit time, which is not good. So I think wait on the lock is more efficient and probably the only correct way to do it.

Thanks,
Mike Received on Tue Jan 29 2002 - 13:44:09 CST