Re: 'no row' deadlock - ORA-00060

This suggests that there may be a different cause to your problem. I attach an SQL script to demonstrate the PK/FK problem, and how ANY index starting with the FK columns is sufficient to eliminate the problem

This suggests an alternative issue - but I can't think at the moment why adding a redundant index should ever be a solution.

Is the DELETE statement in the deadlock dump against the parent or the child ?

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Samuel Becker wrote in message ...


>

>Thanks a lot. I have created an index on a child table and it works !

>

>Nevertheless, there's something i don't understand: this child table

>has already an unique index like Nuno Suto has mentionned: "(COL_FK,

COL_SEQ)"


>where is COL_FK is the foreign key. And if i don't create another index

>(not unique) on COL_FK, i get deadlocks...

>

>Regards




begin 666 c_fk.sql
M<F5M#0IR96T)4V-R:7!T._at_D)8U]F:RYS<6P-"G)E;0E!=71H;W(Z"0E*;VYA
M=&AA;B!,97=I<PT*<F5M"41A=&5D._at_D)36%R(#(P,#$-"G)E;0E0=7)P;W-E
M._at_E$96UO($9+(&QO8VMI;F<@:7-S=64-"G)E;0T*<F5M"4YO=&5S.@T*<F5M
M"0E,87-T('1E<W1E9"!O;B X+C$N-RXP+C -"G)E;0T*#0IS<&]O;"!C7V9K
M#0H-"F1R;W @=&%B;&4_at_8VAI;&0[#0ID<F]P('1A8FQE('!A<F5N=#L-"@T*
M8W)E871E('1A8FQE('!A<F5N=" H#0H):60@"6YU;6)E<BP-"@EV,0EV87)C
M:&%R,B_at_Q,"DL#0H)8V]N<W1R86EN= EP7W!K('!R:6UA<GD@:V5Y*&ED*0T*
M*3L-"@T*#0IR96T-"G)E;0E7:71H('1H92!P<FEM87)Y(&ME>2!C7W!K+"!T
M:&4@=7!D871E('=I;&P_at_3D]4(&QO8VL@=&AE(&-H:6QD('1A8FQE(&EN(&UO
M9&4_at_- T*<F5M"4EF('EO=2!R96UO=F4@=&AE('!R:6UA<GD@:V5Y+"!T:&4@
M=7!D871E(%=)3$P@;&]C:R!T:&4_at_8VAI;&0@=&%B;&4@:6X@;6]D92 T#0IR
M96T-"G)E;0E);B!F86-T+"!A;GD@:6YD97@@=&AA="!S=&%R=',@=VET:"!T
M:&4_at_1DL@8V]L=6UN<R!W:6QL(&)E('-U9F9I8VEE;G0-"G)E;0ET;R!E;&EM
M:6YA=&4@=&AI<R!P87)T:6-U;&%R(%18(&UO9&4_at_-"!P<F]B;&5M+@T*<F5M
M#0H-"F-R96%T92!T86)L92!C:&EL9" H#0H):61?< D);G5M8F5R+ T*"7-E
M<5]C"0EN=6UB97(L#0H)=C$)"79A<F-H87(R*#$P*2P-"@EC;VYS=')A:6YT
M"6-?9FM?<"!F;W)E:6=N(&ME>2 H:61?<"D@<F5F97)E;F-E<R!P87)E;G0L
M#0H)8V]N<W1R86EN= EC7W!K('!R:6UA<GD@:V5Y("AI9%]P+"!S97%?8RD-
M"BD-"CL-"@T*#0II;G-E<G0@:6YT;R!P87)E;G0@=F%L=65S*#$L)V]N92<I
M.PT*:6YS97)T(&EN=&\@8VAI;&0@=F%L=65S("@Q+#$L)V]N92!O;F4G*3L-
M"FEN<V5R="!I;G1O(&-H:6QD('9A;'5E<R H,2PR+"=O;F4@='=O)RD[#0H-
M"F-O;6UI=#L-"@T*=7!D871E('!A<F5N="!S970@:60@/2!I9#L-"@T*<V5L
M96-T("H_at_9G)O;2!V)&QO8VL@=VAE<F4@='EP92 A/2 G35(G.PT*#0IA;'1E
M<B!T86)L92!C:&EL9"!D<F]P('!R:6UA<GD@:V5Y.PT*#0IU<&1A=&4@<&%R
M96YT('-E="!I9" ](&ED.PT*#0IS96QE8W0@*B!F<F]M('8D;&]C:R!W:&5R
M92!T>7!E("$]("=-4B<[#0H-"F-R96%T92!I;F1E>"!C7VED>"!O;B!C:&EL
M9"AI9%]P+"!V,2D[#0H-"G5P9&%T92!P87)E;G0@<V5T(&ED(#T@:60[#0H-
M"G-E;&5C=" J(&9R;VT@=B1L;V-K('=H97)E('1Y<&4@(3T@)TU2)SL-"@T*
+<W!O;VP@;V9F#0H`
`
end