RE: primary/ foreign key constraints for oltp, overhead?...soluti

Jared,
I tried again and got the same result.
on oracle 8.1.7 (RBO).

session1 : delete from emp where deptno = 10;

session2 : delete from dept where deptno = 30; session 2 hanged and waited till transaction in session one was committed.

the max_trans on both tab is set to 255, incase this raises doubt. are you getting a different result? Is the FK present on the detail table you are trying with?

rgds
amar

-----Original Message-----
Sent: Monday, June 11, 2001 10:25 AM
To: Multiple recipients of list ORACLE-L overhead?...soluti

On Sunday 10 June 2001 21:50, Amar Kumar Padhi wrote:
> - locks
> Oracle generates internal locks on dict tables to maintain the data
> integrity.
> e.g.: I deleted a record from detail table in one session. In another
> session I tried to delete a master record not related to the deleted
detail
> record. The 2nd session hanged. There were row-exclusive locks present on
> the detail table and an exclusive lock present on an internal table (I
> couldn't access). The same is the case with update.
>
> Oracle locks both the master & detail table records when the master record
> is deleted and the foreign key is created with on delete cascade option.
>
> Bottomline, Oracle doesn't allow altering of master table primary key when
> active transactions exists on detail table. Oracle will allow update of
> columns other than the primary key.

You should probably test this scenario, as it works fine for me.

Delete the a child detail row.
Delete an unrelated parent row, with delete cascade on the child table.

Works fine.

Possibly are missing an index? Some of these rules have changed I know, but I'll let you look up the details.

Bottom line, it does work.

Jared

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.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: Amar Kumar Padhi
  INET: TS2017_at_emirates.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).