Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Deadlock Problem

Re: Deadlock Problem

From: Galen Boyer <galen_boyer_at_yahoo.com>
Date: 10 Jan 2007 18:15:02 -0600
Message-ID: <uwt3us0n1.fsf@rcn.com>


On 10 Jan 2007, artmt_at_hotmail.com wrote:
> I have a procedure that looks similar to this:
>
> PROCEDURE Proc1 (p_PRICING_DATE DATE, p_FUND_NUMBER NUMBER)
> IS
>
> BEGIN
>
> DELETE FROM Tab1
> WHERE pricing_date = p_PRICING_DATE
> AND fund_number = p_FUND_NUMBER;
>
> INSERT INTO Tab1
> SELECT pricing_date, fund_number, COUNT(*)
> FROM Tab2
> GROUP BY pricing_date, fund_number;
>
> COMMIT;
>
> END;
>
> It is called by another procedure:
>
> PROCEDURE Proc2
> IS
>
> BEGIN
>
> FOR Cur1 IN(SELECT DISTINCT pricing_date, fund_number FROM Tab3)
> LOOP
>
> Proc1(i.pricing_date, i.fund_number);
>
> END LOOP;
>
> END;
>
> The routine runs fine when Proc1 is executed sequencially. If I
> modify Proc2 to submit 10 calls to Proc2 via DBMS_SCHEDULER to run
> simultaniously most executions fail with "ORA-00060: deadlock detected
> while waiting for resource".
>
> If I comment out "DELETE FROM Tab1..." block all 10 simultaneous
> executions run fine.
> Based on the program logic each of the 10 calls to Proc1 deletes a
> separate non-intersecting set of rows. I tested and verified that.
> I also increased INITRANS on Tab1 to 50.
>
> Any suggestions?
>
> Thanks
> Art

First off, I'd use a materialized view and forget about the code you have written in the first place. You are tring to aggregate the fund/price to a count.

Second choice is, I'd update the table instead of delete insert.

My quess at the reason for your problem is that the DELETE from Tabl1 can delete rows that other procs could be inserting.

-- 
Galen Boyer
Received on Wed Jan 10 2007 - 18:15:02 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US