Re: Oracle 8.16 Deadlock

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 11 Jan 2002 11:30:19 -0000
Message-ID: <1010748572.10749.1.nnrp-01.9e984b29@news.demon.co.uk>

So the procedure that hits the problem tries to disable a foreign key, and the self-dealock appears against the parent of the foreign key.

You might like to experiment around the area of using another package in the same session to delete a parent row first, or insert some child rows. Perhaps the chain of invalidation requirements may be sufficient to cause the deadlock.

This would also help to explain the randomness - perhaps it is dependent on some such previous activity which doesn't always occur

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

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

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


Peter Lasner wrote in message <3c3ec5ce_3_at_news2.prserv.net>...


>It's just a simple table. The problem happens when the program (PL/SQL,

>using the dbms_sql package) tries to do :

>ALTER TABLE "T49PDGI"."PDGI_ISA_SPECIALTY" DISABLE CONSTRAINT

>"PDGI_ISA_SPECIALTY_FK_CTRY_FK"

>The constraint is defined as  FOREIGN KEY (SPEC_CTRY_ID)  REFERENCES

>T49PDGI.PDGI_COUNTRIES (CTRY_ID) ;

>By the way sometimes the program executes successfully.

>Regards Peter

>

Received on Fri Jan 11 2002 - 05:30:19 CST