Re: Help on integrity constraint issue

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 - 15:12:57 CST