Re: Transactions

In article <ACU1-_k0R4_at_m-soft.spb.su>

           newsread_at_m-soft.spb.su "M.Soft news reader" writes:

: 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.

There is a small hint in one of the manuals about this: Application Developers Guide Chapter 6: Using Referential Integrity Constraints. For reasons of performance the Oracle engine is basically allowed to see the results of uncommited transactions on _indexes_.

Once you figure out why the Parent table is locked if there is no FK index on the Child table the necessity for the above becomes clear.

-- 
Jonathan Lewis