As you have figured out, the largest cause of Deadlock problems are bad
coding and missing indexes to support foreign keys. Below is a sql to
help you find tables with foreign keys but no indexes to support them.
The query is not 100% bullet proof, but it is a start.
I use this for Curam applications as they do not believe in supplying
indexes for foreign keys (their official response is to drop the FK's -
SELECT 'create index ' || substr(ut.table_name,1,20) ||
ROUND(sys.dbms_random.value*100)||'IDX ' ||
'ON ' || ut.table_name || ' (' || ucc.column_name || ') ' ||
'TABLESPACE ' || ut.TABLESPACE_NAME||'PK pctfree 10' ddl_string
FROM USER_TABLES ut, USER_CONSTRAINTS uc, USER_CONS_COLUMNS ucc
AND ucc.constraint_name = uc.constraint_name
AND ut.table_name = uc.table_name
AND NVL(position,1) = 1
AND NOT EXISTS(SELECT 1 FROM USER_IND_COLUMNS uic
AND ucc.column_name = uic.column_name
ORDER BY 1;
Sent: Monday, June 27, 2005 4:12 AM
Subject: Re: ORA-600
The issue turned out to be a very interesting one.
open two db sessions.
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);
update p set x = 2 where x = 2;
update p set x = 1 where x = 1;
(session 1) -- this will hang
update c set x = 2 where x = 2;
update c set x = 1 where x = 1;
you will find session 1 comes out with deadlock detected error.
create index c_idx on c(x);
try the same test case and the hang will be gone.
look up chapter 7 of tom kytes' book - Indexes on Foreign Keys. (page
142 in my book).
"The number one cause of deadlocks in the Oracle database, in my
experience, is unindexed foreign keys" - says the Guru himself.
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
> chain as soon as it discovers it. Hanganalyze will not have time
> to catch it.
> Mladen Gogala
> Oracle DBA