Deadlock Problem

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 Received on Thu Jan 11 2007 - 09:29:29 CST