Jared:
I've now run through a basic test (on 8.1.7.2.1) and lo and behold I get the
exact same results - index or no index!
For each dml I show the results of this query for the session performing the
DML:
select type, id1, id2, lmode, request, block
from v$lock
****************************
here's the process:
create table tparent (parentid number primary key);
create table tchild (childid number primary key,
parentid number,
constraint parentid_fk foreign key (parentid)
references tparent(parentid));
1) insert into tparent values (1);
LOCK INFO - shared lock on tparent (ID 26902), exclusive row lock on the row
TY ID1 ID2 LMODE REQUEST BLOCK
-- ---------- ---------- ---------- ---------- ----------
TX 196617 6339 6 0 0
TM 26902 0 3 0 0
2) insert into tchild values (1,1);
LOCK INFO - additional shared lock on tchild (ID 26904)
TY ID1 ID2 LMODE REQUEST BLOCK
-- ---------- ---------- ---------- ---------- ----------
TX 196617 6339 6 0 0
TM 26904 0 3 0 0
TM 26902 0 3 0 0
3) insert into tchild values (2,1);
LOCK INFO - no change
TY ID1 ID2 LMODE REQUEST BLOCK
-- ---------- ---------- ---------- ---------- ----------
TX 196617 6339 6 0 0
TM 26904 0 3 0 0
TM 26902 0 3 0 0
4) commit;
5) insert into tparent values (2);
LOCK INFO - again, shared lock on tparent, exclusive row lock
TY ID1 ID2 LMODE REQUEST BLOCK
-- ---------- ---------- ---------- ---------- ----------
TX 131098 6319 6 0 0
TM 26902 0 3 0 0
6) commit;
<< NO INDEX ON FOREIGN KEY >>
7) update tchild set parentid = 2 where childid = 2;
LOCK INFO - shared lock on tchild (ID 26904), no lock on tparent
TY ID1 ID2 LMODE REQUEST BLOCK
-- ---------- ---------- ---------- ---------- ----------
TX 262179 6370 6 0 0
TM 26904 0 3 0 0
8) rollback;
<< NOW ADD INDEX ON FOREIGN KEY AND REEXECUTE PREVIOUS STEP >>
9) create index tchild_i1 on tchild (parentid);
10) update tchild set parentid = 2 where childid = 2;
LOCK INFO - shared lock on tchild, no lock on tparent
TY ID1 ID2 LMODE REQUEST BLOCK
-- ---------- ---------- ---------- ---------- ----------
TX 262177 6370 6 0 0
TM 26904 0 3 0 0
So here's the question - without the index created on the foreign key
in step 9, the update in step 7, according to Oracle, should have produced
a share lock on the parent table, tparent. But here, both with and without
the index, an update made to the child table produces no locks on the parent
table.
Am I missing something?
thanks
bill
-----Original Message-----
Sent: Friday, September 06, 2002 2:23 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
The theory will make much more sense after you see it in action.
Jared
"Magaliff, Bill" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
09/06/2002 07:23 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: foreign key indexes and parent-table locking
I agree that that's the best way to see what actually happens, and I will
do
that
but I like to understand the theory, too . . .
-bill
-----Original Message-----
Sent: Thursday, September 05, 2002 5:40 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Importance: High
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" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
09/05/2002 02:23 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Magaliff, Bill
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Magaliff, Bill
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).
