Transactions

Hello!

I have noticed something in Oracle Server behaviour. Assume that I have a table THE_TABLE( THE_FIELD NUMBER PRIMARY KEY) I.e. there is an unique constraint on this field. Then let's suppose that there are two client transactions TRANSACTION_A and TRANSACTION_B:

Time         TRANSACTION_A                   TRANSACTION_B
1.         INSERT INTO THE_TABLE
           VALUES(1);

2.                                      SELECT * FROM THE_TABLE
                                        WHERE THE_FIELD=1
                                        FOR UPDATE NOWAIT;
                                        (select is empty 'cause
                                        TRANSACTION_A did not commit
                                        yet;  that is just to make
                                        sure that there is no danger
                                        to perform the next operation!!!)

3.                                      INSERT INTO THE_TABLE VALUES(1);

                                        Transaction hangs!!!!! There is no
                                        response from the Oracle until
                                        time point 4!

4.      (COMMIT)                         Error: primary key violation
        (ROLLBACK)                       ( 1 row inserted)

That means that TRANSACTION_B "feels" TRANSACTION_A!? I did not find any description of such situation in the manuals. Of course, it is not a VERY big disaster, but it is VERY interesting to hear opinions of Oracle gurus.

May be it is more natural to let the second transaction insert the row and then give an error message while the transaction is commited? (or at least to have this as an option)? May be Oracle already has such an option?

Anton Eskov
StPetersburg, Russia Received on Tue Dec 27 1994 - 13:36:20 CET