Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> REPOST: Re: Help on integrity constraint issue

REPOST: Re: Help on integrity constraint issue

From: Fernão Magalhaes <barameda_at_no.spam.hotmail.com>
Date: Fri, 25 Jan 2002 22:36:30 -0800
Message-ID: <1$--$%%%_$$-_--__$@news.noc.cabal.int>


When you SELECT for rows, Oracle does not return any for read consistency, since none has been committed yet. At the same time, for transaction integrity, it waits for DDL statements to commit.

Here is another example where Oracle behaves similar to what you describe, but may be easier to agree with:

Session 1 executes this statement:

    INSERT INTO test1_dept VALUES(100, 'test1');

Session 2 attempts to insert another row with the same primary key value:

    INSERT INTO test1_dept VALUES(100, 'test2');

Session 2 waits for Session 1 to either ROLLBACK or COMMIT. Oracle does not play a game that whoever commits first gets to insert his values :-) Therefore, since Session 1 initiated the transaction first, it has a lock on that record.

--Fernão

"Morris Luk" <morris_luk_at_my-deja.com> 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.

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Sat Jan 26 2002 - 00:36:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US