The issue turned out to be a very interesting one.
try this.
open two db sessions.
(session 1) create table p( x number primary key); create table c( x number references p);
insert into p values(1); insert into c values(1); insert into p values (2); insert into c values(2); insert into p values(3); insert into c values(3); commit;
(session 1) update p set x = 2 where x = 2;
(session 2) update p set x = 1 where x = 1;
(session 1) -- this will hang update c set x = 2 where x = 2;
(session 2) update c set x = 1 where x = 1;
you will find session 1 comes out with deadlock detected error.
now
create index c_idx on c(x);
try the same test case and the hang will be gone.
Thanks, Karthik
On 25-Jun-05, at 9:42 PM, Mladen Gogala wrote:
On 06/25/2005 03:04:25 AM, Egor Starostin wrote:
Are you sure that your first session doesn't receive ORA-60? Probably, you just don't look in first session output. I reproduced you case and ORA-60 was generated (which is expected).
Note that hanganalyze in your case shows lock, not a deadlock. Deadlocks are printed in 'Cycles' section of hanganalyze trace file. Sessions from 'Open Cahins' section are just locks.
That is correct, my first session did receive ora-0060 but I wasn't able to
type fast enough. Yes, when I come to think of it, oracle will break the
chain as soon as it discovers it. Hanganalyze will not have time enough
to catch it.
--
Mladen Gogala
Oracle DBA
-- //www.freelists.org/webpage/oracle-l
-- //www.freelists.org/webpage/oracle-l