Yes, mine still deadlocked. My FKs were indexed properly (same
column(s)/position(s)).
-----Original Message-----
From: Chris Weiss
[mailto:oracledba-ezmlmshield-x76481314.[Email address protected]
Sent: Tuesday, August 31, 2004 12:24 PM
To: LazyDBA Discussion
Subject: RE: fk indexes? rumor or fact
The columns of a FK must be the leading columns of any index in the order of
the parent of constraint. I have never seen deadlock when this has been
the case. If the columns in the index are in the wrong order, or they are
not leading columns, then you can get deadlock.
Have you seen FK related deadlock when the indexes were constructed
correctly? I am very curious since I have supported several very large OLTP
systems.
Regards,
Chris
----------------------------------------------------
Chris Weiss, Blueoak Database Engineering, LLC
http://www.blueoakdb.com
PH: 517-381-9305
CELL: 517-974-5634
eFAX: 801-340-0740
-----Original Message-----
From: tpepling
[mailto:oracledba-ezmlmshield-x40105044.[Email address protected]
Sent: Tuesday, August 31, 2004 12:12 PM
To: LazyDBA Discussion
Subject: RE: fk indexes? rumor or fact
Also try LMTs w/ ASSM. They can also reduce deadlocking due to conflicts w/
multiple processes hitting the ITL (Interested Transaction Layer) regardless
of indexed FKs. FYI, such a deadlock graph looks sorta like so:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)------
---
Resource Name process session holds waits process session holds
waits
TX-00040059-00013582 25 36 X 24 47
S
TX-0003004b-0000f6d9 24 47 X 25 36
S
session 36: DID 0001-0019-00000002 session 47: DID 0001-0018-00000002
session 47: DID 0001-0018-00000002 session 36: DID 0001-0019-00000002
Rows waited on:
Session 47: no row
Session 36: no row
===================================================
-----Original Message-----
From: Wendry Yang
[mailto:oracledba-ezmlmshield-x29979095.[Email address protected]
Sent: Monday, August 30, 2004 9:44 PM
To: LazyDBA Discussion
Subject: Re: fk indexes? rumor or fact
AFAIK Oracle still use the locking mechanism when we try to update or delete
records on parent tables, but it should be very short since starting from
Oracle 9i, it creates savepoint, take a lock and immediately release it, and
will rollback operation when needed. Not like prior version where the lock
will be held until the update or delete operation on parent table completed
Regards,
Wendry.
----- Original Message -----
From: "Amy" <oracledba-ezmlmshield-x34455462.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Tuesday, August 31, 2004 7:58 AM
Subject: fk indexes? rumor or fact
> Being a data warehouser, I haven't kept up on the whole FK and index
> thing. I'm running version 9.2 for an OLTP database.
>
> Are indexes on the FK columns required still to prevent locking?
>
>
>
> Amy Loukota
> Oracle Certified DBA
> 1150 E University Dr, Suite 100
> Tempe, AZ 85281
> Office: 480-774-5652
> Email: amy.[Email address protected]
> AIM: amyloukota
>
> This message may contain confidential and/or privileged information. If
> you are not the addressee or authorized to receive this for the
> addressee, you must not use, copy, disclose, or take any action based on
> this message or any information herein. If you have received this
> message in error, please advise the sender immediately by reply e-mail
> and delete this message. Thank you for your cooperation.
>
>
>
>
> --------
> 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
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited. If you are not the intended
recipient, please contact the sender and delete the material from any
computer.
--------
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
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited. If you are not the intended
recipient, please contact the sender and delete the material from any
computer.
Oracle LazyDBA home page