Re: Help on integrity constraint issue

But why does Oracle think the game that whoever initiates first gets to lock first is a good idea? Espeacially when your application is highly multi-user and concurrent and if you lock it and never commit, this can create deadlock very easily and I don't see the disadvantage of whoever commits first gets to insert because in either case, one of the two would get an error eventually when both commit. But if other database servers do it the same way, they may have a good reason which I'm not aware of. I just can't verify because Oracle is the only thing we have. Do you know if other products behave the same way?

Thanks in advance.

"Fern? Magalhaes" <barameda_at_no.spam.hotmail.com> wrote in message news:<OENRUPjpBHA.1440_at_cpimsnntpa03>...
> 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.
Received on Mon Jan 28 2002 - 21:13:29 CST