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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: foreign key indexes and parent-table locking

Re: foreign key indexes and parent-table locking

From: <Jared.Still_at_radisys.com>
Date: Thu, 05 Sep 2002 14:50:49 -0800
Message-ID: <F001.004C9441.20020905145049@fatcity.com>


Bill,

Rather than try to understand that explanation, you may find it more educational to create a pair of tables with a parent/child relationship via foreign key.

Put some data in the tables, then do updates and deletes both with and without FK indexes.

Examine dba_locks while doing so and observe the lock modes.

This will be much easier to understand than the 'documentation'

Jared

"Magaliff, Bill" <Bill.Magaliff_at_lendware.com>
Sent by: root_at_fatcity.com
 09/05/2002 02:23 PM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        foreign key indexes and parent-table locking


Hi,

I'm trying to understand the whole issue of foreign key indexes and locking.
Found a note on metalink (11828.1) that seems to explain it, but either it's
not clear or I'm missing something.

"Why then, does an index on the foreign key mean that the shared lock on
the
parent table is not required?
"When a row in the child table is inserted, deleted or has its foreign key
updated, the corresponding index entry/entries is/are also locked. When an application attempts to delete or update the primary key of a parent row, it
reads the FIRST corresponding entry in the child's foreign key index (uncommitted or otherwise) and, if locked, waits for that lock to be released."
So far so good . . . this next piece, too, seems to make sense:
"If the modified child row is NOT the first occurrence of the foreign key
in
the index then the parent modification must be prevented anyway, regardless
of the outcome of uncommitted transactions on other child rows with this key."
But now here's the part that leaves me hanging . . .
"Hence the error can be flagged immediately and so the transaction is not
forced to wait. This mechanism ensures the minimum reads and wait times to maintain data consistency. "

Can anyone help by either translating this last part or rephrasing it? Or explaining the issue differnetly?

Thanks

bill

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Magaliff, Bill
  INET: Bill.Magaliff_at_lendware.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Sep 05 2002 - 17:50:49 CDT

Original text of this message

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