"Magaliff, Bill" wrote: > > 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
The way I understand the issue is that, as it appears (not very clearly) in the note, the problem is with updating (in the general sense of 'modifying') the PK (parent) table. If you want to delete a row, you must check that there is no child record. How can I find child records? If the FK is indexed, no problem, it can be quickly done. The problem, as always,is with concurrency. If I want to delete the row with key K at the same time as somebody is trying to insert the first child record for K, I can check the index, lock very briefly the FK table to prevent an unwanted insertion, delete my row and release the lock on the child table. If I have no index, it can take me hours to check for the non-existence of child records, and moreover read consistency may give me, in this context, a 'wrong' result - the absence of child record at the time of the beginning of the check is no absolute guarantee. What can I do to play safe? Well, get an exclusive lock on the child table to be certain that no one is messing up with it. Now let's turn ourselves to a user wanting to insert a record into the FK table. What can I do to prevent the unpleasant situation described above when I want to insert a row? Preventing somebody from modifying the PK table would look like a good idea. Let's take a share lock on it, then. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
