Re: Deadlock Problem

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