Re: Help on integrity constraint issue

Hi Mike:

Thanks for your idea. But your delete and insert example is based on the assumption that the row in test1_dept table has been seen in both session 1 and 2 already. In that case, I agree that it should be handled differently. But my test example is different. The row in test1_dept table can only be seen in session 1 alone, not in sesion 2 because session 1 has not committed yet. So that's why I think returning an error is more intuitive rather than creating deadlock. Have I made myself clear?

mike2322_at_hotmail.com (Mike Liu) wrote in message news:<2262aa8e.0201291144.5bab1613_at_posting.google.com>...
> 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 - 16:20:56 CST