This behavior is slightly improved in 9i. Formerly Oracle placed a shared
lock on the entire unindexed foriegn key table. The lock lasted until the
operation concluded. Starting in 9i the lock is temporary. A save point
is established and the lock is actually released before the operation has
completed.
That information is basically right out of the manual. I won't pretend to
know how the 'save point' is created. The net result is reduced locking
contention for unindexed foreign keys. However, you are still far better
off to index them.
regards,
James E. Strange
Database Systems Consultant Wellpoint IT
Office (317) 287-5423 Fax (317) 287-5428
Home 317 253-6154 Cell (317) 402-1742
Oracle Certified Professional DBA
|---------+--------------------------------------------------------->
| | "Patterson Joel " |
| | <oracledba-ezmlmshield-x72559126.[Email Address Removed] | azyDBA.com> |
| | |
| | 03/31/2005 08:36 AM |
| | |
|---------+--------------------------------------------------------->
>-----------------------------------------------------------------------------------------------|
| |
| To: "LazyDBA Discussion" <[Email address protected] |
| cc: |
| Subject: RE: UnIndexed FKs |
>-----------------------------------------------------------------------------------------------|
It is because when you delete a record from a parent table, a cascading
delete will delete the child records. For that matter deleting the
child records directly leads to the same problem -- where are they?
With an index you know which exact location on disk the record is and
therefore only have to lock that record. Without one, you have to scan
the entire table and put a lock on all records because you are in the
middle of a delete, (update/insert) and you don't want changes going on
after you initiated the DML. With an index same thing, except that you
already know which records to lock, and don't have to scan for them.
-----Original Message-----
From: Kerber Andrew
[mailto:oracledba-ezmlmshield-x84328803.[Email address protected]
Sent: Thursday, March 31, 2005 8:04 AM
To: LazyDBA Discussion
Subject: RE: UnIndexed FKs
I have asked this before, and the only answer I get from Oracle is thats
the way it works. I believe it is a bug, but Oracle says its a
feature...
-----Original Message-----
From: kazi
[mailto:oracledba-ezmlmshield-x43876114.[Email address protected]
Sent: Thursday, March 31, 2005 06:10
To: LazyDBA Discussion
Subject: Re: UnIndexed FKs
In case of Un-indexed foriegn keys an delete from master table,
whole related table is locked. Why? I also want to nknow.
KAzi
fahd-m wrote:
>Hi,
> Why Un-indexed foriegn keys lead to deadlocks?
>
>Thanks,
>Fahd
>
>
>--------
>website: http://www.LazyDBA.com
>Please don't reply to RTFM questions
>Oracle documentation is here: http://tahiti.oracle.com
>To unsubscribe: see http://www.lazydba.com/unsubscribe.html
>To subscribe: see http://www.lazydba.com
>By using this list you agree to these
terms:http://www.lazydba.com/legal.html
>
>
>
>
>
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:
http://www.lazydba.com/legal.html
CONFIDENTIALITY NOTICE: This e-mail message, including any attachments,
is for the sole use of the intended recipient(s) and may contain confidential
and privileged information or otherwise protected by law. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not the
intended recipient, please contact the sender by reply e-mail and destroy
all copies of the original message.
Oracle LazyDBA home page