RE: ORA-600 Deadlock Issues

From: Mercadante, Thomas F (LABOR) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Mon, 27 Jun 2005 07:48:28 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF16E753@EXCNYSM0A1AJ.nysemail.nyenet>

Karthik,

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 - nice, eh?).

Good Luck!

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 WHERE uc.constraint_type='R'
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

WHERE uic.table_name=ucc.table_name
AND   ucc.column_name = uic.column_name
AND   uic.column_position=1)

ORDER BY 1; Tom

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Karthik Sent: Monday, June 27, 2005 4:12 AM
To: oracle-l_at_freelists.org
Subject: Re: ORA-600 Deadlock Issues

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.

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.

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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jun 27 2005 - 07:54:03 CDT