George, Well with no indexes on the c table, I can see the deadlock problem easily occurring. You update table b, it throws a full table lock on table c because of no index. Another update comes along and you get the b/c lock dance. Put the index on table c and monitor. Hope this helps. Tom -----Original Message----- From: Leonard, George [mailto:GLeonard@xxxxxxxxxxxxx] Sent: Friday, January 28, 2005 7:26 AM To: Tim Gorman; oracle-l@xxxxxxxxxxxxx Cc: Desplace, Laura Subject: RE: Referential indexes We are getting blocking locks on a table B. The developers came up with the idea that since the child table (B) does not have a dedicated non unique index on the acc num column (although the column is the first column of the PK) that this is causing the blocking locks. B.accnum=3D A.accnum We are saying column is already indexed and this is not the problem. We/DBA's have however found that this table (b) is part of a 3 layer set of tables actually. c.accnum=3D>b.accnum=3D>a.accnum They are updating B (All fields via a Form including the acc num colum) and Table C actually references B. C got a Foreign Key to B on Acc Num. Problem we found was that C does not have ANY index on the Acc num column. George =20________________________________________________ George Leonard Oracle Database Administrator New Dawn Technologies @ Wesbank E-mail:gleonard@xxxxxxxxxxxxx =20 You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! =20 -----Original Message----- From: Tim Gorman [mailto:tim@xxxxxxxxx]=20 Sent: 28 January 2005 14:18 PM To: oracle-l@xxxxxxxxxxxxx Cc: Desplace, Laura; Leonard, George Subject: Re: Referential indexes Please ask the developers: exactly what problem are they seeing? Please describe the failure in detail, describe the symptoms of the problem? Otherwise, it's like claiming murder without a corpse as evidence. on 1/28/05 2:51 AM, Leonard, George at GLeonard@xxxxxxxxxxxxx wrote: > Hi all >=20 >=20 > Got this query from a developer, or actually they are actually arguing > with the DBA team. >=20 > Table A - master, acc number field also only column in PK >=20 > Table B - Child, acc Number Field acc number is the first > column in PK, PK contains 2 more columns. >=20 > DBA's are saying we don't need a normal non unique stand alone index on > the acc Number field for table B. >=20 > Developers want a separate index, >=20 > COMMENT? >=20 > George > =3D20________________________________________________ > George Leonard > Oracle Database Administrator > New Dawn Technologies @ Wesbank > E-mail:gleonard@xxxxxxxxxxxxx > =3D20 > You Have The Obligation to Inform One Honestly of the risk, And As a > Person > You Are Committed to Educate Yourself to the Total Risk In Any Activity! > Once Informed & Totally Aware of the Risk, > Every Fool Has the Right to Kill or Injure Themselves as They See Fit! > =3D20 > ge/oracle-l > ________________________________________________________________________ _=3D > __________________________ >=20 >=20 > The views expressed in this email are, unless otherwise stated, those of =3D > the author and not those > of the FirstRand Banking Group an Authorised Financial Service Provider o=3D > r its management. > The information in this e-mail is confidential and is intended solely for=3D > =3D20the addressee. > Access to this e-mail by anyone else is unauthorised. > If you are not the intended recipient, any disclosure, copying, distribut=3D > ion or any action taken or=3D20 > omitted in reliance on this, is prohibited and may be unlawful. > Whilst all reasonable steps are taken to ensure the accuracy and integrit=3D > y of information and data=3D20 > transmitted electronically and to preserve the confidentiality thereof, n=3D > o liability or=3D20 > responsibility whatsoever is accepted if information or data is, for what=3D > ever reason, corrupted=3D20 > or does not reach its intended destination. >=20 > =3D20 ________________________________ > -- > //www.freelists.org/webpage/oracle-l >=20 _________________________________________________________________________= __________________________ The views expressed in this email are, unless otherwise stated, those of = the author and not those of the FirstRand Banking Group an Authorised Financial Service Provider o= r its management. The information in this e-mail is confidential and is intended solely for= =20the addressee. Access to this e-mail by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribut= ion or any action taken or=20 omitted in reliance on this, is prohibited and may be unlawful. Whilst all reasonable steps are taken to ensure the accuracy and integrit= y of information and data=20 transmitted electronically and to preserve the confidentiality thereof, n= o liability or=20 responsibility whatsoever is accepted if information or data is, for what= ever reason, corrupted=20 or does not reach its intended destination. =20 ________________________________ -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l